Extract child node value using SQL QUERY

Question

I have XML in SQL, I want the below result from the query.

TabSeq	Path	SubTabSeq
1	//Tab/Family	1
1	//Tab/Location	2
2	//Tab/Address	1
2	//Tab/Insurance	2

Below is the XML.

<Tabs>
    <Tab sequence="001">
        <Family sequence="001">
            <Data>Yes</Data>
        </Family>
        <Location sequence="002">
            <Data>USA</Data>
        </Location>
    </Tab>
    <Tab sequence="002">
        <Address sequence="001">
            <Data>XYZ</Data>
        </Address>
        <Insurance sequence="002">
            <Data>Yes</Data>
        </Insurance>
    </Tab>
</Tabs>

I have tried below,

declare @xml xml='<Tabs><Tab sequence="001"><Family sequence="001"><Data>Yes</Data></Family><Location sequence="002"><Data>USA</Data></Location></Tab><Tab sequence="002"><Address sequence="001"><Data>XYZ</Data></Address><Insurance sequence="002"><Data>Yes</Data></Insurance></Tab></Tabs>'

SELECT  t.c.value('@sequence', 'nvarchar(100)') As TabSeq
FROM @xml.nodes('//Tabs/child::node()') as t(c)

SELECT '//Tab' + '/' + c.value('local-name(.)[1]','nvarchar(100)') AS Path,  t.c.value('@sequence', 'nvarchar(100)') As SubTabSeq
FROM @xml.nodes('//Tab/child::node()') as t(c)

Is it possible?

Answer 1

In your second query you're iteraing subtabs, so you need to reference a parent of those nodes, which is Tab. Like with those two methods (depends on how strict you want to be):

SELECT 
  TabSeq1 = t.c.value('../@sequence', 'nvarchar(100)'),
  TabSeq2 = t.c.value('./parent::Tab/@sequence', 'nvarchar(100)'),
  '//Tab' + '/' + c.value('local-name(.)[1]','nvarchar(100)') AS Path,  
  t.c.value('@sequence', 'nvarchar(100)') As SubTabSeq
FROM @xml.nodes('//Tab/child::node()') as t(c)

Answer 2

Please try the following solution.

SQL

declare @xml XML = 
N'<Tabs>
    <Tab sequence="001">
        <Family sequence="001">
            <Data>Yes</Data>
        </Family>
        <Location sequence="002">
            <Data>USA</Data>
        </Location>
    </Tab>
    <Tab sequence="002">
        <Address sequence="001">
            <Data>XYZ</Data>
        </Address>
        <Insurance sequence="002">
            <Data>Yes</Data>
        </Insurance>
    </Tab>
</Tabs>';

SELECT c.value('for $i in . return count(../../*[. << $i])', 'INT') AS [TabSeq]
    , '//Tab/' + c.value('local-name(.)','nvarchar(100)') AS Path
    ,  t.c.value('@sequence', 'INT') As SubTabSeq
FROM @xml.nodes('/Tabs/Tab/*') as t(c);

Output

TabSeq	Path	SubTabSeq
1	//Tab/Family	1
1	//Tab/Location	2
2	//Tab/Address	1
2	//Tab/Insurance	2

Answer 3

As mentioned, you are getting the wrong sequence attribute.

You can feed the result of one .nodes call into another using CROSS APPLY, so you can first shred the Tab nodes, then the child nodes

declare @xml xml='<Tabs><Tab sequence="001"><Family sequence="001"><Data>Yes</Data></Family><Location sequence="002"><Data>USA</Data></Location></Tab><Tab sequence="002"><Address sequence="001"><Data>XYZ</Data></Address><Insurance sequence="002"><Data>Yes</Data></Insurance></Tab></Tabs>'

SELECT 
  TabSeq1 = t2.child.value('@sequence', 'nvarchar(100)'),
  TabSeq2 = t1.tab.value('@sequence', 'nvarchar(100)'),
  Path = '/Tabs/Tab/' + t2.child.value('local-name(.)','nvarchar(100)'),
  Data = t2.child.value('(Data/text())[1]', 'nvarchar(100)')
FROM @xml.nodes('/Tabs/Tab') t1(tab)
CROSS APPLY t1.tab.nodes('*') as t2(child)

db<>fiddle

Note that // descendant axis is slow, you should instead use the / child axis.