Community Articles

Find and share helpful community-sourced technical articles.
Announcements
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.
Labels (1)

Easily convert any XML document to JSON format using TransformXML processor. Save following stylesheet in a file. Use TransformXML processor and specify xslt stylesheet. It will convert any XML document to JSON format.

<?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:template>
    <!-- Array Element -->
    <xsl:template match="*" mode="ArrayElement">
        <xsl:call-template name="Properties"/>
    </xsl:template>
    <!-- Object Properties -->
    <xsl:template name="Properties">
        <xsl:variable name="childName" select="name(*[1])"/>
        <xsl:choose>
            <xsl:when test="not(*|@*)">"<xsl:value-of select="."/>"</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>

That's it !

26,142 Views
Comments
Cloudera Employee

The above XSL, has a small bug. For example if you take the following XML:

<MyElement myAttribute="100" />

It will produce the following JSON:

{
    "MyElement" :
    {
        "myAttribute" : "100",    
	}
}

In essence if there is a single attribute there should not be a comma. Below is an updated version of the above that fixes the above.

<?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:template>
    <!-- Array Element -->
    <xsl:template match="*" mode="ArrayElement">
        <xsl:call-template name="Properties"/>
    </xsl:template>
    <!-- Object Properties -->
    <xsl:template name="Properties">
        <xsl:variable name="childName" select="name(*[1])"/>
        <xsl:choose>
            <xsl:when test="not(*|@*)">"<xsl:value-of select="."/>"
            </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="@*"/>
                <!-- 
                    Handle case where parent has a sibling and there are attributes, thus we need a comma after the last
                    attribute
                -->
                <xsl:if test="count(descendant::*) > 0">,</xsl:if>
                <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="."/>"
        <!-- Only add the comma if the current attribute is not the last one for this element -->
        <xsl:if test="position() != last()">,</xsl:if>
    </xsl:template>
</xsl:stylesheet>
Not applicable

Hi,

on generating arrays I found that this does only work if the first child of an object is a repeating one (see input below).

If I remove the K01 segment the K14s will go into an Array.

Do you have any hints?

Hans

Input:

<?xml version="1.0" encoding="ISO-8859-1"?> <ORDERS05> <IDOC BEGIN="1"> <E1EDK01 SEGMENT="1"> <ACTION>000</ACTION> <CURCY>EUR</CURCY> <WKURS>1.00000</WKURS> <ZTERM>V1A3</ZTERM> <BELNR>0011592846</BELNR> <VSART>01</VSART> <VSART_BEZ>Standard per LKW</VSART_BEZ> <RECIPNT_NO>A132125</RECIPNT_NO> </E1EDK01> <E1EDK14 SEGMENT="1"> <QUALF>006</QUALF> <ORGID>01</ORGID> </E1EDK14> <E1EDK14 SEGMENT="1"> <QUALF>007</QUALF> <ORGID>01</ORGID> </E1EDK14> <E1EDK14 SEGMENT="1"> <QUALF>008</QUALF> <ORGID>I001</ORGID> </E1EDK14> <E1EDK14 SEGMENT="1"> <QUALF>012</QUALF> <ORGID>ZDEK</ORGID> </E1EDK14> <E1EDK14 SEGMENT="1"> <QUALF>010</QUALF> <ORGID>060</ORGID> </E1EDK14> <E1EDK14 SEGMENT="1"> <QUALF>016</QUALF> <ORGID>0043</ORGID> </E1EDK14> </IDOC> </ORDERS05>

Please use the XSLT code located here - it is more complete than the XSLT code provided

Contributor

There is problem with both XSLT schema.

Below example is not converted correct.

<?xml version="1.0" encoding="UTF-8"?>
<note>
<country>
<city>Stockholm</city>
</country>
<country>
<code>FR</code>
<city>Paris</city>
<streets>
<road>Paris</road>
</streets>
</country>
<country>
<code>DK</code>
<city>Copenhagen</city>
<streets>
<road>Paris</road>
<no>1</no>
</streets>
</country>
</note>
Not applicable

I applied this stylesheet, and it does convert xml to json. But problem is the json format is not 100% correct, for example the ':' is becoming ',', and the last child always having ',' there cause problem.

Can we please get the stylesheet corrected?

Thanks.

@James Dong @Hans Pointner This stylesheet is incomplete for arbitrary XML conversion, I suggest you try https://github.com/bramstein/xsltjson I've documented use of it with the TransformXML processor in a new article here: https://community.hortonworks.com/content/kbentry/105547/nifi-xml-to-json-shredding-a-generalised-so...

New Contributor

Loved the article and I would use this tool to validate the result. https://jsonformatter.org/xml-formatter

New Contributor

@Shishir Saxena I think this formatter puts a comma after every json element, before '}' too. For example '..., "key": "value", }'. These an invalid json is created.

This is working as I experienced: https://github.com/bramstein/xsltjson/blob/master/conf/xml-to-json.xsl

can you please upload the sample xml and nifi template here

Thanks

Nitin

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.
Version history
Last update:
‎04-25-2016 08:33 PM
Updated by:
Contributors
Top Kudoed Authors