Select from parent-children xml content

user_822388 0
Viewed 23

I have been looking for solution for selecting parent-child relation from table [Group] which contains a xml column.

[Group] table has the following structure:

  • ID - int
  • Content - xml

There is xml data - parent-child relation in column Content

<root>
    <person name="John">
        <device name="notebook" />
        <device name="xbox" />   
    </person>
    <person name="Jane">
        <device name="TV" />
    </person>
    <person name="Mark">
    </person>
</root>

I would like to select data in the following format:

Group Id PersonName DeviceName
1 John notebook
1 John xbox
1 Jane TV

Because Mark has no device assigned, there is no row for Mark in result.
Is it possible to achieve this result in a SELECT query?

sql-server sql
1 Answers

As I mentioned, you can use XQuery for this. As you don't want any rows for Mark, I go straight to the device node, in the nodes method, as this means that no rows for Mark will be found. Then you can go back up one level to get the person's name:

SELECT V.ID AS GroupID,
       p.d.value('../@name','nvarchar(50)') AS PersonName,
       p.d.value('@name','nvarchar(50)') AS DeviceName
FROM(VALUES(1,CONVERT(xml,'<root>
    <person name="John">
        <device name="notebook" />
        <device name="xbox" />   
    </person>
    <person name="Jane">
        <device name="TV" />
    </person>
    <person name="Mark">
    </person>
</root>')))V(ID, Content)
     CROSS APPLY V.Content.nodes('root/person/device') p(d);
user_20299830
Related