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?

AdamL · Accepted Answer · 2022-10-13 13:10:05Z

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)

answered Oct 13, 2022 at 13:10

AdamL

13.3k5 gold badges57 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yitzhak Khabinsky · Accepted Answer · 2022-10-13 13:17:08Z

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

Charlieface · Accepted Answer · 2022-10-13 15:38:44Z

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.

Collectives™ on Stack Overflow

Extract child node value using SQL QUERY

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related