Creating a CSV from XML using XSL

Question

I am trying to extract customer data out of GNUCash XML file. The final goal is to get a comma-separated-value text file showing the customer data found within the XML data.

The command I am using is

xsltproc transform.xsl data.xml

My xml input file (the full file may be downloaded from https://od.lk/d/OV8xMTAwMjI3ODBf/data.xml ):

. . .
    <gnc:GncCustomer version="2.0.0">
      <cust:guid type="guid">a9dbdd1ec6fddad5e068bfa22f5ad93d</cust:guid>
      <cust:name>Customer2</cust:name>
      <cust:id>000102</cust:id>
      <cust:addr version="2.0.0">
        <addr:name>Billing Name</addr:name>
        <addr:addr1>Billing Address  Line 1</addr:addr1>
        <addr:addr2>Billing Address  Line 2</addr:addr2>
        <addr:addr3>Billing Address  Line 3</addr:addr3>
        <addr:addr4>Billing Address  Line 4</addr:addr4>
        <addr:phone>Phone</addr:phone>
        <addr:fax>Fax</addr:fax>
        <addr:email>E-mail</addr:email>
      </cust:addr>
      <cust:shipaddr version="2.0.0">
        <addr:name>Shipping Name</addr:name>
        <addr:addr1>Shipping Address Line 1</addr:addr1>
        <addr:addr2>Shipping Address Line 2</addr:addr2>
        <addr:addr3>Shipping Address Line 3</addr:addr3>
        <addr:addr4>Shipping Address Line 4</addr:addr4>
        <addr:phone>Shipping phone</addr:phone>
        <addr:fax>Shipping fax</addr:fax>
        <addr:email>Shipping e-mail</addr:email>
      </cust:shipaddr>
      <cust:notes>Notes</cust:notes>
      <cust:taxincluded>USEGLOBAL</cust:taxincluded>
      <cust:active>1</cust:active>
      <cust:discount>0/1</cust:discount>
      <cust:credit>0/1</cust:credit>
      <cust:currency>
        <cmdty:space>ISO4217</cmdty:space>
        <cmdty:id>USD</cmdty:id>
      </cust:currency>
      <cust:use-tt>1</cust:use-tt>
    </gnc:GncCustomer>
. . .

The XSL file that I tried (which also may be downloaded from https://od.lk/d/OV8xMTAwMjI5MTBf/transform.xsl ):

<xsl:stylesheet version="1.0" 
    xmlns="http://www.gnucash.org/XML/"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:cd="http://www.gnucash.org/XML/cd"
    xmlns:book="http://www.gnucash.org/XML/book"
    xmlns:gnc="http://www.gnucash.org/XML/gnc"
    xmlns:cmdty="http://www.gnucash.org/XML/cmdty"
    xmlns:trn="http://www.gnucash.org/XML/trn"
    xmlns:split="http://www.gnucash.org/XML/split"
    xmlns:act="http://www.gnucash.org/XML/act"
    xmlns:price="http://www.gnucash.org/XML/price"
    xmlns:ts="http://www.gnucash.org/XML/ts"
    xmlns:slot="http://www.gnucash.org/XML/kvpslot"
    xmlns:cust="http://www.gnucash.org/XML/cust"
    xmlns:entry="http://www.gnucash.org/XML/entry"
    xmlns:lot="http://www.gnucash.org/XML/lot"
    xmlns:invoice="http://www.gnucash.org/XML/invoice"
    xmlns:owner="http://www.gnucash.org/XML/owner"
    xmlns:job="http://www.gnucash.org/XML/job"
    xmlns:billterm="http://www.gnucash.org/XML/billterm"
    xmlns:bt-days="http://www.gnucash.org/XML/bt-days"
    xmlns:sx="http://www.gnucash.org/XML/sx"
    xmlns:fs="http://www.gnucash.org/XML/fs"
    xmlns:addr="http://www.gnucash.org/XML/custaddr">
<!-- The above were taken from https://www.gnucash.org/docs/v2.6/C/gnucash-guide/appendixa_xmlconvert1.html -->

  <xsl:output method="text" encoding="utf-8"/>

  <!-- Use the <xsl:strip-space> instruction to eliminate all white-space-only text nodes from the source XML document. -->
  <xsl:strip-space elements="*"/>

  <xsl:param name="separator">,</xsl:param>

  <xsl:param name="quote">"</xsl:param>

  <xsl:param name="newline">&#10;</xsl:param>

  <xsl:template match="/">
    <xsl:apply-templates/>
  </xsl:template>

  <xsl:template match="gnc-v2|gnc:book|gnc-account-example">
    <xsl:apply-templates/>
  </xsl:template>

  <xsl:template match="gnc:GncCustomer">
    <xsl:apply-templates select="cust:id"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:guid"/>
    <xsl:value-of select="$separator"/>    
    <xsl:apply-templates select="cust:name"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:addr/addr:name"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:addr/addr:addr1"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:addr/addr:addr2"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:addr/addr:addr3"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:addr/addr:addr4"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:addr/addr:phone"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:addr/addr:fax"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:addr/addr:email"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:shipaddr/addr:name"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:shipaddr/addr:addr1"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:shipaddr/addr:addr2"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:shipaddr/addr:addr3"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:shipaddr/addr:addr4"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:shipaddr/addr:phone"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:shipaddr/addr:fax"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:shipaddr/addr:email"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:notes"/>
    <!-- We somehow need to deal with the possibility that the user typed a line feed character in the notes field. -->
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:taxincluded"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:active"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:discount"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:credit"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:currency/cmdty:space"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:currency/cmdty:id"/>
    <xsl:value-of select="$separator"/>
    <xsl:apply-templates select="cust:use-tt"/>
    <xsl:text>&#10;</xsl:text>
  </xsl:template>

  <xsl:template match="cust:id|cust:guid|cust:name|cust:addr/addr:name|cust:addr/addr:addr1|cust:addr/addr:addr2|cust:addr/addr:addr3|cust:addr/addr:addr4|cust:addr/addr:phone|cust:addr/addr:fax|cust:addr/addr:email|cust:shipaddr/addr:name|cust:shipaddr/addr:addr1|cust:shipaddr/addr:addr2|cust:shipaddr/addr:addr3|cust:shipaddr/addr:addr4|cust:shipaddr/addr:phone|cust:shipaddr/addr:fax|cust:shipaddr/addr:email|cust:notes|cust:taxincluded|cust:active|cust:discount|cust:credit|cust:currency/cmdty:space|cust:currency/cmdty:id|cust:use-tt">
    <xsl:value-of select="$quote"/>
    <xsl:value-of select="."/>
    <xsl:value-of select="$quote"/>
  </xsl:template>

  <xsl:template match="*"/>

</xsl:stylesheet>

Results I am getting show I am not descending down to all the child element nodes. In particular, I am not able to display the billing details. It may be that my XPath expressions are wrong?

For example, for Customer2, the consecutive commas show I am missing the billing data:

"000102","a9dbdd1ec6fddad5e068bfa22f5ad93d","Customer2",,,,,,,,,,,,,,,,,"Notes","USEGLOBAL","1","0/1","0/1","ISO4217","USD","1"

Results I would like to get is a comma-separated-value listing of the XML data for all nodes, such as:

"000102","a9dbdd1ec6fddad5e068bfa22f5ad93d","Customer2","Billing Name","Billing Address  Line 1","Billing Address  Line 2","Billing Address  Line 3","Billing Address  Line 4","Phone","Fax","E-mail","Shipping Name","Shipping Address Line 1","Shipping Address Line 2","Shipping Address Line 3","Shipping Address Line 4","Shipping phone","Shipping fax","Shipping e-mail","Notes","USEGLOBAL","1","0/1","0/1","ISO4217","USD","1"

It may help to identify that the XSL works for cust:currency but not cust:addr and cust:shipaddr

Please post a **reproducible** example - see: [mcve] – michael.hor257k May 21 '16 at 05:26 — michael.hor257k, May 21 '16 at 05:26
Mention your root element of XML. – Rudramuni TP May 21 '16 at 05:35 — Rudramuni TP, May 21 '16 at 05:35
Any ideas or suggestions out there from anyone? – VQL May 28 '16 at 14:48 — VQL, May 28 '16 at 14:48

score 0 · Answer 1 · answered Jun 17 '16 at 13:28

Whenever the output is missing I always look at the namespaces first.. In this case your namespace for addr in the the xsl is declared as:

xmlns:addr="http://www.gnucash.org/XML/custaddr"

whereas, the in xml file it is declared as:

xmlns:addr="http://www.gnucash.org/XML/addr"

Set these to the same value and I think all should be well.

By the way, that final template would be a lot neater if you'd written it like so:

<xsl:template match="*[ancestor::gnc:GncCustomer]" >
    <xsl:value-of select="concat('&quot;', . , '&quot;')"/>
</xsl:template>

Creating a CSV from XML using XSL

1 Answers1