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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » Please recommend best way to read this xml...

12 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

photo1

Posted Friday, September 28, 2012 8:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 03, 2013 4:53 AM
Points: 5, Visits: 20

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 Smile

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

arthurolcot

Posted Friday, September 28, 2012 8:47 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 1,726, Visits: 1,413

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

photo1

Posted Friday, September 28, 2012 9:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 03, 2013 4:53 AM
Points: 5, Visits: 20

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

arthurolcot

Posted Friday, September 28, 2012 9:16 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 1,726, Visits: 1,413

You're welcome.

Post #1365948

photo1

Posted Friday, September 28, 2012 9:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 03, 2013 4:53 AM
Points: 5, Visits: 20

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

mister.magoo

Posted Friday, September 28, 2012 9:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 1,333, Visits: 4,015

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

Post #1365956

arthurolcot

Posted Friday, September 28, 2012 9:30 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 1,726, Visits: 1,413

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

arthurolcot

Posted Friday, September 28, 2012 9:36 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 1,726, Visits: 1,413

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

Post #1365968

photo1

Posted Friday, September 28, 2012 9:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 03, 2013 4:53 AM
Points: 5, Visits: 20

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?

( Wink

I think I may be just wishing it )

Post #1365970

arthurolcot

Posted Friday, September 28, 2012 9:52 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 1,726, Visits: 1,413

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 »

12 posts, Page 1 of 2

1 2 »»

Permissions