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