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

Question

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!

Lukasz Szozda · Accepted Answer · 2015-12-08 15:03:13Z

3

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

edited Dec 8, 2015 at 15:03

answered Dec 8, 2015 at 14:57

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kerneels Roos Over a year ago

Very good! Added bonus for the part on matching. Excited to learn more XQuery: seems it can be very useful indeed.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related