CODEWITHSUNDEEP
sql-serverxml
user2726975
user2726975

Reputation: 1353

Pick XML nodes in SQL server

I have XML in a SQL server table where data looks like this

DECLARE @XML XML
DECLARE @output XML
SET @XML='<ROOT>
    <HDR>
        <NBR>21</NBR>
        <DT>2009-08-17</DT>
    </HDR>
    <OTHERINFO>
        <RAT>
          <ASMT_DT>2015-08-10</ASMT_DT>
        </RAT>
        <RAT>
          <ASMT_DT>2014-08-10</ASMT_DT>
          <INDICATOR>1</INDICATOR>
        </RAT>
        <RAT>
          <ASMT_DT>2013-08-10</ASMT_DT>
          <INDICATOR>1</INDICATOR>
        </RAT>
    </OTHERINFO>
</ROOT>'

I want to print XML with node does not have <INDICATOR>1</INDICATOR> i.e. like this

SET @output=
'<ROOT>
    <HDR>
        <NBR>21</NBR>
        <DT>2009-08-17</DT>
    </HDR>
    <OTHERINFO>
        <RAT>
           <ASMT_DT>2015-08-10</ASMT_DT>
        </RAT>
    </OTHERINFO>
</ROOT>'

How can I do this? Thanks

Upvotes: 0

Views: 51

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You can use .modify() to get rid of all the nodes you do not want to keep:

SET @XML='<ROOT>
    <HDR>
        <NBR>21</NBR>
        <DT>2009-08-17</DT>
    </HDR>
    <OTHERINFO>
        <RAT>
          <ASMT_DT>2015-08-10</ASMT_DT>
        </RAT>
        <RAT>
          <ASMT_DT>2014-08-10</ASMT_DT>
          <INDICATOR>1</INDICATOR>
        </RAT>
        <RAT>
          <ASMT_DT>2013-08-10</ASMT_DT>
          <INDICATOR>1</INDICATOR>
        </RAT>
    </OTHERINFO>
</ROOT>';

SET @XML.modify('delete /ROOT/OTHERINFO/RAT[INDICATOR=1]');
SELECT @XML;

Another - inlineable! - approach was a FLWOR XQuery:

SELECT @XML.query
('  let $hdr:=/ROOT/HDR
    let $oi:=/ROOT/OTHERINFO
    return
    <ROOT>
    {$hdr}
    <OTHERINFO>
    {
        for $r in $oi/RAT[empty(INDICATOR) or INDICATOR/text()!="1"]
        return $r
    }
    </OTHERINFO>
    </ROOT>
');

Upvotes: 2

Related Questions