5 Replies Latest reply: Aug 22, 2012 1:50 AM by HakanBiroglu RSS

    Please help me parse this XML file. Really stuck!!

    956924
      I am working on an interface that reads an XML message via web service and populates a staging table. If you look at the XML (below) you will see that for every <receipt> you can have any number of <transactions> values. My problem is that I dont know how to parse the file and effectively pick up the correct amount of transactions for every receipt. I have used GetElementsByTagName but that does not give me the transaction pertaining to the correct receipt. I have tried to use GetChildNode but that does not work either. I am new to parsing XML so I may not be on the right track with my approach. Could someone please give me an idea on what I need to do to accomplish this? My staging table needs to have a transaction detail row for every <receipt> as well as all the above parent node values. Really stuck on this one....would very much appreciate the help.

      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:acct="http://acct.maryland.gov">
      <soapenv:Header/>
      <soapenv:Body>
      <acct:PaymentTransactionsMessage>
      <acct:PaymentData>
      <County>ANNE ARUNDEL</County>
      <DistrictId>07</DistrictId>
      <LocationId>02</LocationId>
      <Court>DC</Court>
      <TransmittalDate>2012-07-03</TransmittalDate>
      <ZDate>2011-11-10</ZDate>
      <RevenueSource>DC</RevenueSource>
      <Register>
      <RegisterId>050237</RegisterId>
      <Receipt>
      <ReceiptNumber>308651</ReceiptNumber>
      <ReceiptDate>2012-03-01</ReceiptDate>
      <TransactionDetail>
      <Amount>5</Amount>
      <CaseNumber>53633711</CaseNumber>
      <CashierId>308651</CashierId>
      </TransactionDetail>
      <TransactionDetail>
      <Amount>0</Amount>
      <CaseNumber>53633711</CaseNumber>
      <CashierId>308651</CashierId>
      </TransactionDetail>
      </Receipt>
      <Receipt>
      <ReceiptNumber>308652</ReceiptNumber>
      <ReceiptDate>2012-03-01</ReceiptDate>
      <TransactionDetail>
      <Amount>5</Amount>
      <CaseNumber>54199511</CaseNumber>
      <CashierId>308652</CashierId>
      </TransactionDetail>
      <TransactionDetail>
      <Amount>5</Amount>
      <CaseNumber>54199511</CaseNumber>
      <CashierId>308652</CashierId>
      </TransactionDetail>
      <TransactionDetail>
      <Amount>0</Amount>
      <CaseNumber>54199511</CaseNumber>
      <CashierId>308652</CashierId>
      </TransactionDetail>
      </Receipt>
        • 1. Re: Please help me parse this XML file. Really stuck!!
          HakanBiroglu
          Hi,

          For test purposes I have changed your xml to

          <?xml version="1.0"?>
          <PaymentTransactionsMessage>
          <PaymentData>
          <County>ANNE ARUNDEL</County>
          <DistrictId>07</DistrictId>
          <LocationId>02</LocationId>
          <Court>DC</Court>
          <TransmittalDate>2012-07-03</TransmittalDate>
          <ZDate>2011-11-10</ZDate>
          <RevenueSource>DC</RevenueSource>
          <Register>
          <RegisterId>050237</RegisterId>
          <Receipt>
          <ReceiptNumber>308651</ReceiptNumber>
          <ReceiptDate>2012-03-01</ReceiptDate>
          <TransactionDetail>
          <Amount>5</Amount>
          <CaseNumber>53633711</CaseNumber>
          <CashierId>308651</CashierId>
          </TransactionDetail>
          <TransactionDetail>
          <Amount>0</Amount>
          <CaseNumber>53633711</CaseNumber>
          <CashierId>308651</CashierId>
          </TransactionDetail>
          </Receipt>
          <Receipt>
          <ReceiptNumber>308652</ReceiptNumber>
          <ReceiptDate>2012-03-01</ReceiptDate>
          <TransactionDetail>
          <Amount>5</Amount>
          <CaseNumber>54199511</CaseNumber>
          <CashierId>308652</CashierId>
          </TransactionDetail>
          <TransactionDetail>
          <Amount>5</Amount>
          <CaseNumber>54199511</CaseNumber>
          <CashierId>308652</CashierId>
          </TransactionDetail>
          <TransactionDetail>
          <Amount>0</Amount>
          <CaseNumber>54199511</CaseNumber>
          <CashierId>308652</CashierId>
          </TransactionDetail>
          </Receipt>
          </Register>
          </PaymentData>
          </PaymentTransactionsMessage>

          Created an application engine to read and parse the xml with following code:
          Local XmlDoc &xml;
          Local boolean &ret;
          
          &xml = CreateXmlDoc("");
          &ret = &xml.ParseXmlFromURL("c:\temp\test.xml");
          
          &aPaymentData = &xml.GetElementsByTagName("PaymentData");
          If &aPaymentData.len <> 0 Then
             /* Get County */
             &aCounty = &aPaymentData [1].GetElementsByTagName("County");
             If &aCounty.len <> 0 Then
                &strCounty = &aCounty [1].NodeValue;
                MessageBox(0, "", 0, 0, "County: " | &strCounty);
             End-If;
             
             /* Get Receipt */
             &aReceipt = &aPaymentData [1].GetElementsByTagName("Receipt");
             For &i = 1 To &aReceipt.len
                &strReceiptNumber = "";
                &strReceiptDate = "";
                &aReceiptNumber = &aReceipt [&i].GetElementsByTagName("ReceiptNumber");
                If &aReceiptNumber.len <> 0 Then
                   &strReceiptNumber = &aReceiptNumber [1].NodeValue;
                End-If;
                &aReceiptDate = &aReceipt [&i].GetElementsByTagName("ReceiptDate");
                If &aReceiptDate.len <> 0 Then
                   &strReceiptDate = &aReceiptDate [1].NodeValue;
                End-If;
                MessageBox(0, "", 0, 0, "ReceiptNumber: " | &strReceiptNumber | " / ReceiptDate: " | &strReceiptDate);
                
                /* Get TransactionDetai */
                &aTransactionDetail = &aReceipt [&i].GetElementsByTagName("TransactionDetail");
                For &j = 1 To &aTransactionDetail.len
                   &strAmount = "";
                   &strCaseNumber = "";
                   &aAmount = &aTransactionDetail [&j].GetElementsByTagName("Amount");
                   If &aAmount.len <> 0 Then
                      &strAmount = &aAmount [1].NodeValue;
                   End-If;
                   &aCaseNumber = &aTransactionDetail [&j].GetElementsByTagName("CaseNumber");
                   If &aCaseNumber.len <> 0 Then
                      &strCaseNumber = &aCaseNumber [1].NodeValue;
                   End-If;
                   MessageBox(0, "", 0, 0, "Amount: " | &strAmount | " / CaseNumber: " | &strCaseNumber);
                   
                   
                End-For;
             End-For;
          End-If;
          output:

          County: ANNE ARUNDEL (0,0)
          Message Set Number: 0
          Message Number: 0
          Message Reason: County: ANNE ARUNDEL (0,0) (0,0)

          ReceiptNumber: 308651 / ReceiptDate: 2012-03-01 (0,0)
          Message Set Number: 0
          Message Number: 0
          Message Reason: ReceiptNumber: 308651 / ReceiptDate: 2012-03-01 (0,0) (0,0)

          Amount: 5 / CaseNumber: 53633711 (0,0)
          Message Set Number: 0
          Message Number: 0
          Message Reason: Amount: 5 / CaseNumber: 53633711 (0,0) (0,0)

          Amount: 0 / CaseNumber: 53633711 (0,0)
          Message Set Number: 0
          Message Number: 0
          Message Reason: Amount: 0 / CaseNumber: 53633711 (0,0) (0,0)

          ReceiptNumber: 308652 / ReceiptDate: 2012-03-01 (0,0)
          Message Set Number: 0
          Message Number: 0
          Message Reason: ReceiptNumber: 308652 / ReceiptDate: 2012-03-01 (0,0) (0,0)

          Amount: 5 / CaseNumber: 54199511 (0,0)
          Message Set Number: 0
          Message Number: 0
          Message Reason: Amount: 5 / CaseNumber: 54199511 (0,0) (0,0)

          Amount: 5 / CaseNumber: 54199511 (0,0)
          Message Set Number: 0
          Message Number: 0
          Message Reason: Amount: 5 / CaseNumber: 54199511 (0,0) (0,0)

          Amount: 0 / CaseNumber: 54199511 (0,0)
          Message Set Number: 0
          Message Number: 0
          Message Reason: Amount: 0 / CaseNumber: 54199511 (0,0) (0,0)

          Hope this helps.

          Regards,

          Hakan
          • 2. Re: Please help me parse this XML file. Really stuck!!
            956924
            Thank you very much. I am in the process of trying your code and am getting the below error. How did you declare your variables? Are you using array of xmlnode, regular xmlnode...etc? My code is in app package peoplecode. Tools 8.52.

            Assignment left hand side of type XmlNode is not compatible with right hand side of type Array of XmlNode. (2,47)
            • 3. Re: Please help me parse this XML file. Really stuck!!
              HakanBiroglu
              GetElementsByTagName returns an array of xmlnodes, so declare your variables in your applicaion class as
              Local array of xmlnode
              • 4. Re: Please help me parse this XML file. Really stuck!!
                956924
                You sir are a life saver!! Thank you very much...your code was perfect. This was my first bout with XML and I had a tough go of it there for a bit. I owe you a virtual beer!!