Specifying date format when reading XML into SQL
A colleague of mine is working on a procedure that reads some XML and inserts it into a SQL Server 2008 table. This is all very straightforward but he's struggling with the dates that are coming in.
The incoming XML has the dates formatted in the UK style:
Code:
<DateOfBirth>17/05/1970 00:00:00</DateOfBirth>
... but it's apparently failing because it thinks that 17 is the month.
He's tried the SQL Server options Set Language and Set Dateformat, but neither seem to help. Is there any way to force it to read the dates correctly?
The code he's using is:
Code:
Declare @Changes XML
Select @Changes = '
<Root>
<tblIncoming>
<Policy>
<PolicyID>1111</PolicyID>
<DateOfBirth>17/05/1970 00:00:00</DateOfBirth>
</Policy>
</tblIncoming>
</Root>
'
DECLARE @iPolicy INT
DECLARE @PrepareXmlStatus INT
EXEC @PrepareXmlStatus= sp_xml_preparedocument @iPolicy OUTPUT, @Changes
INSERT tblIncoming
SELECT *
FROM OPENXML(@iPolicy, '/Root/tblIncoming/Policy', 2)
WITH tblIncoming
EXEC sp_xml_removedocument @iPolicy;
Any help would be gratefully received...
Re: Specifying date format when reading XML into SQL
Don't use OPENXML ... use the native XML datatype... shred the XML manually. This should work (the test ran fine for me).
Code:
Code:
Declare @Changes XML
Select @Changes = '
<Root>
<tblIncoming>
<Policy>
<PolicyID>1111</PolicyID>
<DateOfBirth>17/05/1970 00:00:00</DateOfBirth>
</Policy>
</tblIncoming>
</Root>
'
select
T.c.value('(PolicyID)[1]', 'int') as PolicyId,
convert(datetime, T.c.value('(DateOfBirth)[1]', 'nvarchar(10)'), 103) as DOB
from @CHANGES.nodes('/Root/tblIncoming/Policy')T(c)
So I shred the XML preserving the data as a string... then used CONVERT to convert it to a date in the 103 format (which I got from here)
No errors, and I get a datetime value. And I also skip the overhead of OPENXML.
-tg
Re: Specifying date format when reading XML into SQL
Thanks, TG - I'll give that one a whirl. ;)
