xsl to convert xml to json

Question

Input XML

<Root>
    <Result>
        <System>
            <Name>ABC</Name>
            <ID pname="PAD">
                <value>4567</value>
            </ID>
            <lastTime>2013-11-06T17:36:46.000-05:00</lastTime>
        </System>
        <line>Metals</line>
    </Result>
    <Result>
        <System>
            <Name>CAYS</Name>
            <ID pname="PAD">
                <value>MCIERT</value>
            </ID>
            <ID pname="ATPAD">
                <value>56412</value>
            </ID>
            <lastTime>2013-12-06T16:43:36.000-05:00</lastTime>
        </System>
        <System>
            <Name>CAYS</Name>
            <ID pname="CAD">
                <value>DGSG</value>
            </ID>
            <ID pname="ARCAD">
                <value>2847114</value>
            </ID>
            <lastTime>2013-12-07T20:02:38.000-05:00</lastTime>
        </System>
        <line>Minerals</line>
    </Result>
</Root>

Output Json

{
"Root": {
"Result": [
  {
    "System": {
      "Name": "ABC",
      "ID": {
        "pname": "PAD",
        "value": "4567"
      },
      "lastTime": "2013-11-06T17:36:46.000-05:00"
    },
    "line": "Metals"
  },
  {
    "System": [
      {
        "Name": "CAYS",
        "ID": [
          {
            "pname": "PAD",
            "value": "MCIERT"
          },
          {
            "pname": "ATPAD",
            "value": "56412"
          }
        ],
        "lastTime": "2013-12-06T16:43:36.000-05:00"
      },
      {
        "Name": "CAYS",
        "ID": [
          {
            "pname": "CAD",
            "value": "DGSG"
          },
          {
            "pname": "ARCAD",
            "value": "2847114"
          }
        ],
        "lastTime": "2013-12-07T20:02:38.000-05:00"
      }
    ],
    "line": "Minerals"
  }
]
}
}

How to write the generic xslt stylesheet which will convert input xml to json

Input might have so many Results under root, Systems and Names under results and also ID name and the values under systems.

Lesseps

Answer 1

The below XSLT which I copied and pasted from here should help you to convert XML to JSON. Thanks :)

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>

<xsl:template match="/">{
    <xsl:apply-templates select="*"/>}
</xsl:template>

<!-- Object or Element Property-->
<xsl:template match="*">
    "<xsl:value-of select="name()"/>" :<xsl:call-template name="Properties">
        <xsl:with-param name="parent" select="'Yes'"> </xsl:with-param>
    </xsl:call-template>
</xsl:template>

<!-- Array Element -->
<xsl:template match="*" mode="ArrayElement">
    <xsl:call-template name="Properties"/>
</xsl:template>

<!-- Object Properties -->
<xsl:template name="Properties">
    <xsl:param name="parent"></xsl:param>
    <xsl:variable name="childName" select="name(*[1])"/>
    <xsl:choose>            
        <xsl:when test="not(*|@*)"><xsl:choose><xsl:when test="$parent='Yes'"> <xsl:text>&quot;</xsl:text><xsl:value-of select="."/><xsl:text>&quot;</xsl:text></xsl:when>
                <xsl:otherwise>"<xsl:value-of select="name()"/>":"<xsl:value-of  select="."/>"</xsl:otherwise>
            </xsl:choose>           
        </xsl:when>                
        <xsl:when test="count(*[name()=$childName]) > 1">{ "<xsl:value-of  select="$childName"/>" :[<xsl:apply-templates select="*" mode="ArrayElement"/>] }</xsl:when>
        <xsl:otherwise>{
            <xsl:apply-templates select="@*"/>
            <xsl:apply-templates select="*"/>
            }</xsl:otherwise>
    </xsl:choose>
    <xsl:if test="following-sibling::*">,</xsl:if>
</xsl:template>

<!-- Attribute Property -->
<xsl:template match="@*">"<xsl:value-of select="name()"/>" : "<xsl:value-of select="."/>",
</xsl:template>
</xsl:stylesheet>

Alba

Please use some JSON validatior option after converting XML to JSON. – Alba

Everything worked fine. but at the last it is not giving element name for minerals },"Minerals"] }] }} – Lesseps

I am checking on this – Alba

Please find the edited XSL. Working fine. Earlier There was miss in element which is having only the text as child and not part of any parent call. – Alba

There are some existing xslt , that are generic (gist.github.com/inancgumus/3ce56ddde6d5c93f3550b3b4cdc6bcb8) – Gavrielle

This XSLT outputs invalid JSON if an XML element only has one child. – Vociferous

@IsaacGSivaa Can you give a tip how to handle white-spaces & empty lines in the output json – Featureless

Very useful, but gives invalid JSON if there are double quotes for strings inside a XML node. – Albertina

Answer 2

@sancelot posted a comment with a link to a github stylesheet which I tested alongside the answered instance (I upvoted). Additional detail here to show the following examples.

Example XML:

<avpList>
  <eComStringAttributeValuePairList attributeName="orderTypeCode" qualifierCodeName="order" qualifierCodeList="OrderTypeCode" qualifierCodeListVersion="2">220</eComStringAttributeValuePairList>
  <eComStringAttributeValuePairList attributeName="orderPriority">647</eComStringAttributeValuePairList>
  <eComStringAttributeValuePairList attributeName="customerDocumentReference">0</eComStringAttributeValuePairList>
</avpList>

If I have a mixed attribute node value and use the upvoted answer I get the following output:

            "avpList": {
            "eComStringAttributeValuePairList": [
                {
                    "attributeName": "orderTypeCode",
                    "qualifierCodeName": "order",
                    "qualifierCodeList": "OrderTypeCode",
                    "qualifierCodeListVersion": "2",
                },
                {
                    "attributeName": "orderPriority",
                },
                {
                    "attributeName": "customerDocumentReference",
                }
            ]
        }

Running it though JSONLint shows the issues with the trailing commas in addition to the missing element value.

Error: Parse error on line 30: ...Version": "2",                    }

Scripts parsed using XML Spy 2019.

Using the commented Github link and parsing produces the following JSON which validates on first pass through the linter. Please note that if you're expected any form of typed data in your JSON, neither stylesheets do it.

            "avpList": {
            "eComStringAttributeValuePairList": [
                {
                    "attributeName": "orderTypeCode",
                    "qualifierCodeName": "order",
                    "qualifierCodeList": "OrderTypeCode",
                    "qualifierCodeListVersion": "2",
                    "text": "220"
                },
                {
                    "attributeName": "orderPriority",
                    "text": "647"
                },
                {
                    "attributeName": "customerDocumentReference",
                    "text": "0"
                }
            ]
        }

Tica