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.

Answer 1

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)