XML hierarchical data

• alexandru.tero.toth

  SSC Enthusiast

  Points: 119

  More actions

  October 10, 2013 at 4:39 am

  #395640

  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

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 10, 2013 at 5:52 am

  #1657465

  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)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

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

• alexandru.tero.toth

  SSC Enthusiast

  Points: 119

  More actions

  October 10, 2013 at 6:46 am

  #1657501

  Thank you Mark

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  October 16, 2013 at 6:04 pm

  #1659103

  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")

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001