Home Forums Programming XML Please recommend best way to read this xml content into a table RE: Please recommend best way to read this xml content into a table

  • If both GPSFixes and GPSFix repeat then you'll need to have 2 x .nodes methods(). Here is an example using your xml as a base (i have stripped it back a lot purely to make it easier to read)

    DECLARE @xml XML

    SET @xml = '

    <VehicleInfoMessage>

    <GPSFixes NumFixes="1">

    <GPSFix>

    <FixTime>2012-09-28 13:27:10 GMT</FixTime>

    <FixTimeUTF>1348838830</FixTimeUTF>

    </GPSFix>

    <GPSFix>

    <FixTime>2011-08-28 12:27:10 GMT</FixTime>

    <FixTimeUTF>1348838830</FixTimeUTF>

    </GPSFix>

    </GPSFixes>

    <GPSFixes NumFixes="2">

    <GPSFix>

    <FixTime>2010-07-28 11:27:10 GMT</FixTime>

    <FixTimeUTF>1348838830</FixTimeUTF>

    </GPSFix>

    <GPSFix>

    <FixTime>2009-06-28 10:27:10 GMT</FixTime>

    <FixTimeUTF>1348838830</FixTimeUTF>

    </GPSFix>

    </GPSFixes>

    </VehicleInfoMessage>'

    SELECT t.c.value('(@NumFixes)[1]', 'smallint') AS 'NumFixes'

    , u.c.value('(FixTime/text())[1]', 'varchar(20)') AS 'FixTime'

    , u.c.value('(FixTimeUTF/text())[1]', 'varchar(20)') AS 'FixTimeUTF'

    FROM @xml.nodes('/VehicleInfoMessage/GPSFixes') T(c)

    CROSS APPLY t.c.nodes('GPSFix') u(c)