I have XML is like this:

<ENVELOPE>
    <DSPACCNAME>
        <DSPDISPNAME>Opening Stock</DSPDISPNAME>
    </DSPACCNAME>
    <PLAMT>
        <PLSUBAMT/>
        <BSMAINAMT>-44912711.35</BSMAINAMT>
    </PLAMT>
    <BSNAME>
        <DSPACCNAME>
            <DSPDISPNAME>Stock-in-Hand-Accesories(KPM)</DSPDISPNAME>
        </DSPACCNAME>
    </BSNAME>
    <BSAMT>
        <BSSUBAMT>-15750.01</BSSUBAMT>
        <BSMAINAMT/>
    </BSAMT>
    <BSNAME>
        <DSPACCNAME>
            <DSPDISPNAME>Stock-in-Hand-Accesories(KVM)</DSPDISPNAME>
        </DSPACCNAME>
    </BSNAME>
    <BSAMT>
        <BSSUBAMT>-16750.01</BSSUBAMT>
        <BSMAINAMT/>
    </BSAMT>
    <DSPACCNAME>
        <DSPDISPNAME>Closing Stock</DSPDISPNAME>
    </DSPACCNAME>
    <BSNAME>
        <DSPACCNAME>
            <DSPDISPNAME>Stock-in-Hand-Accesories(KPM)</DSPDISPNAME>
        </DSPACCNAME>
    </BSNAME>
    <BSAMT>
        <BSSUBAMT>-54750.01</BSSUBAMT>
        <BSMAINAMT/>
    </BSAMT>
    <BSNAME>
        <DSPACCNAME>
            <DSPDISPNAME>Stock-in-Hand-Accesories(KRM)</DSPDISPNAME>
        </DSPACCNAME>
    </BSNAME>
    <BSAMT>
        <BSSUBAMT>-74750.01</BSSUBAMT>
        <BSMAINAMT/>
    </BSAMT>
</ENVELOPE>

My result should come as:

Opening Stock   Stock-in-Hand-Accesories(KPM)   -15750.01
Opening Stock   Stock-in-Hand-Accesories(KVM)   -16750.01
Closing Stock   Stock-in-Hand-Accesories(KPM)   -54750.01
Closing Stock   Stock-in-Hand-Accesories(KRM)   -74750.01

2 Answers

2
Salman A On Best Solutions

Assuming that the only relation is the order of nodes, you can select all BSNAME nodes as anchors, and use the >> and << operator to find nearest BSAMT and DSPACCNAME nodes:

DECLARE @xml XML = '<ENVELOPE>
    <DSPACCNAME>
        <DSPDISPNAME>Opening Stock</DSPDISPNAME>
    </DSPACCNAME>
    <PLAMT>
        <PLSUBAMT/>
        <BSMAINAMT>-44912711.35</BSMAINAMT>
    </PLAMT>
    <BSNAME>
        <DSPACCNAME>
            <DSPDISPNAME>Stock-in-Hand-Accesories(KPM)</DSPDISPNAME>
        </DSPACCNAME>
    </BSNAME>
    <BSAMT>
        <BSSUBAMT>-15750.01</BSSUBAMT>
        <BSMAINAMT/>
    </BSAMT>
    <BSNAME>
        <DSPACCNAME>
            <DSPDISPNAME>Stock-in-Hand-Accesories(KVM)</DSPDISPNAME>
        </DSPACCNAME>
    </BSNAME>
    <BSAMT>
        <BSSUBAMT>-16750.01</BSSUBAMT>
        <BSMAINAMT/>
    </BSAMT>
    <DSPACCNAME>
        <DSPDISPNAME>Closing Stock</DSPDISPNAME>
    </DSPACCNAME>
    <BSNAME>
        <DSPACCNAME>
            <DSPDISPNAME>Stock-in-Hand-Accesories(KPM)</DSPDISPNAME>
        </DSPACCNAME>
    </BSNAME>
    <BSAMT>
        <BSSUBAMT>-54750.01</BSSUBAMT>
        <BSMAINAMT/>
    </BSAMT>
    <BSNAME>
        <DSPACCNAME>
            <DSPDISPNAME>Stock-in-Hand-Accesories(KRM)</DSPDISPNAME>
        </DSPACCNAME>
    </BSNAME>
    <BSAMT>
        <BSSUBAMT>-74750.01</BSSUBAMT>
        <BSMAINAMT/>
    </BSAMT>
</ENVELOPE>';
SELECT refnode.value('(./DSPACCNAME/DSPDISPNAME)[1]', 'VARCHAR(100)') AS [BSNAME]
     , refnode.value('let $c := . return (../BSAMT[. >> $c]/BSSUBAMT)[1]', 'DECIMAL(18,2)') AS [BSAMT]
     , refnode.value('let $c := . return (../DSPACCNAME[. << $c]/DSPDISPNAME)[last()]', 'VARCHAR(100)') AS [DSPACCNAME]
FROM @xml.nodes('//ENVELOPE/BSNAME') x(refnode)

Demo on db<>fiddle

0
Mitalee Rao On

While this was okay to do as Salman A has suggested, it is highly susceptible to errors. It looks like this XML was formed with a custom TDL (Tally add-on). In this case, I agree with Panagiotis Kanavos that the XML is not properly formed. You can have the TDL developer simply re-arrange the XML tags by putting an XML tag at the 'Line' Level for every parent node, and at the 'Field' Level for every child node. Note that the Line must be repeated using 'Repeat' variable for multiple parent nodes with child nodes to come up in the XML.

You can refer this reference guide, specifically the section 2.2.5 , on how to use XML tags.