CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxquery
Kerneels Roos
Kerneels Roos

Reputation: 124

T-SQL XQuery for returning a result row per matching XML value for each nested element

Given a XML data typed column x in table y with values such as:

<a>
<b>
<c>1</c>
<c>2</c>
<c>3</c>
</b>
</a>

How can I obtain a result set with T-SQL XQuery such that one row is returned per <c> element value? Let's say table y also has column z that contains the number of <c> elements in column x - just so we have an additional column to return.

The best we could come up with thus far was:

select 
z, 
cvalues = x.query('data(a/b/c)')
from y

The above however will return one row per row in y and have a white space separated list of the values for the <c> elements in column cvalues.

How can we get a result set such as:

z cvalues
3 1
3 2
3 3

instead of what we get now:

z cvalues
3 1 2 3

Thanks!

Upvotes: 3

Views: 990

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You can use CROSS APPLY combined with nodes:

SELECT 
  z, 
  cvalues = s.c.value('.', 'int')
FROM y
CROSS APPLY x.nodes('//c') AS s(c);

LiveDemo

Output:

╔═══╦═════════╗
║ z ║ cvalues ║
╠═══╬═════════╣
║ 3 ║       1 ║
║ 3 ║       2 ║
║ 3 ║       3 ║
╚═══╩═════════╝

Keep in mind that:

//c match all c xml element no matter where it is in hierarchy

/a/b/c will match c xml element that is nested inside a and b

Upvotes: 3

Related Questions