Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Converting XML Records to JSON Using XSLT Transformation – Issue with Number Formatting

I’m working on a project where I fetch data from a database using a stored procedure. The data is returned in XML format like this:

<record>
      <field name="ID">8f7e-25fbcce2cdc7</field>
      <field name="TRANSACTIONSTATUS">202</field>
      <field name="TRANSAMT">1.90</field>
      <field name="SNDNAME">MR. Jade</field>
      <field name="RECVNAME">MR. Lucas</field>
      <field name="SNDACCTNUMBER">8224002623</field>
      <field name="RECVACCTNUMBER">5666666</field>
</record>

I’m using XSLT transformation in WSO2 EI to convert this XML into JSON format. Here’s the XSLT code I’m using:

<?xml version="1.0" encoding="UTF-8"?>
<localEntry key="CTDetailsById_Formatter_LE" xmlns="http://ws.apache.org/ns/synapse">
    <xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:output encoding="UTF-8" indent="yes" method="xml" omit-xml-declaration="yes" version="1.0"/>
        <xsl:template match="/">
            <xsl:variable name="ctDetails" select="//*[local-name()='table']/*[local-name()='record']"/>
            <jsonElement>
                <xsl:element name="transactionId">
                    <xsl:value-of select="$ctDetails/*[local-name()='field'][@name='ID']"/>
                </xsl:element>
                <xsl:element name="status">
                    <xsl:variable name="TrnStatus" select="$ctDetails/*[local-name()='field'][@name='TRANSACTIONSTATUS']"/>
                    <xsl:choose>
                        <xsl:when test="$TrnStatus='202'">
                            <xsl:value-of select="'pending'"/>
                        </xsl:when>
                        <xsl:when test="$TrnStatus='204'">
                            <xsl:value-of select="'initiated'"/>
                        </xsl:when>
                        <xsl:when test="$TrnStatus='203'">
                            <xsl:value-of select="'completed'"/>
                        </xsl:when>
                        <xsl:otherwise/>
                    </xsl:choose>
                </xsl:element>
                <xsl:element name="credittrasfer">
                    <xsl:element name="amount">
                        <xsl:value-of select="$ctDetails/*[local-name()='field'][@name='TRANSAMT']"/>
                    </xsl:element>
                    <xsl:element name="sendername">
                        <xsl:value-of select="$ctDetails/*[local-name()='field'][@name='SNDNAME']"/>
                    </xsl:element>
                    <xsl:element name="receivername">
                        <xsl:value-of select="$ctDetails/*[local-name()='field'][@name='RECVNAME']"/>
                    </xsl:element>
                    <xsl:element name="senderacctnumber">
                        <xsl:value-of select="$ctDetails/*[local-name()='field'][@name='SNDACCTNUMBER']"/>
                    </xsl:element>
                    <xsl:element name="receiveracctnumber">
                        <xsl:value-of select="$ctDetails/*[local-name()='field'][@name='RECVACCTNUMBER']"/>
                    </xsl:element>
                </xsl:element>
            </jsonElement>
        </xsl:template>
    </xsl:transform>
</localEntry>

The problem I’m facing is with the formatting of numbers in the JSON output. For example, the SNDACCTNUMBER and RECVACCTNUMBER field should be a string in the JSON output, but it’s coming out as a number without quotes. Here’s the JSON output I’m getting:

{
    "transactionId": "M20240425101010101MTBOTS06651000849",
    "status": "pending",
    "credittrasfer": {
        "amount": 1.90,
        "sendername": "MR. Jade",
        "receivername": "MR. Lucas",
        "senderacctnumber": 8224002623, // This should be "8224002623"
        "receiveracctnumber": 5666666 // This should be "5666666"
    }
}

I’ve tried different approaches in the XSLT code to format the numbers as strings in the JSON output, but I haven’t been successful so far. Can someone guide me on how to ensure that specific fields like SNDACCTNUMBER and RECVACCTNUMBER are formatted as strings in the JSON output using XSLT?

Tags:

2

Answers


  1. 0

    This behavior is acceptable in the wso2 synapse engine. when the XML to json transformation is happening, there is no information to decide whether the primitive data type is number or string. hence by default, it treate numbers as numbers. if you need to change the json message as you need, let’s use JSON Transform Mediator[1] after converting the message to json. Please note that this mediator is available with EI 660 onwards.

    [1] https://ei.docs.wso2.com/en/latest/micro-integrator/references/mediators/json-Transform-Mediator/

    Login or Signup to reply.
  2. 0

    Did you try:

    <xsl:element name="senderacctnumber">
   <xsl:text>'</xsl:text>
   <xsl:value-of select="$ctDetails/*[@name='SNDACCTNUMBER']"/>
   <xsl:text>'</xsl:text>
</xsl:element>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search