none
Complex XMLDoc RRS feed

  • Question

  • I am writing a workflow for CRM. II have successfully dealt with other XML files , but they only contain data for a single customer. Currently I receive his type of XML file that I need to break down for individual customers. I'm ok with a simple file ( i.e. only one customer on it), but when it comes to the files containing multiple customers , I'm stuck. The history is the file is taken from an entity in CRM, loaded into stream memory, I interrogate the system, do some bits n pieces and then load the completed data to a different entity in CRM. With this report, I need to create three different xml files ( in memory ) which only contains the data for each customer. The customer is determined by the first 6 characters in the reference element. I'm presuming that I would need to first get a list of the customers and then use the original memory stream to create a new xml file in memory. I also need to amend some of the data in the new files i.e.   change the subject-first-aosn and subject-last-aosn to reflect the aosn elements. If possible I would like to add a new element to each MessagingAdvice tag 'actiontaken'. I would like to be able to do this without creating a physical file. I'm thinking that id be looking at xmldoc.loadXml(customer)Below I have an sample file with the three results I m looking for. Any help would be most apricated

    <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
      <Data>
        <MessagingAdvices>
          <MessagingHeader document-number="0" advice-type="ADDACS" subject-first-aosn="00040784" subject-last-aosn="00040784" user-number="XXXXXX" stream-identifier="0" envelope-sequence-number="00010088" report-generation-date="2018-07-18" user-name="SOME COMPANY" report-type="7001" />
          <AddresseeInformation name=" " />
          <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-07-18" reference="123456ABC" payer-name="PAYER 1" payer-account-number="88888888" payer-sort-code="555555" reason-code="1" aosn="00040784" />
         <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-07-18" reference="999999REF" payer-name="PAYER 2" payer-account-number="88888888" payer-sort-code="555555" reason-code="1" aosn="00040785" />
         <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-07-18" reference="123456XYZ" payer-name="PAYER 27" payer-account-number="88888888" payer-sort-code="555555" reason-code="1" aosn="00040786" />
         <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-07-18" reference="369851ABC" payer-name="MR SMITH" payer-account-number="88888888" payer-sort-code="555555" reason-code="1" aosn="00040787" />
         <MessagingError />
        </MessagingAdvices>
      </Data>
    </BACSDocument>
    
    XML 1
    
    <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
      <Data>
        <MessagingAdvices>
          <MessagingHeader document-number="0" advice-type="ADDACS" subject-first-aosn="00040784" subject-last-aosn="00040784" user-number="XXXXXX" stream-identifier="0" envelope-sequence-number="00010088" report-generation-date="2018-07-18" user-name="SOME COMPANY" report-type="7001" />
          <AddresseeInformation name=" " />
          <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-07-18" reference="123456ABC" payer-name="PAYER 1" payer-account-number="88888888" payer-sort-code="555555" reason-code="1" aosn="00040784"  actiontaken="Some Action"/>
         <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-07-18" reference="123456XYZ" payer-name="PAYER 27" payer-account-number="88888888" payer-sort-code="555555" reason-code="1" aosn="00040786"  actiontaken="Some Action" />
         <MessagingError />
        </MessagingAdvices>
      </Data>
    </BACSDocument>
    
    XML2
    
    <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
      <Data>
        <MessagingAdvices>
          <MessagingHeader document-number="0" advice-type="ADDACS" subject-first-aosn="00040784" subject-last-aosn="00040784" user-number="XXXXXX" stream-identifier="0" envelope-sequence-number="00010088" report-generation-date="2018-07-18" user-name="SOME COMPANY" report-type="7001" />
          <AddresseeInformation name=" " />
         <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-07-18" reference="999999REF" payer-name="PAYER 2" payer-account-number="88888888" payer-sort-code="555555" reason-code="1" aosn="00040785"  actiontaken="Some Action" />
         <MessagingError />
        </MessagingAdvices>
      </Data>
    </BACSDocument>
    
    XML3
    
    <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
      <Data>
        <MessagingAdvices>
          <MessagingHeader document-number="0" advice-type="ADDACS" subject-first-aosn="00040784" subject-last-aosn="00040784" user-number="XXXXXX" stream-identifier="0" envelope-sequence-number="00010088" report-generation-date="2018-07-18" user-name="SOME COMPANY" report-type="7001" />
          <AddresseeInformation name=" " />
         <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-07-18" reference="369851ABC" payer-name="MR SMITH" payer-account-number="88888888" payer-sort-code="555555" reason-code="1" aosn="00040787"   actiontaken="Some Action"/>
         <MessagingError />
        </MessagingAdvices>
      </Data>
    </BACSDocument>


    Dont ask me .. i dont know

    Thursday, July 19, 2018 8:32 AM

Answers

  • Something like the following should work.  Given the source XML it will generate XML1, XML2 and XML3:

    Dim xDoc = XDocument.Load("XMLSource.xml")
    Dim advices = xDoc.Root.<Data>.<MessagingAdvices>
    Dim customerGroups = (From element In advices.<MessagingAdvice> Let cust = element.@reference.Substring(0, 6) Select cust, element Group By cust Into Group)
    Dim resultDocs As New List(Of XDocument)
    For Each customer In customerGroups
        Dim result = New XDocument
        result.Add(xDoc.Root)
        result.Root.RemoveAll()
        result.Root.Add(<Data><MessagingAdvices></MessagingAdvices></Data>)
        Dim resultAdvices = result.Root.<Data>.<MessagingAdvices>.First
        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<MessagingHeader>.First)
        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<AddresseeInformation>.First)
        For Each group In customer.Group
            group.element.@actiontaken = "some action"
            resultAdvices.Add(group.element)
        Next
        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<MessagingError>.First)
        resultDocs.Add(result)
    Next
    For i = 0 To resultDocs.Count - 1
        resultDocs(i).Save($"XMLFile{i + 1}.xml")
    Next


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Thursday, July 19, 2018 1:54 PM
    Moderator
  • Hi Reed,

    Thank yo for taking the time to tray and explain it to me, but in short .. no, it doesn't make sense to me only being a novice at this stuff. 

    I could just about work out what was happening in the original code, but with this one I'm total lost


    Dont ask me .. i dont know

    Hi Pete,

    Well, at this point we are starting to dance the line between free advice and paid consulting, but I'll give you one more freebee since you're obviously putting some effort into it on your part.

    The main trick is to follow the flow of the XML document and adjust the code to match.  I'll have to admit that my last post wasn't entirely on track to handle the specifics of this document (I did have to actually write the code to get the flow correct).

    Here's an update version of the code that handles this case in the way you need:

    Dim xDoc = XDocument.Load("ARUDD_XMLFile.xml")
    Dim aruddElement = xDoc.Root.<Data>.<ARUDD>.First
    Dim originatingRecords = aruddElement.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>
    Dim customerGroups = (From element In originatingRecords.<ReturnedDebitItem> Let cust = element.<PayerAccount>.@ref.Substring(0, 6) Select cust, element Group By cust Into Group)
    Dim resultDocs As New List(Of XDocument)
    For Each customer In customerGroups
        Dim result = New XDocument
        result.Add(xDoc.Root)
        result.Root.RemoveAll()
        result.Root.Add(<Data><ARUDD></ARUDD></Data>)
        Dim newARUDD = result.Root.<Data>.<ARUDD>.First
        newARUDD.Add(aruddElement.<Header>.First)
        newARUDD.Add(aruddElement.<AddresseeInformation>.First)
        newARUDD.Add(aruddElement.<ServiceLicenseInformation>.First)
        newARUDD.Add(<Advice><OriginatingAccountRecords><OriginatingAccountRecord></OriginatingAccountRecord></OriginatingAccountRecords></Advice>)
        Dim newOAR = newARUDD.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>.First
        newOAR.Add(originatingRecords.First.<OriginatingAccount>)
        For Each group In customer.Group
            newOAR.Add(group.element)
        Next
        newOAR.Add(originatingRecords.First.<Totals>)
        resultDocs.Add(result)
    Next
    For i = 0 To resultDocs.Count - 1
        resultDocs(i).Save($"ARUDD_XMLFile{i + 1}.xml")
    Next
    


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Pete Newman Monday, July 30, 2018 4:37 PM
    Monday, July 30, 2018 3:31 PM
    Moderator

All replies

  • Something like the following should work.  Given the source XML it will generate XML1, XML2 and XML3:

    Dim xDoc = XDocument.Load("XMLSource.xml")
    Dim advices = xDoc.Root.<Data>.<MessagingAdvices>
    Dim customerGroups = (From element In advices.<MessagingAdvice> Let cust = element.@reference.Substring(0, 6) Select cust, element Group By cust Into Group)
    Dim resultDocs As New List(Of XDocument)
    For Each customer In customerGroups
        Dim result = New XDocument
        result.Add(xDoc.Root)
        result.Root.RemoveAll()
        result.Root.Add(<Data><MessagingAdvices></MessagingAdvices></Data>)
        Dim resultAdvices = result.Root.<Data>.<MessagingAdvices>.First
        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<MessagingHeader>.First)
        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<AddresseeInformation>.First)
        For Each group In customer.Group
            group.element.@actiontaken = "some action"
            resultAdvices.Add(group.element)
        Next
        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<MessagingError>.First)
        resultDocs.Add(result)
    Next
    For i = 0 To resultDocs.Count - 1
        resultDocs(i).Save($"XMLFile{i + 1}.xml")
    Next


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Thursday, July 19, 2018 1:54 PM
    Moderator
  • Hi,

    Thank you for your help. I keep getting an error ' Error Illegal characters in path

    xml file Im using

    <?xml version="1.0" encoding="utf-16"?>
    <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
      <Data>
        <MessagingAdvices>
          <MessagingHeader document-number="0" advice-type="ADDACS" subject-first-aosn="00040709" subject-last-aosn="00040710" user-number="123456" stream-identifier="0" envelope-sequence-number="00010055" report-generation-date="2018-06-29" user-name="CASHBACS INTERNATIONAL LIMITED" report-type="7001" />
          <AddresseeInformation name=" " />
          <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-06-29" reference="999999123" payer-name="MR SMITH" payer-account-number="11111111" payer-sort-code="333333" reason-code="1" aosn="00040709" />
          <MessagingAdvice user-number="123456" record-type="A" effective-date="2018-06-29" reference="777777ABC" payer-name="R JONES" payer-account-number="22222222" payer-sort-code="444444" reason-code="1" aosn="00040710" />
          <MessagingError />
        </MessagingAdvices>
      </Data>
    </BACSDocument>

    my  ( your code )

    Try
                                    Dim xDoc = XDocument.Load(_mod_xml)
                                    Dim advices = xDoc.Root.<Data>.<MessagingAdvices>
                                    Dim customerGroups = (From element In advices.<MessagingAdvice> Let cust = element.@reference.Substring(0, 6) Select cust, element Group By cust Into Group)
                                    Dim resultDocs As New List(Of XDocument)
                                    For Each customer In customerGroups
                                        Dim result = New XDocument
                                        result.Add(xDoc.Root)
                                        result.Root.RemoveAll()
                                        result.Root.Add(<Data><MessagingAdvices></MessagingAdvices></Data>)
                                        Dim resultAdvices = result.Root.<Data>.<MessagingAdvices>.First
                                        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<MessagingHeader>.First)
                                        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<AddresseeInformation>.First)
                                        For Each group In customer.Group
                                            group.element.@actiontaken = "some action"
                                            resultAdvices.Add(group.element)
                                        Next
                                        resultAdvices.Add(xDoc.Root.<Data>.<MessagingAdvices>.<MessagingError>.First)
                                        resultDocs.Add(result)
                                    Next
                                    For i = 0 To resultDocs.Count - 1
                                        resultDocs(i).Save($"XMLFile{i + 2}.xml")
                                    Next
                                Catch err As Exception
                                    Using sw As StreamWriter = File.AppendText(Globals.clsGlobals.LogFile & _errorReportName)
                                        sw.WriteLine("Error " & err.Message.ToString)
                                    End Using
                                End Try

    any suggestions on what I'm doing wrong


    Dont ask me .. i dont know

    Monday, July 23, 2018 7:37 PM
  • Where do you get the error?  If it is in the load show us the path in _mod_xml.

    Also, what does a file with multiple customers look like?


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.


    • Edited by dbasnett Tuesday, July 24, 2018 1:41 PM
    Tuesday, July 24, 2018 1:40 PM
  • Sorry, should have come back and said , I have sorted it

    thank you


    Dont ask me .. i dont know

    Tuesday, July 24, 2018 1:59 PM
  • Hi Reed,

    Thank you for all you help on the previous query, it has helped me immensely, and saved writing loads of long winded code.  I have adapted it to other XML files of the same structure successfully. I have tried to adapt it to another complex XML but after 2 days on it I have given up. Could you please guide me on how to adapt your previous code to the following XML file. I know in the totals for each of the 'created' xml files are incorrect, but that's fine as I have extra processing / editing to do once the original XML is separated for each customer.  In this file the each customer is identified by the left 6 of @ref either in the Returned debit item tag or the Payer Account tag

    <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
      <Data>
        <ARUDD>
          <Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2018-07-04" />
          <AddresseeInformation name="COMPANY NAME" />
          <ServiceLicenseInformation userName="COMPANY NO 1" userNumber="000000" />
          <Advice>
            <OriginatingAccountRecords>
              <OriginatingAccountRecord>
                <OriginatingAccount name="CUST ACC" number="00000000" sortCode="00-00-00" type="0" bankName="BANK" branchName="ANYTOWN" />
                <ReturnedDebitItem ref="111111ABC" transCode="01" returnCode="0183" returnDescription="REFER TO PAYER" originalProcessingDate="2018-07-02" valueOf="1.00" currency="GBP">
                  <PayerAccount number="00000001" ref="111111ABC" name="CUSTOMER 1" sortCode="00-00-00" bankName="BANK" branchName="BRANCHNAME" />
                </ReturnedDebitItem>
                <ReturnedDebitItem ref="222222FRED" transCode="01" returnCode="0183" returnDescription="REFER TO PAYER" originalProcessingDate="2018-08-02" valueOf="1.00" currency="GBP">
                  <PayerAccount number="00000009" ref="222222FRED" name="JOHN SMITH" sortCode="00-00-00" bankName="BANK" branchName="BRANCHNAME" />
                </ReturnedDebitItem>
                <ReturnedDebitItem ref="111111DEF" transCode="17" returnCode="0183" returnDescription="REFER TO PAYER" originalProcessingDate="2018-07-03" valueOf="1.00" currency="GBP">
                  <PayerAccount number="00000002" ref="111111DEF" name="CUSTOMER 197" sortCode="00-00-00" bankName="BANK" branchName="BRANCHNAME" />
                </ReturnedDebitItem>
                <Totals numberOf="3" valueOf="3.00" currency="GBP" />
              </OriginatingAccountRecord>
            </OriginatingAccountRecords>
          </Advice>
        </ARUDD>
      </Data>
    </BACSDocument>
    
    File 1
    
    <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
      <Data>
        <ARUDD>
          <Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2018-07-04" />
          <AddresseeInformation name="COMPANY NAME" />
          <ServiceLicenseInformation userName="COMPANY NO 1" userNumber="000000" />
          <Advice>
            <OriginatingAccountRecords>
              <OriginatingAccountRecord>
                <OriginatingAccount name="CUST ACC" number="00000000" sortCode="00-00-00" type="0" bankName="BANK" branchName="ANYTOWN" />
                <ReturnedDebitItem ref="111111ABC" transCode="01" returnCode="0183" returnDescription="REFER TO PAYER" originalProcessingDate="2018-07-02" valueOf="1.00" currency="GBP">
                  <PayerAccount number="00000001" ref="111111ABC" name="CUSTOMER 1" sortCode="00-00-00" bankName="BANK" branchName="BRANCHNAME" />
                </ReturnedDebitItem>
                <ReturnedDebitItem ref="111111DEF" transCode="17" returnCode="0183" returnDescription="REFER TO PAYER" originalProcessingDate="2018-07-03" valueOf="1.00" currency="GBP">
                  <PayerAccount number="00000002" ref="111111DEF" name="CUSTOMER 197" sortCode="00-00-00" bankName="BANK" branchName="BRANCHNAME" />
                </ReturnedDebitItem>
                <Totals numberOf="3" valueOf="3.00" currency="GBP" />
              </OriginatingAccountRecord>
            </OriginatingAccountRecords>
          </Advice>
        </ARUDD>
      </Data>
    </BACSDocument>
    
    FILE 2
    
    <BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
      <Data>
        <ARUDD>
          <Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2018-07-04" />
          <AddresseeInformation name="COMPANY NAME" />
          <ServiceLicenseInformation userName="COMPANY NO 1" userNumber="000000" />
          <Advice>
            <OriginatingAccountRecords>
              <OriginatingAccountRecord>
                <OriginatingAccount name="CUST ACC" number="00000000" sortCode="00-00-00" type="0" bankName="BANK" branchName="ANYTOWN" />
                <ReturnedDebitItem ref="222222FRED" transCode="01" returnCode="0183" returnDescription="REFER TO PAYER" originalProcessingDate="2018-08-02" valueOf="1.00" currency="GBP">
                  <PayerAccount number="00000009" ref="222222FRED" name="JOHN SMITH" sortCode="00-00-00" bankName="BANK" branchName="BRANCHNAME" />
                </ReturnedDebitItem>
                <Totals numberOf="3" valueOf="3.00" currency="GBP" />
              </OriginatingAccountRecord>
            </OriginatingAccountRecords>
          </Advice>
        </ARUDD>
      </Data>
    </BACSDocument>


    Dont ask me .. i dont know

    Thursday, July 26, 2018 7:50 AM
  • It should be pretty much the same thing except that you need to drill down into the <Data>.<ARUDD>.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord> element to get the "customerGroups" of <ReturnedDebitItem> elements by "ref" value.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, July 26, 2018 6:23 PM
    Moderator
  • HI Reed,

    I'm still struggling here to get my head around this. I've had several attempts to get the correct xml output, but the best I have come up with is the following ( only putting one of the created xml output files for example ). As you will notice, its not formed correctly

    <BACSDocument>
      <Data>.<ARUDD>.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>.<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2018-07-04"></Header><AddresseeInformation name="COMPANY NAME"></AddresseeInformation><ServiceLicenseInformation userName="COMPANY NO 1" userNumber="000000"></ServiceLicenseInformation><ReturnedDebitItem ref="111111ABC" transCode="01" returnCode="0183" returnDescription="REFER TO PAYER" originalProcessingDate="2018-07-02" valueOf="1.00" currency="GBP" actiontaken="some action"><PayerAccount number="00000001" ref="111111ABC" name="CUSTOMER 1" sortCode="00-00-00" bankName="BANK" branchName="BRANCHNAME"></PayerAccount></ReturnedDebitItem><ReturnedDebitItem ref="111111DEF" transCode="17" returnCode="0183" returnDescription="REFER TO PAYER" originalProcessingDate="2018-07-02" valueOf="1.00" currency="GBP" actiontaken="some action"><PayerAccount number="00000002" ref="111111DEF" name="CUSTOMER 197" sortCode="00-00-00" bankName="BANK" branchName="BRANCHNAME"></PayerAccount></ReturnedDebitItem><Totals numberOf="3" valueOf="3.00" currency="GBP"></Totals></OriginatingAccountRecord>.</OriginatingAccountRecords>.</Advice>.</ARUDD>.</Data>
    </BACSDocument>

    My adaption of the code

     Dim _xml As StringReader = New StringReader(_mod_xml)
                                    Dim xDoc = XDocument.Load(_xml)
                                    Dim advices = xDoc.Root.<Data>.<ARUDD>
                                    Dim customerGroups = (From element In advices.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>.<ReturnedDebitItem> Let cust = element.@ref.Substring(0, 6) Select cust, element Group By cust Into Group)
                                    Dim resultDocs As New List(Of XDocument)
                                    For Each customer In customerGroups
                                        Dim result = New XDocument
                                        result.Add(xDoc.Root)
                                        result.Root.RemoveAll()
                                        result.Root.Add(<Data>.<ARUDD>.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>.</OriginatingAccountRecord>.</OriginatingAccountRecords>.</Advice>.</ARUDD>.</Data>)
                                        Dim resultAdvices = result.Root.<Data>.<ARUDD>.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>.First
                                        resultAdvices.Add(xDoc.Root.<Data>.<ARUDD>.<Header>.First)
                                        resultAdvices.Add(xDoc.Root.<Data>.<ARUDD>.<AddresseeInformation>.First)
                                        resultAdvices.Add(xDoc.Root.<Data>.<ARUDD>.<ServiceLicenseInformation>.First)
                                        For Each group In customer.Group
                                            group.element.@actiontaken = "some action"
                                            resultAdvices.Add(group.element)
                                        Next
                                        resultAdvices.Add(xDoc.Root.<Data>.<ARUDD>.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>.<Totals>.First)
                                        resultDocs.Add(result)
                                    Next
                                    For i = 0 To resultDocs.Count - 1
    
                                        Using sw As StreamWriter = File.AppendText(Globals.clsGlobals.LogFile & _errorReportName)
                                            sw.WriteLine("resultDocs Test " & i.ToString & "  " & resultDocs(i).ToString)
                                        End Using
                                    Next


    Dont ask me .. i dont know

    Friday, July 27, 2018 8:20 AM
  • Hi Pete,

    The structure of this file is a little different so you'll want to add a few more steps to the process.

    After doing result.Root.RemoveAll, you only want to add back <Data>.<ARUDD> initially.  Save that into an XElement.  Then add the rest of <Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord> and save that XElement.

    Now in your loop you have two elements to add to.  The first one gets the common stuff (Header, AddresseeInformation, etc.) and the second one gets the specific item from the customer group.

    You have to look at the original document and adjust the code so that you are holding a reference variable to each XElement section that you need to recreate, so that you can get the right elements in the right section of the document.

    Does that make sense?


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, July 27, 2018 2:03 PM
    Moderator
  • Hi Reed,

    Thank yo for taking the time to tray and explain it to me, but in short .. no, it doesn't make sense to me only being a novice at this stuff. 

    I could just about work out what was happening in the original code, but with this one I'm total lost


    Dont ask me .. i dont know

    Monday, July 30, 2018 1:00 PM
  • Hi Reed,

    Thank yo for taking the time to tray and explain it to me, but in short .. no, it doesn't make sense to me only being a novice at this stuff. 

    I could just about work out what was happening in the original code, but with this one I'm total lost


    Dont ask me .. i dont know

    Hi Pete,

    Well, at this point we are starting to dance the line between free advice and paid consulting, but I'll give you one more freebee since you're obviously putting some effort into it on your part.

    The main trick is to follow the flow of the XML document and adjust the code to match.  I'll have to admit that my last post wasn't entirely on track to handle the specifics of this document (I did have to actually write the code to get the flow correct).

    Here's an update version of the code that handles this case in the way you need:

    Dim xDoc = XDocument.Load("ARUDD_XMLFile.xml")
    Dim aruddElement = xDoc.Root.<Data>.<ARUDD>.First
    Dim originatingRecords = aruddElement.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>
    Dim customerGroups = (From element In originatingRecords.<ReturnedDebitItem> Let cust = element.<PayerAccount>.@ref.Substring(0, 6) Select cust, element Group By cust Into Group)
    Dim resultDocs As New List(Of XDocument)
    For Each customer In customerGroups
        Dim result = New XDocument
        result.Add(xDoc.Root)
        result.Root.RemoveAll()
        result.Root.Add(<Data><ARUDD></ARUDD></Data>)
        Dim newARUDD = result.Root.<Data>.<ARUDD>.First
        newARUDD.Add(aruddElement.<Header>.First)
        newARUDD.Add(aruddElement.<AddresseeInformation>.First)
        newARUDD.Add(aruddElement.<ServiceLicenseInformation>.First)
        newARUDD.Add(<Advice><OriginatingAccountRecords><OriginatingAccountRecord></OriginatingAccountRecord></OriginatingAccountRecords></Advice>)
        Dim newOAR = newARUDD.<Advice>.<OriginatingAccountRecords>.<OriginatingAccountRecord>.First
        newOAR.Add(originatingRecords.First.<OriginatingAccount>)
        For Each group In customer.Group
            newOAR.Add(group.element)
        Next
        newOAR.Add(originatingRecords.First.<Totals>)
        resultDocs.Add(result)
    Next
    For i = 0 To resultDocs.Count - 1
        resultDocs(i).Save($"ARUDD_XMLFile{i + 1}.xml")
    Next
    


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Pete Newman Monday, July 30, 2018 4:37 PM
    Monday, July 30, 2018 3:31 PM
    Moderator
  • Hi Reed,

    Big thank you. With the code in front of me , I think I understand what each part is doing, so a thumbs up for teaching this old dog a new trick. Apricate the 'free' time you have given on this, and if there's a next time, I'll be happy to pay  


    Dont ask me .. i dont know

    Monday, July 30, 2018 4:37 PM
  • Hi Reed,

    Big thank you. With the code in front of me , I think I understand what each part is doing, so a thumbs up for teaching this old dog a new trick. Apricate the 'free' time you have given on this, and if there's a next time, I'll be happy to pay  


    Dont ask me .. i dont know

    You're welcome Pete.  I need to clarify because I think I gave the wrong impression.  We are here to help and not allowed to sell services, but sometimes a user either A) doesn't want to do any work themselves (not your case), or B) hits a roadblock beyond their ability, and then there's really no choice but to hire a consultant.

    As long as you are trying and posting back with what you tried and where you're stuck, we'll keep trying to help.  But if you hit a point where you know you just need someone to do it for you, then go the consulting route.

    I'm glad you found this helpful and maybe with the two differing examples you'll have enough to continue making modifications for other use cases.  Please don't hesitate to ask a follow up question if it comes to that.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Monday, July 30, 2018 5:27 PM
    Moderator