Results 1 to 3 of 3

Thread: Specifying date format when reading XML into SQL

  1. #1

    Thread Starter
    Fanatic Member InvisibleDuncan's Avatar
    Join Date
    May 2001
    Location
    Eating jam.
    Posts
    819

    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...
    Indecisiveness is the key to flexibility.

    www.mangojacks.com

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Fanatic Member InvisibleDuncan's Avatar
    Join Date
    May 2001
    Location
    Eating jam.
    Posts
    819

    Re: Specifying date format when reading XML into SQL

    Thanks, TG - I'll give that one a whirl.
    Indecisiveness is the key to flexibility.

    www.mangojacks.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width