XSLT (for CSV) for the give XML structure

Question

I don't know how to make an XSLT for XML to convert it to CSV. Please code a XSLT for the given structure of XML and try to validate and consider the all original commas and quotes etc.

<DROPSHIPITEMS>
  <CREATED value="Thu Apr 21 23:17:39 BST 2011">
    <PRODUCT ITEM='8101'>
      <MODEL>FY316A</MODEL>
      <EAN>5055071647109</EAN>
      <NAME>Enchanted Twilight Flower Fairy 'Amethyst'</NAME>
      <DESCRIPTION><![CDATA[<p> Twilight Fairy 'Amethyst'</p><p>This, Fairy.</p>]]></DESCRIPTION>
      <DIMENSION><![CDATA[Height 31 - 32cm Width, 16 - 18.5cm Depth 12 - 13.5cm]]></DIMENSION>
      <PRICE>16.63</PRICE>
      <DELIVERY>I</DELIVERY>
      <QUANTITY>224</QUANTITY>
      <MIN_ORDER_QTY>1</MIN_ORDER_QTY>
      <URL>http://www.abc-dropship.co.uk/gifts/product_info.php?products_id=8101</URL>
      <IMAGE_URL>http://www.abc-dropship.co.uk/gifts/images/FY316A_001.jpg</IMAGE_URL>
      <CATEGORIES>9003|100297</CATEGORIES>
      <OPTIONS><![CDATA[B - Hand on Dress|A - Flower in Hand|Any]]></OPTIONS>
    </PRODUCT>
</CREATED>
</DROPSHIPITEMS>

Thanks

CSV is just a format: which XML element should go into your CSV? Have your tied something? — MarcoS, May 04 '11 at 07:08
I already answered a similar question to this: http://stackoverflow.com/questions/3056579/convert-xml-document-to-comma-delimited-csv-file-using-xslt-stylesheet/3060630#3060630 Have a look at that link, it should give you enough help to be able to do this yourself. — Flynn1179, May 04 '11 at 08:47

score 2 · Answer 1 · answered May 04 '11 at 14:02

Try this:

<?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="*/*/*[1]/*">
      <xsl:value-of select="name()" />
      <xsl:if test="not(position() = last())">,</xsl:if>
    </xsl:for-each>
    <xsl:text>&#10;</xsl:text>
    <xsl:apply-templates select="*/*/*" mode="row"/>
  </xsl:template>

  <xsl:template match="*" mode="row">
    <xsl:apply-templates select="*" mode="data" />
    <xsl:text>&#10;</xsl:text>
  </xsl:template>

  <xsl:template match="*" mode="data">
    <xsl:choose>
      <xsl:when test="contains(text(),',')">
        <xsl:text>&quot;</xsl:text>
        <xsl:call-template name="doublequotes">
          <xsl:with-param name="text" select="text()" />
        </xsl:call-template>
        <xsl:text>&quot;</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="." />
      </xsl:otherwise>
    </xsl:choose>
    <xsl:if test="position() != last()">,</xsl:if>
  </xsl:template>

  <xsl:template name="doublequotes">
    <xsl:param name="text" />
    <xsl:choose>
      <xsl:when test="contains($text,'&quot;')">
        <xsl:value-of select="concat(substring-before($text,'&quot;'),'&quot;&quot;')" />
        <xsl:call-template name="doublequotes">
          <xsl:with-param name="text" select="substring-after($text,'&quot;')" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$text" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

It correctly handles fields with a comma, and fields with double quotes as well.

EDIT: Almost forgot; this template adds a header line with field names in. If you don't need that, the first template just needs to be

  <xsl:template match="/">
    <xsl:apply-templates select="*/*/*" mode="row"/>
  </xsl:template>

rsp · Accepted Answer · 2011-05-04T12:10:22.730

1

To convert the XML to comma delimeted is just the same as any other transformation. The following reproduces the fields as-is so unless they are in the same order in every PRODUCT you need to handle that still. The example will get you started:

  <xsl:output method="text"/>

  <xsl:template match="//PRODUCT">
        <xsl:value-of select="@ITEM" />
    <xsl:text>,</xsl:text>
    <xsl:apply-templates select="./*" />
    <xsl:text>&#x0A;</xsl:text>
  </xsl:template>

  <xsl:template match="PRODUCT/*">
        <xsl:value-of select="translate(text(), ',', '@')" />
    <xsl:text>,</xsl:text>
  </xsl:template>

Btw, the &#0A; adds a newline, for dos/windows you might need to add a &#0D; too.

Update: commas in values can be escaped by replacing them with another character using the translate() function.

edited May 04 '11 at 12:10

answered May 04 '11 at 09:28

rsp

23,135
6
55
69

Thanks rsp, The XSL works fine but doesn't deal with original commas that I have in the DATA of Discription Element. It also includes the commas that we have in original text. So if I import, it will surely generate separate column so how to deal with this problem? – Mehboob Afridi May 04 '11 at 11:08
@Mehboob, see update - @ might not be your replacement of choice, but is easy to spot :-) – rsp May 04 '11 at 12:11

XSLT (for CSV) for the give XML structure

2 Answers2