Removing tagds from XML

1

I receive XML files which I need to remove data before forwarding them to clients. in the instance below, if I supply a parameter value for ref ie only the first 6 digits , how can I delete the ReturnedDebitItem that doesn't starte with the parameter value ie <> Left (ref , 6) Below is the before and what I want to get to if I supply 121212 as the parameter. opening and closing tags removed to post

"BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd""
"Data"
"ARUDD"
"Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2018-01-03" /"
"AddresseeInformation name="SOME COMPANY" /"
"ServiceLicenseInformation userName="SOME COMPANY" userNumber="000000" /"
"Advice"
"OriginatingAccountRecords"
"OriginatingAccountRecord"
"OriginatingAccount name="SOME COMPANY ACCOUNT" number="11111111" sortCode="11-11-11" type="0" bankName="BANK" branchName="SOME TOWN" /"
"ReturnedDebitItem ref="121212TEST1" transCode="17" returnCode="1363" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP""
"PayerAccount number="12345678" ref="121212TEST1" name="JOHN" sortCode="12-34-56" /"
"/ReturnedDebitItem"
"ReturnedDebitItem ref="121212TEST2" transCode="17" returnCode="5363" returnDescription="NO ACCOUNT" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP""
"PayerAccount number="87654321" ref="121212TEST2" name="PAUL" sortCode="09-09-09" /"
"/ReturnedDebitItem"
"ReturnedDebitItem ref="321654TEST1" transCode="17" returnCode="1363" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2017-12-29" valueOf="30.00" currency="GBP""
"PayerAccount number="01100110" ref="321654TEST1" name="PETER" sortCode="01-01-01" /"
"/ReturnedDebitItem"
"ReturnedDebitItem ref="121212TEST3" transCode="17" returnCode="1363" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP""
"PayerAccount number="33333333" ref="121212TEST3" name="MARY" sortCode="33-33-33" /"
"/ReturnedDebitItem"
"/OriginatingAccountRecord"
"/OriginatingAccountRecords"
"/Advice"
"/ARUDD"
"/Data"
"/BACSDocument"

End up with

"BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd""
"Data"
"ARUDD"
"Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2018-01-03" /"
"AddresseeInformation name="SOME COMPANY" /"
"ServiceLicenseInformation userName="SOME COMPANY" userNumber="000000" /"
"Advice"
"OriginatingAccountRecords"
"OriginatingAccountRecord"
"OriginatingAccount name="SOME COMPANY ACCOUNT" number="11111111" sortCode="11-11-11" type="0" bankName="BANK" branchName="SOME TOWN" /"
"ReturnedDebitItem ref="121212TEST1" transCode="17" returnCode="1363" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP""
"PayerAccount number="12345678" ref="121212TEST1" name="JOHN" sortCode="12-34-56" /"
"/ReturnedDebitItem"
"ReturnedDebitItem ref="121212TEST2" transCode="17" returnCode="5363" returnDescription="NO ACCOUNT" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP""
"PayerAccount number="87654321" ref="121212TEST2" name="PAUL" sortCode="09-09-09" /"
"/ReturnedDebitItem"
"ReturnedDebitItem ref="121212TEST3" transCode="17" returnCode="1363" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP""
"PayerAccount number="33333333" ref="121212TEST3" name="MARY" sortCode="33-33-33" /"
"/ReturnedDebitItem"
"/OriginatingAccountRecord"
"/OriginatingAccountRecords"
"/Advice"
"/ARUDD"
"/Data"
"/BACSDocument"

2

Normally I wouldn't do in the database, I'd use frontend program (c# or vb), but just to show, that it can be done:

Query 
declare @xmltext varchar(max)='
<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
 <Data>
  <ARUDD>
   <Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2018-01-03" />
   <AddresseeInformation name="SOME COMPANY" />
   <ServiceLicenseInformation userName="SOME COMPANY" userNumber="000000" />
   <Advice>
    <OriginatingAccountRecords>
     <OriginatingAccountRecord>
      <OriginatingAccount name="SOME COMPANY ACCOUNT" number="11111111" sortCode="11-11-11" type="0" bankName="BANK" branchName="SOME TOWN" />
      <ReturnedDebitItem ref="121212TEST1" transCode="17" returnCode="1363" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP">
       <PayerAccount number="12345678" ref="121212TEST1" name="JOHN" sortCode="12-34-56" />
      </ReturnedDebitItem>
      <ReturnedDebitItem ref="121212TEST2" transCode="17" returnCode="5363" returnDescription="NO ACCOUNT" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP">
       <PayerAccount number="87654321" ref="121212TEST2" name="PAUL" sortCode="09-09-09" />
      </ReturnedDebitItem>
      <ReturnedDebitItem ref="321654TEST1" transCode="17" returnCode="1363" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2017-12-29" valueOf="30.00" currency="GBP">
       <PayerAccount number="01100110" ref="321654TEST1" name="PETER" sortCode="01-01-01" />
      </ReturnedDebitItem>
      <ReturnedDebitItem ref="121212TEST3" transCode="17" returnCode="1363" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2017-12-29" valueOf="60.00" currency="GBP">
       <PayerAccount number="33333333" ref="121212TEST3" name="MARY" sortCode="33-33-33" />
      </ReturnedDebitItem>
     </OriginatingAccountRecord>
    </OriginatingAccountRecords>
   </Advice>
  </ARUDD>
 </Data>
</BACSDocument>'
;

declare @xmlsearch varchar(10)='121212';

with cte1 /* split into lines */
  as (select itemnumber
            ,item+'>' as item
        from dbo.DelimitedSplit8K(@xmltext,'>')
       where item!=''
     )
    ,cte2 /* find start end end tags of returneddebititem */
  as (select itemnumber
            ,item
            ,row_number() over(partition by case
                                               when item like '%<returneddebititem ref="%'
		      								 then 1
				      						 else 2
                                            end
                                   order by itemnumber
                              ) as rn
        from cte1
       where item like '%<returneddebititem ref="%'
          or item like '%</returneddebititem>%'
     )
    ,cte3 /* find min and max linenumbers */
  as (select min(itemnumber) as minitemnumber
            ,max(itemnumber) as maxitemnumber
        from cte2
       group by rn
       having sum(case when item like '%<returneddebititem ref="'+@xmlsearch+'%' then 1 else 0 end)=0
     )
    ,cte4 /* find lines to exclude */
  as (select a.itemnumber
        from cte1 as a
             inner join cte3 as b
                     on a.ItemNumber between b.minitemnumber and b.maxitemnumber
       group by a.itemnumber
               ,a.item
     )
select a.item
  from cte1 as a
 where not exists (select 1
                     from cte4 as b
                    where b.itemnumber=a.itemnumber
                  )
 order by a.itemnumber
;

Now, someone familiar to xml functions in mssql will probably do this much more efficient and nice, but hey, I almost never use xml functions in mssql.