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:

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?

CodePudding user response：

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);