XML to CSV using XSLT namespace in root element

Question

I am new to XML and XSLT and have a need to convert an Rest API response XML to CSV using XSLT and I need the XSLT doc to attach to the program. I tried several online tutorials but the transformation is reading all the elements not just the way I need them. Can some one please help me!

XML is below

    <convertTo xmlns="http://xecdapi.xe.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://xecdapi.xe.com/schema/v1/convertTo.xsd" class=" cd-browser-extension">
<terms>http://www.xe.com/legal/dfs.php</terms>
<privacy>http://www.xe.com/privacy.php</privacy>
<to>USD</to>
<amount>1.0</amount>
<timestamp>2018-10-25T00:00:00Z</timestamp>
<from>
<rate>
<currency>AUD</currency>
<mid>1.4160280983</mid>
</rate>
<rate>
<currency>SGD</currency>
<mid>1.3814918146</mid>
</rate>
<rate>
<currency>EUR</currency>
<mid>0.8773448168</mid>
</rate>
<rate>
<currency>GBP</currency>
<mid>0.7760517332</mid>
</rate>
<rate>
<currency>CAD</currency>
<mid>1.3048398838</mid>
</rate>
<rate>
<currency>INR</currency>
<mid>73.3497808743</mid>
</rate>
</from>
</convertTo>

XSLT I was trying to output first 2 elements that I need.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
          xmlns:zz="http://xecdapi.xe.com" version="2.0" >

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

  <xsl:template match="//zz:convertTo/zz:from/zz:rate">
     <xsl:for-each select = "//zz:convertTo/zz:from/zz:rate">
     <xsl:value-of select = "zz:currency"/>
        <xsl:value-of select="zz:mid"/>

        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

CSV output needed

EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate
2018-10-26,USD,INR,CURRENT,73.865

CSV Imange

Can you please edit your question to show the latest XSLT you have tried, even if it doesn't produce anything? I suspect one problem you are having is that your XML has a "default namespace" (The `xmlns="http://xecdapi.xe.com"`). It might help you if you looked at this question then: https://stackoverflow.com/questions/1344158/xslt-with-xml-source-that-has-a-default-namespace-set-to-xmlns . Thanks! — Tim C, Oct 28 '18 at 11:15
Thank you Tim, It seems to be an issue with the name space as you suggested. When I take out all the namespace info from the root element it is working. I will try the link you mentioned. Thanks! - Syd — syd sri, Oct 28 '18 at 18:54
@syd sri - You might want to change the title for this question, because your XSLT is handling namespaces correctly, which is good! The problem lies elsewhere now. Thanks! — Tim C, Oct 29 '18 at 10:56

Tim C · Answer 1 · 2018-10-29T11:02:15.883

You have a problem with this template....

<xsl:template match="//zz:convertTo/zz:from/zz:rate">
   <xsl:for-each select="//zz:convertTo/zz:from/zz:rate">
     <xsl:value-of select="zz:currency"/>
    <xsl:value-of select="zz:mid"/>
  </xsl:for-each>
</xsl:template>

Firstly (and strictly speaking, this is not actually a problem in this case), you don't need the full path to zz:rate specified. You can just do this...

<xsl:template match="zz:rate">

Secondly (and this is a problem) within the template you do this...

<xsl:for-each select="//zz:convertTo/zz:from/zz:rate">

But when you start an expression with // this effectively select nodes anywhere in the XML document regardless of the current node you are matching. Effectively you are saying "for every zz:rate in the document, get all zz:rates"

In fact, you don't need this xsl:for-each at all. You are already in a template matching zz:rate. What you just need to do, is select the zz:rate elements you want in the previous template, which will stop nodes such as terms being output.

Try this XSLT (Note it is missing one field as I wasn't sure where "CurrencyRateType" comes from

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:zz="http://xecdapi.xe.com" version="2.0" >

  <xsl:template match="/">
    <xsl:text>EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate&#10;</xsl:text>
    <xsl:apply-templates select="//zz:convertTo/zz:from/zz:rate" />
  </xsl:template>

  <xsl:template match="zz:rate">
    <xsl:value-of select="substring(../../zz:timestamp, 1, 10)"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="zz:currency"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="../../zz:to"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="zz:mid"/>
    <xsl:text>&#10;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

EDIT: Actually, if you are using XSLT 2.0, you can shorten the final template to this...

<xsl:template match="zz:rate">
  <xsl:value-of select="substring(../../zz:timestamp, 1, 10), zz:currency, ../../zz:to, zz:mid" separator=","/>
  <xsl:text>&#10;</xsl:text>
</xsl:template>

Hi Tim C, Thank you so much. I am very new to XML and XSLT and trying different things by reading w3schools and other stuff on google. Appreciate your detailed explanation on where the problem is. It is working now and the field 'CURRENT' is a default value for all rows which I will add. It is working as desired appreciate all your help! thanks - Sri — syd sri, Oct 29 '18 at 14:00

XML to CSV using XSLT namespace in root element

1 Answers1