Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Edit XML File In SQL

Author  Topic 

Pete_N
Posting Yak Master

181 Posts
Posted - 2013-11-10 : 16:05:16
We receive xml files from a third party which are 'split' internally and re-distributed to our customers. I sample of a partial file below.

I need to get from
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY A" number="11111111" sortCode="11111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="121212" transCode="01" returnCode="1304" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-10-31" valueOf="1.00" currency="GBP">
<PayerAccount number="11111111" ref="121212" name="A NAME" sortCode="111111" bankName="BARCLAYS BANK PLC" branchName="BRANCH" />
</ReturnedDebitItem>
<Totals numberOf="1" valueOf="1.00" currency="GBP" />
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY B" number="1111111" sortCode="111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="323232" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="11111111" ref="323232" name="NAME 2" sortCode="111111" bankName="B BRANCH" branchName=" BRANCH " />
</ReturnedDebitItem>
<ReturnedDebitItem ref="454545" transCode="17" returnCode="0305" returnDescription="REFER TO PAYER" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="111111" ref="989898" name="A NAME" sortCode="111111" bankName="METRO BANK" branchName="London" />
</ReturnedDebitItem>
<Totals numberOf="2" valueOf="2.00" currency="GBP" />
</OriginatingAccountRecord>
</OriginatingAccountRecords>

to

<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY A" number="11111111" sortCode="11111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="121212" transCode="01" returnCode="1304" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-10-31" valueOf="1.00" currency="GBP">
<PayerAccount number="11111111" ref="121212" name="A NAME" sortCode="111111" bankName="BARCLAYS BANK PLC" branchName="BRANCH" />
</ReturnedDebitItem>
<ReturnedDebitItem ref="323232" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="11111111" ref="323232" name="NAME 2" sortCode="111111" bankName="B BRANCH" branchName=" BRANCH " />
</ReturnedDebitItem>
<ReturnedDebitItem ref="454545" transCode="17" returnCode="0305" returnDescription="REFER TO PAYER" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="111111" ref="989898" name="A NAME" sortCode="111111" bankName="METRO BANK" branchName="London" />
</ReturnedDebitItem>
<Totals numberOf="3" valueOf="3.00" currency="GBP" />
</OriginatingAccountRecord>
</OriginatingAccountRecords>


There can me x number of <OriginatingAccountRecord>
<OriginatingAccount tags and Totals, but I only need the first one and the last totals in a files

is this possible ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-11-11 : 02:39:05
[code]
declare @x xml='<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY A" number="11111111" sortCode="11111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="121212" transCode="01" returnCode="1304" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-10-31" valueOf="1.00" currency="GBP">
<PayerAccount number="11111111" ref="121212" name="A NAME" sortCode="111111" bankName="BARCLAYS BANK PLC" branchName="BRANCH" />
</ReturnedDebitItem>
<Totals numberOf="1" valueOf="1.00" currency="GBP" />
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY B" number="1111111" sortCode="111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="323232" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="11111111" ref="323232" name="NAME 2" sortCode="111111" bankName="B BRANCH" branchName=" BRANCH " />
</ReturnedDebitItem>
<ReturnedDebitItem ref="454545" transCode="17" returnCode="0305" returnDescription="REFER TO PAYER" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="111111" ref="989898" name="A NAME" sortCode="111111" bankName="METRO BANK" branchName="London" />
</ReturnedDebitItem>
<Totals numberOf="2" valueOf="2.00" currency="GBP" />
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY C" number="1111111" sortCode="111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="1234211" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="2134342" ref="235235" name="NAME ZZZ" sortCode="111111" bankName="BASL BRANCH" branchName=" BRANCH " />
</ReturnedDebitItem>
<Totals numberOf="1" valueOf="1.00" currency="GBP" />
</OriginatingAccountRecord>
</OriginatingAccountRecords>',
@RetDebitItemXML xml,@Number int,@Val int
select @RetDebitItemXML=@x.query('//OriginatingAccountRecord[position()!= 1]/ReturnedDebitItem')
select @Number=@x.query('sum(//Totals/@numberOf)').value('.','int'),@Val=@x.query('sum(//Totals/@valueOf)').value('.','int')
set @x.modify (' insert (sql:variable("@RetDebitItemXML")) after (/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/ReturnedDebitItem[1])')
set @x.modify (' delete (/OriginatingAccountRecords[1]/OriginatingAccountRecord[position()!=1])')
set @x.modify (' replace value of (/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/Totals[1]/@numberOf) with sql:variable("@Number")')
set @x.modify (' replace value of (/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/Totals[1]/@valueOf) with xs:decimal(sql:variable("@Val"))')
select @x


output
------------------------------------------
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY A" number="11111111" sortCode="11111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="121212" transCode="01" returnCode="1304" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-10-31" valueOf="1.00" currency="GBP">
<PayerAccount number="11111111" ref="121212" name="A NAME" sortCode="111111" bankName="BARCLAYS BANK PLC" branchName="BRANCH" />
</ReturnedDebitItem>
<ReturnedDebitItem ref="323232" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="11111111" ref="323232" name="NAME 2" sortCode="111111" bankName="B BRANCH" branchName=" BRANCH " />
</ReturnedDebitItem>
<ReturnedDebitItem ref="454545" transCode="17" returnCode="0305" returnDescription="REFER TO PAYER" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="111111" ref="989898" name="A NAME" sortCode="111111" bankName="METRO BANK" branchName="London" />
</ReturnedDebitItem>
<ReturnedDebitItem ref="1234211" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="1.00" currency="GBP">
<PayerAccount number="2134342" ref="235235" name="NAME ZZZ" sortCode="111111" bankName="BASL BRANCH" branchName=" BRANCH " />
</ReturnedDebitItem>
<Totals numberOf="4" valueOf="4" currency="GBP" />
</OriginatingAccountRecord>
</OriginatingAccountRecords>

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Pete_N
Posting Yak Master

181 Posts
Posted - 2013-11-11 : 12:35:15
Hi Visakh

Thank you for your help with the extract XML i supplied. I have tried to expand your working to fit the compled XML file, but no matter what i do i cannot get to the desired result.

declare @x xml='<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2013-11-05" />
<AddresseeInformation name="XXXXXXXXXXXXXXXXX" />
<ServiceLicenseInformation userName="XXXXXXXXXXXXXXXXXX LIMITED" userNumber="XXXXXX" />
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY A" number="11111111" sortCode="111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="12121212" transCode="01" returnCode="1304" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-10-31" valueOf="50.00" currency="GBP">
<PayerAccount number="11111111" ref="12121212" name="CUSTOMER 1" sortCode="222222" bankName="BARCLAYS BANK PLC" branchName="AYLESBURY MARKET SQ" />
</ReturnedDebitItem>
<Totals numberOf="1" valueOf="50.00" currency="GBP" />
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY B" number="11111111" sortCode="111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="321321321" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="120.00" currency="GBP">
<PayerAccount number="11111111" ref="321321321" name="CUSTOMER 2" sortCode="555555" bankName="BARCLAYS BANK PLC" branchName="EPSOM" />
</ReturnedDebitItem>
<ReturnedDebitItem ref="9A6542B" transCode="17" returnCode="0305" returnDescription="REFER TO PAYER" originalProcessingDate="2013-11-01" valueOf="18.50" currency="GBP">
<PayerAccount number="88885555" ref="9A6542B" name="CUSTOMER 3" sortCode="585858" bankName="METRO BANK" branchName="London" />
</ReturnedDebitItem>
<Totals numberOf="2" valueOf="138.50" currency="GBP" />
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data>
<SignatureMethod>Vanilla</SignatureMethod>
<Signature></Signature>
</BACSDocument>',
@RetDebitItemXML xml,@Number int,@Val int
select @RetDebitItemXML=@x.query('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord[position()!= 1]/ReturnedDebitItem')
select @Number=@x.query('sum(/OriginatingAccountRecords/OriginatingAccountRecord/Totals/@numberOf)').value('.','float')
select @Val=@x.query('sum(/OriginatingAccountRecords/OriginatingAccountRecord/Totals/@valueOf)').value('.','float')
set @x.modify (' insert (sql:variable("@RetDebitItemXML")) after (/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/ReturnedDebitItem[1])')
set @x.modify (' delete (/OriginatingAccountRecords[1]/OriginatingAccountRecord[position()!=1])')
set @x.modify (' replace value of (/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/Totals[1]/@numberOf) with sql:variable("@Number")')
set @x.modify (' replace value of (/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/Totals[1]/@valueOf) with xs:decimal(sql:variable("@Val"))')
select @x
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-11-11 : 12:49:48
Thats why i always suggest to post illustration of actual scenario

declare @x xml='<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2013-11-05" />
<AddresseeInformation name="XXXXXXXXXXXXXXXXX" />
<ServiceLicenseInformation userName="XXXXXXXXXXXXXXXXXX LIMITED" userNumber="XXXXXX" />
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY A" number="11111111" sortCode="111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="12121212" transCode="01" returnCode="1304" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-10-31" valueOf="50.00" currency="GBP">
<PayerAccount number="11111111" ref="12121212" name="CUSTOMER 1" sortCode="222222" bankName="BARCLAYS BANK PLC" branchName="AYLESBURY MARKET SQ" />
</ReturnedDebitItem>
<Totals numberOf="1" valueOf="50.00" currency="GBP" />
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY B" number="11111111" sortCode="111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
<ReturnedDebitItem ref="321321321" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="120.00" currency="GBP">
<PayerAccount number="11111111" ref="321321321" name="CUSTOMER 2" sortCode="555555" bankName="BARCLAYS BANK PLC" branchName="EPSOM" />
</ReturnedDebitItem>
<ReturnedDebitItem ref="9A6542B" transCode="17" returnCode="0305" returnDescription="REFER TO PAYER" originalProcessingDate="2013-11-01" valueOf="18.50" currency="GBP">
<PayerAccount number="88885555" ref="9A6542B" name="CUSTOMER 3" sortCode="585858" bankName="METRO BANK" branchName="London" />
</ReturnedDebitItem>
<Totals numberOf="2" valueOf="138.50" currency="GBP" />
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data>
<SignatureMethod>Vanilla</SignatureMethod>
<Signature></Signature>
</BACSDocument>',
@RetDebitItemXML xml,@Number int,@Val int

select @RetDebitItemXML=@x.query('//OriginatingAccountRecords/OriginatingAccountRecord[position()!= 1]/ReturnedDebitItem')
select @Number=@x.query('sum(//OriginatingAccountRecords/OriginatingAccountRecord/Totals/@numberOf)').value('.','float')
select @Val=@x.query('sum(//OriginatingAccountRecords/OriginatingAccountRecord/Totals/@valueOf)').value('.','float') 
set @x.modify (' insert (sql:variable("@RetDebitItemXML")) after (/BACSDocument[1]/Data[1]/ARUDD[1]/Advice[1]/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/ReturnedDebitItem[1])')
set @x.modify (' delete (/BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords[1]/OriginatingAccountRecord[position()!=1])')
set @x.modify (' replace value of (/BACSDocument[1]/Data[1]/ARUDD[1]/Advice[1]/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/Totals[1]/@numberOf) with sql:variable("@Number")')
set @x.modify (' replace value of (/BACSDocument[1]/Data[1]/ARUDD[1]/Advice[1]/OriginatingAccountRecords[1]/OriginatingAccountRecord[1]/Totals[1]/@valueOf) with xs:decimal(sql:variable("@Val"))')
select @x


output
------------------------------------------
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
  <Data>
    <ARUDD>
      <Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2013-11-05" />
      <AddresseeInformation name="XXXXXXXXXXXXXXXXX" />
      <ServiceLicenseInformation userName="XXXXXXXXXXXXXXXXXX LIMITED" userNumber="XXXXXX" />
      <Advice>
        <OriginatingAccountRecords>
          <OriginatingAccountRecord>
            <OriginatingAccount name="COMPANY A" number="11111111" sortCode="111111" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS" />
            <ReturnedDebitItem ref="12121212" transCode="01" returnCode="1304" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-10-31" valueOf="50.00" currency="GBP">
              <PayerAccount number="11111111" ref="12121212" name="CUSTOMER 1" sortCode="222222" bankName="BARCLAYS BANK PLC" branchName="AYLESBURY MARKET SQ" />
            </ReturnedDebitItem>
            <ReturnedDebitItem ref="321321321" transCode="01" returnCode="1305" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-11-01" valueOf="120.00" currency="GBP">
              <PayerAccount number="11111111" ref="321321321" name="CUSTOMER 2" sortCode="555555" bankName="BARCLAYS BANK PLC" branchName="EPSOM" />
            </ReturnedDebitItem>
            <ReturnedDebitItem ref="9A6542B" transCode="17" returnCode="0305" returnDescription="REFER TO PAYER" originalProcessingDate="2013-11-01" valueOf="18.50" currency="GBP">
              <PayerAccount number="88885555" ref="9A6542B" name="CUSTOMER 3" sortCode="585858" bankName="METRO BANK" branchName="London" />
            </ReturnedDebitItem>
            <Totals numberOf="3" valueOf="188" currency="GBP" />
          </OriginatingAccountRecord>
        </OriginatingAccountRecords>
      </Advice>
    </ARUDD>
  </Data>
  <SignatureMethod>Vanilla</SignatureMethod>
  <Signature />
</BACSDocument>



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Pete_N
Posting Yak Master

181 Posts
Posted - 2013-11-11 : 13:03:25
Hi Visakh,

That's perfect thank you. I didn't post the full xml previously as I wanted to try and finish working the solution and learn something instead of just asking for the full answer. Lesson learned and I will remember to post the full scenario in future

once again thanks for you help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-11-11 : 13:12:45
quote:
Originally posted by Pete_N

Hi Visakh,

That's perfect thank you. I didn't post the full xml previously as I wanted to try and finish working the solution and learn something instead of just asking for the full answer. Lesson learned and I will remember to post the full scenario in future

once again thanks for you help

No problem
I'm happy if you're able to extend it to get your solution so that it'll improve your knowledge . I just suggested to post the actual scenario so that it will help you to get accurate solution in minimal time.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site