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

XML hierarchical data Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 4:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 7:43 AM
Points: 38, Visits: 40
hello

I have a request to collect some data from an xml file, and I am stuck at this point. The xml is structured like this

[...]
<device id ='...'>
.....
<sensor id = '...'>
........
</sensor>
.....
<sensor id = '...'>
........
</sensor>
.....
<sensor id = '...'>
........
</sensor>
...
</device>

I want to generate a table that contains the device id and sensor id like this:

deviceID sensorID
40 256
40 258
40 259
51 1011
51 1012
etc etc

can you please help me out with an idea.


thank you
Alex
Post #1503457
Posted Thursday, October 10, 2013 5:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Something like this?


DECLARE @x XML = '
<Root>
<device id ="40">
<sensor id = "256">
</sensor>
<sensor id = "258">
</sensor>
<sensor id = "259">
</sensor>

</device>
<device id ="51">
<sensor id = "1011">
</sensor>
<sensor id = "1012">
</sensor>
</device>
</Root>
'

SELECT x.device.value('@id[1]','INT') AS deviceID,
y.sensor.value('@id[1]','INT') AS sensorID
FROM @x.nodes('/Root/device') AS x(device)
OUTER APPLY x.device.nodes('sensor') AS y(sensor)



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1503488
Posted Thursday, October 10, 2013 6:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 7:43 AM
Points: 38, Visits: 40
Thank you Mark
Post #1503527
Posted Wednesday, October 16, 2013 6:04 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 500, Visits: 2,290
Because you are dealing with a one-to-many parent/child node relationship you could start your query with /Root/device/sensor as your context like so:

(Taking the example code that Mark was nice enough to put together)

SELECT x.device.value('(../@id)[1]','INT') AS deviceID,
x.device.value('(@id)[1]','INT') AS sensorID
FROM @x.nodes('/Root/device/sensor') x(device)

Here we don't need to do any joins (or use apply). The downside is that this produces a more complex and slightly slower query plan. That said, I have been using sp_xml_preparedocument lately which performs wonderfully and with a simple query plan:

DECLARE @x XML = '
<Root>
<device id ="40">
<sensor id = "256">
</sensor>
<sensor id = "258">
</sensor>
<sensor id = "259">
</sensor>

</device>
<device id ="51">
<sensor id = "1011">
</sensor>
<sensor id = "1012">
</sensor>
</device>
</Root>
'

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

select *
from OPENXML (@hdoc, '/Root/device/sensor', 2)
WITH (
deviceID int '../@id',
sensorID int '@id'
)

Edit: Added SQL Code -- e.g. (code="sql")


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1505454
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse