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 9:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 27, 2013 11:38 AM
Points: 19, Visits: 69
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)

Post #1365977
Posted Friday, September 28, 2012 10:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:29 AM
Points: 2,554, Visits: 1,615
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.
Post #1365983
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse