Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Please recommend best way to read this xml content into a table Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 27, 2013 11:38 AM
Points: 19, Visits: 69
Hello,

I am relatively new to reading xml into table using TSQL but I have tons of experience programming with TSQL in general.

I need to read xml into a table and from my reading it appears that .nodes is the best way to do this. Before I commit to it I would like to hear opinions from the gurus here and thank you.

Here is a sample of my xml content:

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE VehicleInfo SYSTEM "http://www.vehicleinfo.com/dconnect/DConnect.v2.0.dtd">
<VehicleInfoMessage Type="GPS">
<VIN>4444TCFEXB8543456</VIN>
<FleetId>421250</FleetId>
<MsgId>415583965696</MsgId>
<MessageTime>2012-09-28 13:27:16 GMT</MessageTime>
<MessageTimeUTF>1348838836</MessageTimeUTF>
<DeliveryStatus>Current</DeliveryStatus>

<GPSFixes NumFixes="1">
<GPSFix>
<FixTime>2012-09-28 13:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
<Latitude>434.09230</Latitude>
<Longitude>-370.21554</Longitude>
<Ignition>On</Ignition>
<Speed Type="Avg" Units="MPH">9</Speed>
<Speed Type="Inst" Units="MPH">16</Speed>
<Speed Type="Max" Units="MPH">16</Speed>
<Heading>250</Heading>
<Odometer>27933.495</Odometer>
<AgeInMiles>0.006</AgeInMiles>
</GPSFix>
</GPSFixes>
</VehicleInfoMessage>

In order to make it go into the xml variable I had to strip the header. Not sure if this is the best way to approach it.

SELECT @MyXML = right(field1,len(field1)-charindex('<VehicleinfoMessage Type="GPS">',field1)+1)
Post #1365900
Posted Friday, September 28, 2012 8:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:29 AM
Points: 2,551, Visits: 1,615
Hi. To strip out the DTD you can use the CONVERT function e.g.:

DECLARE @xmlVarchar VARCHAR(MAX)
SET @xmlVarchar = '<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE VehicleInfo SYSTEM "http://www.vehicleinfo.com/dconnect/DConnect.v2.0.dtd">
<VehicleInfoMessage Type="GPS">
<VIN>4444TCFEXB8543456</VIN>
<FleetId>421250</FleetId>
<MsgId>415583965696</MsgId>
<MessageTime>2012-09-28 13:27:16 GMT</MessageTime>
<MessageTimeUTF>1348838836</MessageTimeUTF>
<DeliveryStatus>Current</DeliveryStatus>

<GPSFixes NumFixes="1">
<GPSFix>
<FixTime>2012-09-28 13:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
<Latitude>434.09230</Latitude>
<Longitude>-370.21554</Longitude>
<Ignition>On</Ignition>
<Speed Type="Avg" Units="MPH">9</Speed>
<Speed Type="Inst" Units="MPH">16</Speed>
<Speed Type="Max" Units="MPH">16</Speed>
<Heading>250</Heading>
<Odometer>27933.495</Odometer>
<AgeInMiles>0.006</AgeInMiles>
</GPSFix>
</GPSFixes>
</VehicleInfoMessage>'

SELECT CONVERT(XML, @xmlVarchar, 2)


This gets rid of the DTD for you nicely.

The .nodes method is useful if you have repeating sections in the XML and you need to shred it into a relational table. Looking at your example it looks like the section GPSFixes could repeat? If so, then .nodes is your best bet for getting that xml into a relational table format.

Here is an example of the usage

DECLARE @xmlVarchar VARCHAR(MAX)
SET @xmlVarchar = '<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE VehicleInfo SYSTEM "http://www.vehicleinfo.com/dconnect/DConnect.v2.0.dtd">
<VehicleInfoMessage Type="GPS">
<VIN>4444TCFEXB8543456</VIN>
<FleetId>421250</FleetId>
<MsgId>415583965696</MsgId>
<MessageTime>2012-09-28 13:27:16 GMT</MessageTime>
<MessageTimeUTF>1348838836</MessageTimeUTF>
<DeliveryStatus>Current</DeliveryStatus>

<GPSFixes NumFixes="1">
<GPSFix>
<FixTime>2012-09-28 13:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
<Latitude>434.09230</Latitude>
<Longitude>-370.21554</Longitude>
<Ignition>On</Ignition>
<Speed Type="Avg" Units="MPH">9</Speed>
<Speed Type="Inst" Units="MPH">16</Speed>
<Speed Type="Max" Units="MPH">16</Speed>
<Heading>250</Heading>
<Odometer>27933.495</Odometer>
<AgeInMiles>0.006</AgeInMiles>
</GPSFix>
</GPSFixes>
</VehicleInfoMessage>'

DECLARE @xml XML
SELECT @xml = CONVERT(XML, @xmlVarchar, 2)

SELECT @xml.value('(/VehicleInfoMessage/@Type)[1]', 'varchar(10)') AS 'Type'
, @xml.value('(/VehicleInfoMessage/VIN)[1]', 'varchar(20)') AS 'Vin'
, t.c.value('(FixTime)[1]', 'varchar(20)') AS 'FixTime'
, t.c.value('(FixTimeUTF)[1]', 'varchar(20)') AS 'FixTimeUTF'
FROM @xml.nodes('/VehicleInfoMessage/GPSFixes/GPSFix') T(c)

Post #1365916
Posted Friday, September 28, 2012 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 27, 2013 11:38 AM
Points: 19, Visits: 69
awesome. thanks for the tip on the convert(stripping of the header) and thank you for a great
example of the select statement.
Post #1365935
Posted Friday, September 28, 2012 9:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:29 AM
Points: 2,551, Visits: 1,615
You're welcome.
Post #1365948
Posted Friday, September 28, 2012 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 27, 2013 11:38 AM
Points: 19, Visits: 69
one more question please. How do I get the value from the speed entries?

<Speed Type="Avg" Units="MPH">9</Speed>
<Speed Type="Inst" Units="MPH">16</Speed>
<Speed Type="Max" Units="MPH">16</Speed>
Post #1365954
Posted Friday, September 28, 2012 9:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 1,780, Visits: 5,662
In addition to the great answer you have....

Try using /text() as part of the xpath when using ".value" ...

DECLARE @xml XML
SELECT @xml = CONVERT(XML, @xmlVarchar, 2)

SELECT @xml.value('(/VehicleInfoMessage/@Type/text())[1]', 'varchar(10)') AS 'Type'
, @xml.value('(/VehicleInfoMessage/VIN/text())[1]', 'varchar(20)') AS 'Vin'
, t.c.value('(FixTime/text())[1]', 'varchar(20)') AS 'FixTime'
, t.c.value('(FixTimeUTF/text())[1]', 'varchar(20)') AS 'FixTimeUTF'
FROM @xml.nodes('/VehicleInfoMessage/GPSFixes/GPSFix') T(c)

It will usually show a marked speed improvement.



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1365956
    Posted Friday, September 28, 2012 9:30 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Yesterday @ 1:29 AM
    Points: 2,551, Visits: 1,615
    MM: Thanks for the /text() tip there.

    photo1: try this to get the speed values

    SELECT t.c.value('(Speed[@Type="Avg"]/text())[1]', 'varchar(20)') AS 'SpeedAvg'
    , t.c.value('(Speed[@Type="Inst"]/text())[1]', 'varchar(20)') AS 'SpeedInst'
    , t.c.value('(Speed[@Type="Max"]/text())[1]', 'varchar(20)') AS 'SpeedMax'
    FROM @xml.nodes('/VehicleInfoMessage/GPSFixes/GPSFix') T(c)

    Post #1365961
    Posted Friday, September 28, 2012 9:36 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Yesterday @ 1:29 AM
    Points: 2,551, Visits: 1,615
    BTW: Choose better datatypes that I have in the query! shame on me !!
    Post #1365968
    Posted Friday, September 28, 2012 9:43 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Friday, September 27, 2013 11:38 AM
    Points: 19, Visits: 69
    great help. I really appreciate it.

    What if the GPSFixes section does repeat? Will the query come automatically in two rows with the redundant information duplicated?

    ( I think I may be just wishing it )

    Post #1365970
    Posted Friday, September 28, 2012 9:52 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Yesterday @ 1:29 AM
    Points: 2,551, Visits: 1,615
    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)

    Post #1365975
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse