1 2 Previous Next 18 Replies Latest reply on Oct 3, 2012 1:55 PM by comment

    xml export format help required

    rrighini

      Hi all,

       

      I have a limited knowledge of xml structures - self taught. I have done xml exports before in this format

       

      <?xml version='1.0' encoding='UTF-8' ?>
      <xsl:stylesheet version='1.0'
      xmlns:xsl='http://www.w3.org/1999/XSL/Transform'
      xmlns:fm="http://www.filemaker.com/fmpdsoresult"
      exclude-result-prefixes="fm">
      <xsl:output version='1.0' encoding='UTF-8' indent='yes' method='xml' />
      <xsl:template match="/">

       

      <PaymentList>
      <Payment>
      <xsl:for-each select="fm:FMPDSORESULT/fm:ROW[1]">

      <PaymentTypeField><xsl:value-of select="./fm:DebitPaymentType" /></PaymentTypeField>
      <DebitSortCodeField><xsl:value-of select="./fm:DebitSortCode" /></DebitSortCodeField>
      <DebitAccountNumberField><xsl:value-of select="./fm:DebitAccountNo" /></DebitAccountNumberField>
      <PaymentCurrencyField><xsl:value-of select="./fm:DebitPaymentCurrency" /></PaymentCurrencyField>
      <PaymentAmountField><xsl:value-of select="./fm:InvoicePaymentsGrossSummary" /></PaymentAmountField>
      <PaymentReferenceNumberField><xsl:value-of select="./fm:DebitPaymentReferenceNumber" /></PaymentReferenceNumberField>
      <ValueDateField><xsl:value-of select="./fm:DebitPaymentDate" /></ValueDateField>
      <DebitAccountNameField><xsl:value-of select="./fm:DebitAccountName" /></DebitAccountNameField>
      </xsl:for-each>

      <BeneficiaryList>
      <xsl:for-each select="fm:FMPDSORESULT/fm:ROW">
      <BeneficiaryItem>
      <AccountNumber><xsl:value-of select="./fm:SupplierBankAccountNumber" /></AccountNumber>
      <SortCode><xsl:value-of select="./fm:SupplierBankSortCode" /></SortCode>
      <Amount><xsl:value-of select="./fm:InvoicePaymentsGrossTotal" /></Amount>
      <AccountTypeIndicator><xsl:value-of select="./fm:SupplierAccountTypeIndicator" /></AccountTypeIndicator>
      <Name><xsl:value-of select="./fm:SupplierName" /></Name>
      <BankName><xsl:value-of select="./fm:SupplierBankName" /></BankName>
      <PaymentReferenceNumber><xsl:value-of select="./fm:InvoiceNo" /></PaymentReferenceNumber>
      </BeneficiaryItem>
      </xsl:for-each>
      </BeneficiaryList>
      </Payment>
      </PaymentList>
      </xsl:template>
      </xsl:stylesheet>

       

       

       

      However, I need to be able to get my payments data out of FM for upload to an online bank account and they require this format.

       

       

      Below is an example of a BACS GBP Multiple in XML format.

       

      <PaymentList><Payment>

       

      <PaymentTypeField> BACS_ Payment</PaymentTypeField> <DebitSortCodeField>300000</DebitSortCodeField> <DebitAccountNumberField>00001000</DebitAccountNumberField> <PaymentCurrencyField>GBP</PaymentCurrencyField> <PaymentAmountField>30000.00</PaymentAmountField> <PaymentReferenceNumberField>REFERENCE</PaymentReferenceNumberField> <ValueDateField>10-Nov-2011</ValueDateField> <DebitAccountNameField>GBP ACCOUNT</DebitAccountNameField> 7

       

      <BeneficiaryList>

       

       

       

      <BeneficiaryItem AccountNumber="12345678" SortCode="300001" Amount="10000.00" AccountTypeIndicator="" Name="BENEFICIARY NAME" BankName="LTSB" PaymentReferenceNumber="BEN REF" />

       

      <BeneficiaryItem AccountNumber="

      12345678" SortCode="300001" Amount="10000.00" AccountTypeIndicator="" Name="BENEFICIARY NAME" BankName="LTSB" PaymentReferenceNumber="BEN REF" />

       

       

      <BeneficiaryItem AccountNumber="

      12345678" SortCode="300001" Amount="10000.00" AccountTypeIndicator="" Name="BENEFICIARY NAME" BankName="LTSB" PaymentReferenceNumber="BEN REF" />

       

       

      </BeneficiaryList>

      </Payment> </PaymentList>

       

       

       

      I've played around with my formatting but can't work out how to achieve the above.

       

      Any help appreciated - our bills are stacking up!!!!

       

      Thanks in advance

       

       

       

       

        • 1. Re: xml export format help required
          beverly

          Played around with formatting of the FIELD on the LAYOUT? And if so, did you check the box to export as formatted?

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: xml export format help required
            rrighini

            Sorry I wasn't very clear.  I've played around with the xml sstructure, particularly the Beneficiary list part which is the bit I can't figure out how to write in xml.

            • 3. Re: xml export format help required
              comment

              A sample of the data being exported would be helpful, I think. Why don't you export a record or two as XML (preferably in FMPXMLRESULT grammar) without applying a stylesheet, then post the result here?

               

               

              Note: it's best to zip XML and XSLT files and attach them, to prevent forum software mangling the code.

              • 4. Re: xml export format help required
                beverly

                OK, I thought you meant the format of the date field upon export. Michael's right. We know nothing of your database structure.

                1. Do you have multiple fields, repeating field or related fields for the Beneficiary List?
                2. Are you using XSLT or are you trying to calculate the text in someway for export?

                 

                I see this:

                <Payment>

                     <xsl:for-each select="fm:FMPDSORESULT/fm:ROW[1]">

                which makes think you are exporting from a table with the beneficiaries, but the first row is used for the "parent" (Payment) information. Is that correct? If you happen to be exporting more than one "parent" record, then this is where it gets trickier. If you have an XSLT, then the muenchian method for grouping would help. http://www.jenitennison.com/xslt/grouping/muenchian.html

                If you have related children, rather than muenchian, exporting parent and portals is a little easier to understand by "walking the portal rows" with XSLT.

                 

                Beverly

                • 5. Re: xml export format help required
                  rrighini

                  Export is the format that works for me but not the bank.

                   

                  Export 2 is my attempt at exporting in the banks required format.

                   

                  Payments3 (2) is my attempt at the xml structure required by the bank which I am sure is completely wrong!

                   

                  Thanks for the help

                  • 6. Re: xml export format help required
                    comment

                    We are still missing a sample of the original data.

                    • 7. Re: xml export format help required
                      rrighini

                      Sorry - it must be very frustrating for you all having novices like me on.  Here's the fmp xml export with no stylesheet

                      • 8. Re: xml export format help required
                        comment

                        Ah, it's getting clearer now. But before we get to the actual transformation, I'd like to understand how your data maps to the expected output.

                         

                        If I understand correctly, your Invoice is a Payment and your Suppliers are Beneficiaries. However, I don't see that you have exported all the data that appears in the sample file. Perhaps it's not required, but I wanted to make sure. For example, where is Payment's date? Or the amount due to each supplier (which together, I think, would total up to the invoice's gross total)? And what is the source for populating the PaymentReferenceNumber attribute?

                        • 9. Re: xml export format help required
                          rrighini

                          The payment list section at the top is our account details which is a single record extracted that shows payment type (always = BACS_Payment), our debit sort code, our debit account number, the payment currency (always GBP), the total of all invoices being uploaded (InvoicePaymentGrossSummary), the debit payment reference number (which will be the currentdate+a serial number eg 01-01-2012-2), and debit account name (Our bank account name).

                           

                          The Beneficiary List should then be an export of all the invoices waiting to be uploaded to the bank from my database, as individual records showing

                           

                          suppliers account number, (Suppliers::SupplierBankAccountNumber")

                          suppliers bank sort code ( Suppliers::SupplierBankSortCode")

                          Invoice Total ( ="InvoicePaymentsGrossTotal")

                          Supplier Account Type ( Suppliers::SupplierAccountTypeIndicator ) which is eg "GBP"

                          supplier name (="Suppliers::SupplierName") ie. the name on their bank account

                          supplier bank name ("./fm:SupplierBankName" /) ie. name of the suppliers bank

                          payment reference number ( ="InvoiceNo" )

                           

                          Hope this makes sense. I'm fairly ok with what data needs to export - I just can't figure how to design the xml on the beneficiary list so that it appears how they want it and successfully extracts from my database.

                          • 10. Re: xml export format help required
                            comment

                            rrighini wrote:

                             

                            The Beneficiary List should then be an export of all the invoices waiting to be uploaded to the bank

                             

                            Are you sure about this? If I understand you correctly, when exporting 3 invoices, the exported XML will have 1 payment and 3 suppliers. That is assuming each invoice is addressed to a single supplier - is that a safe assumption?

                            • 11. Re: xml export format help required
                              rrighini

                              No sorry.  If I export three invoces with a total value of say £1000 the payment list section "InvoicePaymentsGrossTotal" will say £1000.  The beneficiary list will then have 3 items and the invoice total "InvoicePaymentsGrossTotal" will be different in each record - like

                               

                              Beneficiary 1 - Supplier A -  invoice total £300

                              Beneficiary 2 - Supplier B - invoice total £200

                              Beneficiary 3 - Supplier C - invoice total £500

                               

                              = £1000 as listed in my payments section.

                               

                              Is that clearer.  You're very kind bearing with me like this.

                              • 12. Re: xml export format help required
                                comment

                                I am sorry to cross-examine you so, but this is crucial. You say, "no sorry", but it looks like you have confirmed what I asked. Using your example, the resulting XML would look something like this (after some pruning  for readability), would it not?

                                 

                                 

                                <PaymentList>
                                    <Payment>
                                        <PaymentReferenceNumberField>SOMETHING UNIQUE TO THIS EXPORT</PaymentReferenceNumberField> 
                                        <PaymentAmountField>1000.00</PaymentAmountField> 
                                        <DebitAccountNameField>OUR ACCOUNT</DebitAccountNameField>
                                        <BeneficiaryList>
                                            <BeneficiaryItem Amount="300.00" Name="SUPPLIER A" PaymentReferenceNumber="INVOICE A #" />
                                            <BeneficiaryItem Amount="200.00" Name="SUPPLIER B" PaymentReferenceNumber="INVOICE B #" />
                                            <BeneficiaryItem Amount="500.00" Name="SUPPLIER C" PaymentReferenceNumber="INVOICE C #" />
                                        </BeneficiaryList>
                                    </Payment> 
                                </PaymentList>
                                

                                 

                                Note that this shows 1 Payment in the PaymentList and 3 BeneficiaryItems in the BeneficiaryList.

                                • 13. Re: xml export format help required
                                  beverly

                                  r, I've looked at all your attachments. I'm not sure that we still have the correct export information. However, I made an attempt at revising your XSLT.

                                  1. don't "nest" xsl:for-each if other ways will work. (called templates, are used, but more complex for your needs)

                                       so, I took your outer "loop" out and changed, for example:

                                  <PaymentTypeField><xsl:value-of select="./fm:DebitPaymentType"></xsl:value-of></PaymentTypeField>

                                  to

                                  <PaymentTypeField><xsl:value-of select="fm:FMPDSORESULT/fm:ROW[1]/fm:DebitPaymentType"></xsl:value-of></PaymentTypeField>

                                   

                                  2. you can't put "xsl:value-of select..." inside where you want attribute values.

                                  so, you're doing this:

                                  <BeneficiaryItem> AccountNumber=xsl:value-of select="./fm:SupplierBankAccountNumber" SortCode=xsl:value-of select="./fm:SupplierBankSortCode Amount=xsl:value-of select="./fm:InvoicePaymentsGrossTotal" AccountTypeIndicator=xsl:value-of select="./fm:SupplierAccountTypeIndicator" Name=xsl:value-of select="./fm:SupplierName" BankName=xsl:value-of select="./fm:SupplierBankName" PaymentReferenceNumber=xsl:value-of select="./fm:InvoiceNo" </BeneficiaryItem>
                                  

                                  when it should be:

                                  <BeneficiaryItem AccountNumber="{./fm:SupplierBankAccountNumber}" SortCode="{./fm:SupplierBankSortCode}" Amount="{./fm:InvoicePaymentsGrossTotal}" AccountTypeIndicator="{./fm:SupplierAccountTypeIndicator}" Name="{./fm:SupplierName}" BankName="{./fm:SupplierBankName}" PaymentReferenceNumber="{./fm:InvoiceNo}" />

                                   

                                  the curly braces are, in fact called "Attribute Value Templates". You need the attribute "AccountName" to be a part of the Element "BeneficiaryItem". This is a "shortcut" way to putting the correct values in there.

                                  http://www.w3.org/TR/xslt#attribute-value-templates

                                  http://www.dpawson.co.uk/xsl/sect2/N1575.html

                                   

                                  Another way involves creating the element and adding each attribute and using the 'xsl:value-of'

                                  http://www.w3.org/TR/xslt#section-Creating-Elements-with-xsl:element

                                  http://www.w3.org/TR/xslt#creating-attributes

                                   

                                  I'll post this and then attach the XSLT that I revised.

                                  Beverly

                                  • 14. Re: xml export format help required
                                    rrighini

                                    No problem at all re cross-examination. Your example is exactly what I'm trying to achieve.

                                    1 2 Previous Next