Community Articles

Find and share helpful community-sourced technical articles.
Announcements
Celebrating as our community reaches 100,000 members! Thank you!
Labels (1)
avatar

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 !

28,670 Views
Comments
avatar
Contributor

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>
avatar
New Contributor

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>

avatar

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

avatar
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>
avatar
New Contributor

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.

avatar

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

avatar
New Contributor

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

avatar
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

avatar

can you please upload the sample xml and nifi template here

Thanks

Nitin