JSONX to JSON transformation

Question

I have some XML data dumped from my database and I need to re-format it into JSON. I am using an IBM DataPower database, so I actually need to transform that XML to JSONx and then use IBM's default translator, which automatically translates JSONx to JSON.

I am having difficulty in defining the elements of a JSONx array. Here's what I'm currently trying:

XML

<sql result="success">
    <row>
        <column>
            <name>Prod</name>
            <value>Acura</value>
        </column>
        <column>
            <name>Color</name>
            <value>SILVER</value>
        </column>
        <column>
            <name>Prod</name>
            <value>Accord</value>
        </column>
        <column>
            <name>Color</name>
            <value>Gold</value>
        </column>
    </row>
</sql>

Desired JSON output

{"Category" : [
    {“prod”: “Acura”, "Color" : “Silver”},
    {“prod”: “Accord”, "Color" : “Gold”}
    ],
    "Status" : “Success”
    }

The problem I'm having is that I can't get prod and color to be part of the same JSON object. Instead, I am getting output like this:

{"Category": [{
    "ID":    [
        ": Acura",
        ": Accord"
        ],
    "NAME":    [
        ": SILVER",
        ": Gold"
        ]
}]}

Here's the JSONx code that I'm using that produces the problematic JSON:

<json:object
    xsi:schemaLocation="http://www.datapower.com/schemas/json jsonx.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx">
    <json:array name="Category">
        <json:object>
            <xsl:for-each select="//column">
                <xsl:variable name="colName" select="name" />
                <xsl:if test="$colName = 'Prod'">
                    <json:string name="Prod">:
                        <xsl:value-of select="value" />
                    </json:string>
                </xsl:if>
                <xsl:if test="$colName = 'Color'">
                    <json:string name="Color">:
                        <xsl:value-of select="value" />
                    </json:string>
                </xsl:if>
            </xsl:for-each>
        </json:object>
    </json:array>
</json:object>

I can tell that the <xsl:for-each> tag is going through one condition and creating a JSON object in the loop, but I don't understand how to create the JSON object after it gets the values of both color and prod. How can I make sure that those values are parsed correctly?

Answer 1

Hi I got the answer,I have tried multiple possibilities,This is one of the posibility,I will get the output like i mentioned in that question

<json:object xsi:schemaLocation="http://www.datapower.com/schemas/json jsonx.xsd"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx">
    <json:array name="Categories">

<xsl:for-each select="sql/row">
<json:object>
<xsl:for-each select="column">
<xsl:variable name="colName" select="name" />
<xsl:choose>
<xsl:when test="$colName = 'PROD ">
<json:string name="name">  <xsl:value-of select="value"/></json:string>
</xsl:when>
<xsl:when test="$colName = 'NAME'">
<json:string name="ID">  <xsl:value-of select="value"/></json:string>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</json:object>
</xsl:for-each>
</json:array>
   </json:object> 

output

{"Categories": [
      {
      "prod": "Acura",
      "color": "SILVER"
   },
      {
      "prod": "Accord",
      "color": "Gold"
   }
]}

Answer 2

You have to work more on the database to xml parsing, to get an xml like this:

<product-list>
    <product>
        <name>Acura</name>
        <color>SILVER</color>
    </product>
    <product>
        <name>Accord</name>
        <color>Gold</color>
    </product>
</product-list>

this way will be more achievable to parse it as you want.

The xml you showing, maybe is possible to do this but it will drive you to unreasonable complexity ... i think

edit: my programming environment is not ready for something like this ... so let me know for the results

<json:object xsi:schemaLocation="http://www.datapower.com/schemas/json jsonx.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx">
    <json:array name="Category">
        <xsl:for-each select="product">
            <json:object>
                <json:string name="prod"><xsl:value-of select="name"/></json:string>
                <json:string name="Color"><xsl:value-of select="color"/></json:string>
            </json:object>
        <xsl:for-each>
    <json:array>
</json:object>

or this way

<json:object xsi:schemaLocation="http://www.datapower.com/schemas/json jsonx.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx">
    <json:array name="Category">
        <xsl:for-each select="product">
            <xsl:text>{<xsl:text>
                <json:string name="prod"><xsl:value-of select="name"/></json:string>
                <json:string name="Color"><xsl:value-of select="color"/></json:string>
            <xsl:text>}<xsl:text>
        <xsl:for-each>
    <json:array>
</json:object>

i think the first is fine ... in case where the object needs name, choose the last.