CODEWITHSUNDEEP
sqlsql-serversqlxml
Qwerty
Qwerty

Reputation: 25

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?

Upvotes: 1

Views: 81

Answers (3)

Charlieface
Charlieface

Reputation: 72511

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.

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

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

Upvotes: 0

AdamL
AdamL

Reputation: 13201

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)

Upvotes: 1

Related Questions