Solved

Convert invoice XML data to flat CSV format via XSLT

Posted on 2004-04-02
4
652 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:jackdempsey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 2

Assisted Solution

by:jeffqyzt
jeffqyzt earned 25 total points
ID: 10742107
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
 
LVL 12

Accepted Solution

by:
dfiala13 earned 100 total points
ID: 10742379
<?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
 
LVL 2

Expert Comment

by:jeffqyzt
ID: 10742641
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
 

Author Comment

by:jackdempsey
ID: 10757850
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

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question