sqlsql-serversqlxml

Extract child node value using SQL QUERY

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?

Solution

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