No problem, thanks for the feedback.

I did notice that i left something unnecessary in the code from my testing :ermm:

You don't need the...

Hi,

Normally you can simply use the nodes() method to shred the xml and this will return you a nice dataset. However, with your example XML, I see that the data...

To shred the xml to flat tables, you will need to use some of the xml data type methods and maybe a little bit of XQuery. the nodes() method will...

Nice, learnt something new today. Thanks.

Personally I would use XQuery to do the transformation. I save xslt for the hardest of transformations, but to be honest, I have been able to do most things in...

Very good + clear question. thanks... nice way to start the week.

Nice, thank you.. only got this one right because that i've used FTS briefly in the past, otherwise i'd have no idea!

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....

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...

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

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)

You're welcome.

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...

Great question.. I nearly fell into the trap of selecting answer two by not reading the question properly! Thanks.

As I mentioned previously, AFAIK, there is no real way to just strip out a namespace from an xml structure by using TSQL other than to cast the xml to...