CODEWITHSUNDEEP
sql-serverxmlt-sql
scrounger
scrounger

Reputation: 48

Delete an element if any level of Children or Children's children doesn't have a praticular value

I have an XML column in the SQL server table. I want to delete certain elements and extract the rest info from that XML column.

CREATE TABLE #TempTable (xmlDoc xml)

INSERT INTO #TempTable (xmlDoc)
SELECT TOP 1 ai.[XMLDocument].value('(/RequestInfo/TransDocument)[1]','varchar(max)') AS xmlDoc FROM [dbo].[FormDetails] WHERE  [FormID] = xyz

I want to delete all those elements if any level of children(or children's children) does not have a particular value. In the example given below I want to delete all the <FormDetail> elements where USStateCd NOT EQUAL to 'NJ'

XML :

<Form>
 <FormDetail>
       <EmployeeInfoGrp>
        <OtherCompletePersonName>
          <PersonFirstNm>LALALALALI</PersonFirstNm>
          <PersonMiddleNm>S</PersonMiddleNm>
          <PersonLastNm>LILILILA</PersonLastNm>
        </OtherCompletePersonName>
        <MailingAddressGrp>
          <USAddressGrp>
            <AddressLine1Txt>108 ABCD AVE</AddressLine1Txt>
            <CityNm>SPRINGFIELD</CityNm>
            ***<USStateCd>TN</USStateCd>***
            <USZIPCd>37100</USZIPCd>
          </USAddressGrp>
        </MailingAddressGrp>
      </EmployeeInfoGrp>
  </FormDetail>

 <FormDetail>
       <EmployeeInfoGrp>
        <OtherCompletePersonName>
          <PersonFirstNm>CHACHACHACHA</PersonFirstNm>
          <PersonMiddleNm>D</PersonMiddleNm>
          <PersonLastNm>CHICHICHI</PersonLastNm>
        </OtherCompletePersonName>
        <MailingAddressGrp>
          <USAddressGrp>
            <AddressLine1Txt> EFGH AVE</AddressLine1Txt>
            <CityNm>bLah</CityNm>
            ***<USStateCd>NJ</USStateCd>***
            <USZIPCd>00111</USZIPCd>
          </USAddressGrp>
        </MailingAddressGrp>
      </EmployeeInfoGrp>
  </FormDetail>
</Form>

Upvotes: 0

Views: 34

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22283

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata)
VALUES
(N'<Form xmlns="urn:us:go:tr:msg:formtransmitterupstreammessage" xmlns:xsd="w3.org/2001/XMLSchema"
      xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:abc="urn:us:gov:treasury:irs:common">
    <FormDetail xmlns="urn:us:go:tr:abc:ext:air" recordType="String" lineNum="0">
        <EmployeeInfoGrp>
            <OtherCompletePersonName>
                <PersonFirstNm>LALALALALI</PersonFirstNm>
                <PersonMiddleNm>S</PersonMiddleNm>
                <PersonLastNm>LILILILA</PersonLastNm>
            </OtherCompletePersonName>
            <MailingAddressGrp>
                <USAddressGrp>
                    <AddressLine1Txt>108 ABCD AVE</AddressLine1Txt>
                    <CityNm>SPRINGFIELD</CityNm>
                    <USStateCd>TN</USStateCd>
                    <USZIPCd>37100</USZIPCd>
                </USAddressGrp>
            </MailingAddressGrp>
        </EmployeeInfoGrp>
    </FormDetail>
    <FormDetail xmlns="urn:us:go:tr:abc:ext:air" recordType="String" lineNum="0">
        <EmployeeInfoGrp>
            <OtherCompletePersonName>
                <PersonFirstNm>CHACHACHACHA</PersonFirstNm>
                <PersonMiddleNm>D</PersonMiddleNm>
                <PersonLastNm>CHICHICHI</PersonLastNm>
            </OtherCompletePersonName>
            <MailingAddressGrp>
                <USAddressGrp>
                    <AddressLine1Txt>EFGH AVE</AddressLine1Txt>
                    <CityNm>bLah</CityNm>
                    <USStateCd>NJ</USStateCd>
                    <USZIPCd>00111</USZIPCd>
                </USAddressGrp>
            </MailingAddressGrp>
        </EmployeeInfoGrp>
    </FormDetail>
</Form>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @state CHAR(2) = 'NJ';

;WITH XMLNAMESPACES (DEFAULT 'urn:us:go:tr:abc:ext:air'
    , 'urn:us:go:tr:msg:formtransmitterupstreammessage' AS ns1)
UPDATE @tbl
SET xmldata.modify('delete /ns1:Form/FormDetail[(EmployeeInfoGrp/MailingAddressGrp/USAddressGrp/USStateCd/text())[1] ne sql:variable("@state")]');

-- after
SELECT * FROM @tbl;

Output

<Form xmlns="urn:us:go:tr:msg:formtransmitterupstreammessage" xmlns:xsd="w3.org/2001/XMLSchema" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:abc="urn:us:gov:treasury:irs:common">
  <FormDetail xmlns="urn:us:go:tr:abc:ext:air" recordType="String" lineNum="0">
    <EmployeeInfoGrp>
      <OtherCompletePersonName>
        <PersonFirstNm>CHACHACHACHA</PersonFirstNm>
        <PersonMiddleNm>D</PersonMiddleNm>
        <PersonLastNm>CHICHICHI</PersonLastNm>
      </OtherCompletePersonName>
      <MailingAddressGrp>
        <USAddressGrp>
          <AddressLine1Txt>EFGH AVE</AddressLine1Txt>
          <CityNm>bLah</CityNm>
          <USStateCd>NJ</USStateCd>
          <USZIPCd>00111</USZIPCd>
        </USAddressGrp>
      </MailingAddressGrp>
    </EmployeeInfoGrp>
  </FormDetail>
</Form>

Upvotes: 2

Related Questions