Iterate over XML "cousins" with SQL Server

Question

I have the below XML document stored in a TSQL variable with XML type:

<root>
  <parent>
    <child>Alice</child>
    <child>Bob</child>
    <child>Carol</child>
  </parent>
  <house>
    <room><id>1</id></room>
    <room><id>2</id></room>
    <room><id>3</id></room>
  </house>
</root>

I would like to iterate over "cousin" nodes (that is, nodes whose parents are siblings) and insert in a table one row per iteration and one column per cousin. So the result would be something like this:

Child | Room
------------
Alice | 1
Bob   | 2
Carol | 3

(I know for a fact that there are as many rooms as children).

I feel like this is a simple task, but can't seem to find a way. I am a beginner in SQL Server and XPath, and probably lack the terminology to look for documentation.

What I've tried so far is to iterate over, say the child elements, and try to read the matching room element from there using ROW_NUMBER to pick the room I want:

INSERT INTO children (child, room)
SELECT 
  child = T.Item.value('(../parent/child/text())[' + (ROW_NUMBER() OVER(ORDER BY T.Item)) + ']', 'VARCHAR(10)'),
  room = T.Item.value('(id/text())[1]', 'CHAR(1)')
FROM   
  @XML.nodes('root/house/room') AS T(Item)

But SQL server complains that the value() accepts only string literal as first argument (what kind of limitation is that??).

Any idea of how I could do that simply?

Answer 1

Not sure if this the best or shortest way, but it produces the output you require:

DECLARE @x XML='
<root>
  <parent>
    <child>Alice</child>
    <child>Bob</child>
    <child>Carol</child>
  </parent>
  <house>
    <room><id>1</id></room>
    <room><id>2</id></room>
    <room><id>3</id></room>
  </house>
</root>';

;WITH childs AS (
    SELECT
        n.e.value('.','NVARCHAR(128)') AS child,
        id=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM
        @x.nodes('/root/parent/child') AS n(e)
),
room_ids AS (
    SELECT
        n.e.value('.','NVARCHAR(128)') AS room_id,
        id=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM
        @x.nodes('/root/house/room/id') AS n(e)
)
SELECT
    c.child,
    r.room_id
FROM
    childs AS c
    INNER JOIN room_ids AS r ON
        r.id=c.id
ORDER BY
    c.id;

Answer 2

As a pure XPath query you might do this:

SELECT The.Room.value('id[1]','varchar(max)'),The.Room.value('let $r:=. return (../../parent/child[position()=$r]/text())[1]','varchar(max)')
FROM @YourXML.nodes('/root/house/room') AS The(Room)

But - to be honest - I'd prefer TT.'s solution. This solution relys on the rooms to be numbered from 1 to n without gaps. TT.'s solution would work even with unsorted room numbers...