SQL Server - XQuery: delete parent node based on child value substring

Question

I want to delete all parent nodes TxDtls of the following XML where position 20 of child value Ref is 2.

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04 camt.054.001.04.xsd">
  <BkToCstmrDbtCdtNtfctn>
    <Ntfctn>
      <Ntry>
          <TtlChrgsAndTaxAmt Ccy="CHF">1.60</TtlChrgsAndTaxAmt>
          <Rcrd>
            <Amt Ccy="CHF">1.60</Amt>
            <CdtDbtInd>DBIT</CdtDbtInd>
            <ChrgInclInd>false</ChrgInclInd>
            <Tp>
              <Prtry>
                <Id>2</Id>
              </Prtry>
            </Tp>
          </Rcrd>
        </Chrgs>
        <NtryDtls>
           <TxDtls>
            <RmtInf>
              <Strd>
                <CdtrRefInf>
                  <Ref>111118144400000000020076766</Ref>
                </CdtrRefInf>
               </Strd>
            </RmtInf>
           </TxDtls>
          <TxDtls>
             <RmtInf>
              <Strd>
                <CdtrRefInf>
                  <Ref>111117645600000000030076281</Ref>
                </CdtrRefInf>
              </Strd>
            </RmtInf>
          </TxDtls>
        </NtryDtls>
      </Ntry>
    </Ntfctn>
  </BkToCstmrDbtCdtNtfctn>
</Document>

So I want to delete the first TxDtls node (substring position 20 = 2) while I want to keep the second one (substring position 20 <> 2).

I tried this:

UPDATE mytable SET XMLData.modify('delete .//TxDtls[RmtInf/Strd/CdtrRefInf/Ref/substring(text(),20,1) = ''2'']')

However, I get the error "The XQuery syntax '/function()' is not supported". Any hints on how to achieve this?

Thanks

While asking a question, you need to provide a **minimal reproducible example**. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Jun 01 '21 at 16:56
You can't delete a substring, that doesn't make sense. What are you trying to do? Sample data and expected output would help — Charlieface, Jun 01 '21 at 18:25

Yitzhak Khabinsky · Accepted Answer · 2021-06-01T22:51:17.730

What a difference made by the partially provided minimal reproducible example.

The XML is still not well-formed. I had to comment out the following tag: </Chrgs>

A default namespace is easily handled by its declaration in the XQuery method.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<?xml version="1.0"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04 camt.054.001.04.xsd">
    <BkToCstmrDbtCdtNtfctn>
        <Ntfctn>
            <Ntry>
                <TtlChrgsAndTaxAmt Ccy="CHF">1.60</TtlChrgsAndTaxAmt>
                <Rcrd>
                    <Amt Ccy="CHF">1.60</Amt>
                    <CdtDbtInd>DBIT</CdtDbtInd>
                    <ChrgInclInd>false</ChrgInclInd>
                    <Tp>
                        <Prtry>
                            <Id>2</Id>
                        </Prtry>
                    </Tp>
                </Rcrd>
                <!--</Chrgs>-->
                <NtryDtls>
                    <TxDtls>
                        <RmtInf>
                            <Strd>
                                <CdtrRefInf>
                                    <Ref>111118144400000000020076766</Ref>
                                </CdtrRefInf>
                            </Strd>
                        </RmtInf>
                    </TxDtls>
                    <TxDtls>
                        <RmtInf>
                            <Strd>
                                <CdtrRefInf>
                                    <Ref>111117645600000000030076281</Ref>
                                </CdtrRefInf>
                            </Strd>
                        </RmtInf>
                    </TxDtls>
                </NtryDtls>
            </Ntry>
        </Ntfctn>
    </BkToCstmrDbtCdtNtfctn>
</Document>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

UPDATE @tbl SET xmldata.modify('declare default element namespace "urn:iso:std:iso:20022:tech:xsd:camt.054.001.04";
delete /Document/BkToCstmrDbtCdtNtfctn/Ntfctn/Ntry/NtryDtls/TxDtls[RmtInf/Strd/CdtrRefInf/Ref[substring(./text()[1],20,1) = "2"]]');

-- after
SELECT * FROM @tbl;

This is it, thank you so much! I had tried for hours before... — M4tee, Jun 02 '21 at 05:40

SQL Server - XQuery: delete parent node based on child value substring

1 Answers1