CODEWITHSUNDEEP
sql-serverxmlxquery
JSacksteder
JSacksteder

Reputation: 790

How to return certain sequential sibling nodes with XQuery on SQL Server?

Given the following snippet, I need a query that returns the text of the 'a' nodes and the text of the 1st following 'b' node.

declare @X xml = '
<root>
    <a>cat</a>
    <b>Cats don''t like milk</b>
    <a>dog</a>
    <c>not this</c>
    <b>Dogs like everything</b>
    <b>and not this</b>
</root>'

e.g. - 

Animal | Preference
-------|---------------------
cat    | Cats don't like milk
dog    | Dogs like everything

Using the nodes method like this-

SELECT
    c.x.value(N'text()[1]','varchar(max)') as animal
    --,??? as preference
FROM
    @X.nodes('root/a') c(x)

I don't think adjacent nodes are available from the 'x' object, because it's a table variable by that point. Clearly the XML is suboptimal, since the b nodes should more accurately be children of the a nodes, but that's the shape of the data as it comes to me. SQL Server is limited to a subset of XQuery, which makes this more difficult.

Upvotes: 2

Views: 1138

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

With due thanks to this answer, which isn't quite what we need but which did point me in the direction of the >> operator.

The lack of proper axes makes this more involved than necessary:

SELECT
    c.a.value('text()[1]','varchar(max)') as animal,
    c.a.query('
        let $a := . 
        return (../b[. >> $a][1])/text()'
    ).value('text()[1]','varchar(max)') as preference
FROM 
    @x.nodes('root/a') c(a)

We have to assign the a node to a variable in order to be able to query for the first b node that follows it.

Upvotes: 1

Related Questions