XML hierarchical data

  • 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

  • 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
  • Thank you Mark

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply