Any OpenXML experts? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Any OpenXML experts?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-04 : 08:00:19

Paul writes "Can someone explain how to format date time values when using OPENXML in stored procedures. The stored procedure updates the data except when a date time value is passed to it. In this case I am using a value called
EducationSheetSchool.Start= XMLEducationSheetSchool.Start

Here is my stored procedure:

CREATE PROC dbo.usp_EducationUpdateSchool @empdata nText
AS

DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata

--This code updates old data.
UPDATE EducationSheetSchool

SET
EducationSheetSchool.Address= XMLEducationSheetSchool.Address,
EducationSheetSchool.[Name]= XMLEducationSheetSchool.[Name],
EducationSheetSchool.LEA= XMLEducationSheetSchool.LEA,
EducationSheetSchool.Tel= XMLEducationSheetSchool.Tel,
EducationSheetSchool.Email= XMLEducationSheetSchool.Email,
EducationSheetSchool.Start= XMLEducationSheetSchool.Start,
EducationSheetSchool.ReasonForLeaving= XMLEducationSheetSchool.ReasonForLeaving

FROM OPENXML(@hDoc, 'NewDataSet/NewTable')
WITH (EducationSheetSchoolID Integer ,EducationSheetID Integer, Address nvarchar(50), Name nvarchar(50),LEA nvarchar(50), Tel nvarchar(50), Email nvarchar(50),Start DateTime,ReasonForLeaving nvarchar(50)) XMLEducationSheetSchool
--WITH (EducationSheetSchoolID Integer ,EducationSheetID Integer, Address nvarchar(50), Name nvarchar(50),LEA nvarchar(50), Tel nvarchar(50), Email nvarchar(50),ReasonForLeaving nvarchar(50)) XMLEducationSheetSchool
WHERE EducationSheetSchool.EducationSheetSchoolID= XMLEducationSheetSchool.EducationSheetSchoolID
GO"

Kristen
Test

22859 Posts

Posted - 2005-11-06 : 08:45:45

What does the XML data for START look like?

I kinda assumed that XML dates where always in ISO format, but maybe not!

You could perhaps "persuade" SQL Server to process the data by putting a

SET DATEFORMAT dmy

command above the UPDATE - put the "dmy" letters in the order the date is presented in the XML

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources