json to csv using xslt

Question

I'm new to the xslt.

I'm trying to converting json to csv using xslt.

Here is the input json

[
    {
        "id": "1",
        "name": "manu"
    },
    {
        "id": "2",
        "name": "vivek"
    }
]

and XSLT is:-

I'm very sure I'm not doing the proper mapping in the select attribute.

Can anyone please help me with this

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    extension-element-prefixes="math"
    xmlns:math="http://exslt.org/math" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema" version="3.0" xmlns="http://www.w3.org/2005/xpath-functions"  xpath-default-namespace="http://www.w3.org/2005/xpath-functions" expand-text="yes" > 
    <xsl:param name="input"/> 
    <xsl:output method="text"/> 
    
    <xsl:template match="/">
    <xsl:apply-templates/>
    </xsl:template>

<xsl:template name="initial-template"> 
<xsl:variable name="input-as-xml" select="json-to-xml($input)"/> 
       
<xsl:for-each select="$input-as-xml//*">

            
    <xsl:variable name="eachData" select="."></xsl:variable>
    <xsl:choose>
        <xsl:when test="contains($eachData,',')">
            <xsl:value-of select="replace($eachData, ',', ' ')"/>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="$eachData"/>
        </xsl:otherwise>
    </xsl:choose>
    <xsl:if test="position() != last()">
        <xsl:value-of select="','"/>
    </xsl:if>

</xsl:for-each>
<xsl:text>&#10;</xsl:text>
</xsl:template>           
</xsl:stylesheet>

Daniel Haley · Accepted Answer · 2020-10-09 15:41:38Z

2

No need to convert this to XML, you can convert the JSON to CSV directly.

Apart from special handling of commas, it should simply be

<xsl:value-of select="
   parse-json($json-string) =>
   array:for-each(function($row){
      $row?id || ',' || $row?name
   } =>
   string-join('&#xa;')
"/>

This assumes that the names "id" and "name" are known in advance. If they aren't, things get a bit more complicated because the order of entries in a map in JSON is supposed to have no significance. In XSLT 3.0, when you parse JSON into a map, the original order of entries is not retained. If you know the order of entries in the JSON and want to retain it, then going via XML is probably the answer, because the json-to-xml() function (unlike parse-json()) does retain the order of entries.

edited Oct 9, 2020 at 15:41

Daniel Haley

53.1k7 gold badges75 silver badges97 bronze badges

answered Oct 9, 2020 at 11:07

Michael Kay

165k11 gold badges97 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Honnen · Accepted Answer · 2020-10-09 08:29:22Z

0

If you know the structure of the JSON is simply a plain array of objects you can use

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:map="http://www.w3.org/2005/xpath-functions/map"
    xmlns:array="http://www.w3.org/2005/xpath-functions/array"
    exclude-result-prefixes="#all"
    version="3.0">
    
  <xsl:param name="json-string" as="xs:string">[
    {
        "id": "1",
        "name": "manu"
    },
    {
        "id": "2",
        "name": "vivek"
    }
]</xsl:param>

  <xsl:param name="json-xml" select="json-to-xml($json-string)"/>

  <xsl:output method="text"/>

  <xsl:template match="/" name="xsl:initial-template">
    <xsl:value-of select="$json-xml/*/*!string-join(*, ',')" separator="&#10;"/>
  </xsl:template>
  
</xsl:stylesheet>

Or use

  <xsl:template match="/" name="xsl:initial-template">
    <xsl:value-of select="$json-xml/*/(string-join(head(*)/*/@key, ','), *!string-join(*, ','))" separator="&#10;"/>
  </xsl:template>

if you want a header line as well.

edited Oct 9, 2020 at 8:29

answered Oct 9, 2020 at 8:15

Martin Honnen

169k6 gold badges100 silver badges122 bronze badges

3 Comments

manu Over a year ago

Martin thanks for your time. but there is one issue here..if the data itself has comma then how its handling. My assumption is, if the data has comma then it should be inside the double quotes. example:- "vi,ek"

Toolbox Over a year ago

Can same be applied if needing to have more depth in the json data? Like: <data>{"datasheets": {"result-sheet": {"bank": 1},"balance-sheet": {"credit": 2}}}</data>

Martin Honnen Over a year ago

@Toolbox, perhaps give it a try and experiment, if you can't get it to work then post a question showing exactly what input you have, which result you want, which XSLT you have and how it fails.

Collectives™ on Stack Overflow

json to csv using xslt

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related