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...