Select using xquery with nested node

Question

I try to return a set of result from an xml document using xquery but I didn't figure out.

Here are my queries:

DECLARE @Data xml
SET @Data='<main>
 <person>
  <name>Daniel</name>
  <cars>
   <model>y75</model>
   <model>b25</model>
  </cars>
 </person>
 <person>
  <name>Marc</name>
  <cars>
   <model>h20</model>
  </cars>
 </person>
</main>'

SELECT x.value('name[1]','nvarchar(max)') name,
    x.value('cars[1]','nvarchar(max)') car
FROM   @Data.nodes('//main/person') AS e(x)

-- Returns:

-- Name  Car
------------------
-- Daniel y75b25
-- Marc  h20

I would like to show:

Name   Car
Daniel y75
Daniel b25
Marc   h20

I tried in different way but I didn't find the good results!

Thanks.

Remus Rusanu · Accepted Answer · 2010-10-14 23:47:57Z

2

You must project all models as rows (using .nodes()), then navigate up the parent to extract the person:

SELECT x.value('../../name[1]','nvarchar(max)') name, 
    x.value('.','nvarchar(max)') car 
FROM   @Data.nodes('//main/person/cars/model') AS e(x)

Updated

If you want to return persons that have no cars, then you have to apply the .nodes() method twice: once for the persons, and then once more on each person to gets the cars. The OUTER APPLY operator will do the kind of 'outer join' you need:

SELECT e.x.value('name[1]','nvarchar(max)') name, 
    c.x.value('.','nvarchar(max)') car 
FROM   @Data.nodes('//main/person') AS e(x)
outer apply e.x.nodes('./cars/model') as c(x)

edited Oct 14, 2010 at 23:47

answered Oct 12, 2010 at 17:18

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

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

2 Comments

Dan Over a year ago

That works fine. I didn't know we can use expression like ../ Thank you!

Dan Over a year ago

However there is an issue, when we have no "model" for a person, the query doesn't show the "name", like a LEFT JOIN in T-SQL. For example if I remove the model "h20", "Marc" is not returned. So, I think the starting point must be "name" and not "model", what do you think?

Collectives™ on Stack Overflow

Select using xquery with nested node

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related