Convert invoice XML data to flat CSV format via XSLT

As a novice XSLT, I'm struggling to read docs, and interpret results to resolve this query, hence the question. Given the following XML data, I want to output each invoice on a separate line, but with the contents of all the address info included. All of the <PHONE_NUMBER> relative to the account are also to be repeated on each invoice record. Could someone help in providing an XSLT solution.

<FILE>
  <LETTER>
    <RUN_DATE>01_04_2004</RUN_DATE>
    <ADDRESS >
      <FLAT_NO>Flat 1A</FLAT_NO>
      <STREET_NO> </STREET_NO>
      <STREET_NAME>Skipworth Road</STREET_NAME>
      <AREA>Coventry</AREA>
      <POST_CODE>CV6 8HT</POST_CODE>
    </ADDRESS>
    <INVOICE_ADDRESS>
      <FLAT_NO>Flat 1A</FLAT_NO>
      <STREET_NO></STREET_NO>
      <STREET_NAME>Skipworth Road</STREET_NAME>
      <AREA>Coventry</AREA>
      <POSTCODE>CV6 8HT</POSTCODE>
    </INVOICE_ADDRESS>
    <ACCOUNT>
      <ACCOUNT_NO>C11111</ACCOUNT_NO>
      <TITLE>MR</TITLE>
      <NAME1>John</NAME1>
      <NAME2>Dempsey</NAME2>
     </ACCOUNT>
    <PHONE_NUMBER>02476222222</PHONE_NUMBER>
    <PHONE_NUMBER>07801651711</PHONE_NUMBER>
    <ARREARS>
      <LAST_PAYMENT_DATE></LAST_PAYMENT_DATE>
      <LAST_PAYMENT_AMOUNT>   0.00</LAST_PAYMENT_AMOUNT>
      <ACCOUNT_BALANCE>      171.74</ACCOUNT_BALANCE>
    </ARREARS>
    <INVOICES>
      <INVOICE>
        <INVOICE_NUMBER>P0001</INVOICE_NUMBER>
        <INVOICE_DATE>01_04_2004</INVOICE_DATE>
        <INVOICE_AMOUNT>20.34</INVOICE_AMOUNT>
      </INVOICE>
    </INVOICES>
    <INVOICES>
      <INVOICE>
        <INVOICE_NUMBER>P0002</INVOICE_NUMBER>
        <INVOICE_DATE>2004_01_15</INVOICE_DATE>
        <INVOICE_AMOUNT>5.18</INVOICE_AMOUNT>
      </INVOICE>
    </INVOICES>
  </LETTER>
</FILE>

The output would look something like:

01_04_2004~Flat 1A~ ~Skipworth Road~Coventry~CV6 8HT~Flat 1A~~Skipworth Road~Coventry~CV6 8HT~C11111~MR~John~Dempsey~02476222222,07801651711~~0.00~ 171.74~P0001~01_04_2004~20.34
01_04_2004~Flat 1A~ ~Skipworth Road~Coventry~CV6 8HT~Flat 1A~~Skipworth Road~Coventry~CV6 8HT~C11111~MR~John~Dempsey~02476222222,07801651711~~0.00~ 171.74~P0002~2004_01_15~5.18
jackdempseyAsked:
Who is Participating?
 
dfiala13Connect With a Mentor Commented:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" />

<xsl:template match="/">
      <xsl:for-each select="FILE/LETTER">
            <xsl:variable name="run"><xsl:value-of select="RUN_DATE" /></xsl:variable>
            <xsl:variable name="addr">
                  <xsl:for-each select="ADDRESS">
                        <xsl:call-template name="Address" />
                  </xsl:for-each>
            </xsl:variable>
            <xsl:variable name="invaddr">
                  <xsl:for-each select="INVOICE_ADDRESS">
                        <xsl:call-template name="Address" />
                  </xsl:for-each>
            </xsl:variable>
            <xsl:variable name="acct">
                  <xsl:value-of select="concat(ACCOUNT/ACCOUNT_NO, '~',  ACCOUNT/TITLE,  '~',  ACCOUNT/NAME1,'~',  ACCOUNT/NAME2)" />
            </xsl:variable>
            <xsl:variable name="phone">
            <xsl:for-each  select="PHONE_NUMBER">
            <xsl:value-of  select="."/><xsl:if test="position() &lt; last()">,</xsl:if>
         </xsl:for-each>
            </xsl:variable>
            <xsl:for-each select="INVOICES/INVOICE">
                  <xsl:value-of select="concat($run,'~',$addr,'~',$invaddr,'~',$acct, '-', $phone)" />
                  <xsl:value-of select="concat('~',INVOICE_NUMBER,'~',INVOICE_DATE,'~',INVOICE_AMOUNT)" />
                  <xsl:text>&#xD;&#xA;</xsl:text>
            </xsl:for-each>
      </xsl:for-each>
      

</xsl:template>

<xsl:template name="Address">
      <xsl:value-of select="concat(FLAT_NO, '~',  STREET_NO,  '~',  STREET_NAME,'~',  AREA,'~',  POST_CODE)" />
</xsl:template>

</xsl:stylesheet>
0
 
jeffqyztConnect With a Mentor Commented:
The point to remember is that if you want to refer to the parent inside your for-each, you need to use the full XPath back to it.  The following stylesheet does what you want, I think.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:template match="/">
      <xsl:for-each select="/FILE/LETTER/INVOICES/INVOICE">
         <xsl:value-of select="/FILE/LETTER/RUN_DATE"/>~
         <xsl:value-of select="/FILE/LETTER/ADDRESS/FLAT_NO"/>~
         <xsl:value-of select="/FILE/LETTER/ADDRESS/STREET_NO"/>~
         <xsl:value-of select="/FILE/LETTER/ADDRESS/STREET_NAME"/>~
         <xsl:value-of select="/FILE/LETTER/ADDRESS/AREA"/>~
         <xsl:value-of select="/FILE/LETTER/ADDRESS/POST_CODE"/>~
         <xsl:value-of select="/FILE/LETTER/INVOICE_ADDRESS/FLAT_NO"/>~
         <xsl:value-of select="/FILE/LETTER/INVOICE_ADDRESS/STREET_NO"/>~
         <xsl:value-of select="/FILE/LETTER/INVOICE_ADDRESS/STREET_NAME"/>~
         <xsl:value-of select="/FILE/LETTER/INVOICE_ADDRESS/AREA"/>~
         <xsl:value-of select="/FILE/LETTER/INVOICE_ADDRESS/POST_CODE"/>~
         <xsl:value-of select="/FILE/LETTER/ACCOUNT/ACCOUNT_NO"/>~
         <xsl:value-of select="/FILE/LETTER/ACCOUNT/TITLE"/>~
         <xsl:value-of select="/FILE/LETTER/ACCOUNT/NAME1"/>~
         <xsl:value-of select="/FILE/LETTER/ACCOUNT/NAME2"/>~
         <xsl:for-each  select="/FILE/LETTER/PHONE_NUMBER">
            <xsl:value-of  select="."/>,
         </xsl:for-each>
         <xsl:value-of select="/FILE/LETTER/ARREARS/LAST_PAYMENT_DATE"/>~
         <xsl:value-of select="/FILE/LETTER/ARREARS/LAST_PAYMENT_AMOUNT"/>~
         <xsl:value-of select="/FILE/LETTER/ARREARS/ACCOUNT_BALANCE"/>~
         <xsl:value-of select="INVOICE_NUMBER"/>~
         <xsl:value-of select="INVOICE_DATE"/>~
         <xsl:value-of select="INVOICE_AMOUNT"/>
      </xsl:for-each>
   </xsl:template>
</xsl:stylesheet>
0
 
jeffqyztCommented:
Heh,

dfiala13's answer is much more portable than mine (and it correctly puts each output on a single line, what I get for using a web browser to view :), but you can see two different approaches to reaching the parent from the child.  There is the direct access method (my example) and then there is the "store it in a variable for later use" method (dfiala13's).  I think dfiala has the better solution here, but there are times when each approach would be useful.  



0
 
jackdempseyAuthor Commented:
jeffqyzt, you helped me to understand the structure a bit clearer now, and as you say dfiala13 provided me with the solution I required when I applied it to my scenario, even to the extent of introducing me to variables. (always learning)

thanks to both of you. And as pointed out, my environment requires the portability also, hence the points . cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.