NiFi - Converting XML to JSON

[#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 !

[#2]

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>

[#3]

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>

[#4]

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

[#5]

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>

[#6]

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.

[#7]

@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...

[#8]

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

[#9]

@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

[#10]

can you please upload the sample xml and nifi template here

Thanks

Nitin