Please recommend best way to read this xml content into a table

Question

Post reply

Please recommend best way to read this xml content into a table

Molap

Mr or Mrs. 500

Points: 507
More actions
September 28, 2012 at 8:27 am

#74456

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)
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Arthur Olcot SSCertifiable Points: 6563 More actions · Answer 1

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)

Molap Mr or Mrs. 500 Points: 507 More actions · Answer 2

awesome. thanks for the tip on the convert(stripping of the header) and thank you for a great

example of the select statement.

Arthur Olcot SSCertifiable Points: 6563 More actions · Answer 3

Arthur Olcot

SSCertifiable

Points: 6563

September 28, 2012 at 9:16 am

#1543483

You're welcome.

Molap Mr or Mrs. 500 Points: 507 More actions · Answer 4

one more question please. How do I get the value from the speed entries?

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

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

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Arthur Olcot SSCertifiable Points: 6563 More actions · Answer 6

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)

Arthur Olcot SSCertifiable Points: 6563 More actions · Answer 7

BTW: Choose better datatypes that I have in the query! shame on me !!

Molap Mr or Mrs. 500 Points: 507 More actions · Answer 8

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 )

Arthur Olcot SSCertifiable Points: 6563 More actions · Answer 9

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)

Molap Mr or Mrs. 500 Points: 507 More actions · Answer 10

ok. so if they do repeat I really need to know how many times they repeat and somehow dynamically construct the statement? Otherwise it looks like hardcoding the number of repeats for static record is the way to go.

arthurolcot (9/28/2012)
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)

Arthur Olcot SSCertifiable Points: 6563 More actions · Answer 11

No you don't need to do that. That example query will work ok regardless of the number of actual repeats you get of GPSFixes or GPSFix in the XML message. You don't need to add in any more .nodes methods.

Try it, take the example code and add in lots more <GPSFixes> sections and <GPSFix> sections into the test xml and run the code. The query will deal with it fine.