How to convert XML to JSON Array using XSLT?

[former_member771623]

I have a scenario where in I am trying to convert the following XML:

<root>
	<firstName>kumar</firstName>
	<lastName>kumar</lastName>
	<contactDetails>
		<emailIds>
			<emailIdType>Primary</emailIdType>
			<emailId>kumar90@kumarte.com</emailId>
		</emailIds>
		<contactNumber>
			<contactType>Mobile</contactType>
			<candidateContact>9999999999</candidateContact>
		</contactNumber>
	</contactDetails>
	<checks>
		<Source>Graduation</Source>
		<checkFields>
			<Name>Registration number/Seat number </Name>
		</checkFields>
		<checkFields>
			<Name>Mode of Study: Full time / Part time / Distance learning course</Name>
		</checkFields>
		<files>
			<fileContent>PDF URL</fileContent>
		</files>
	</checks>
	<checks>
		<Source>Previous</Source>
		<checkFields>
			<Name>Employee code</Name>
		</checkFields>
		<checkFields>
			<Name>Designation</Name>
		</checkFields>
		<files>
			<fileContent>PDF URL</fileContent>
		</files>
	</checks>
</root>

Into a JSON Payload that looks like the following:

```

{
  "firstName": "kumar",
  "lastName": "kumar",
  "contactDetails": {
    "emailIds": [{
        "emailIdType": "Primary",
        "emailId": "kumar0@kumarte.com"
      }],
    "contactNumber": [{
        "contactType": "Mobile",
        "candidateContact": "9999999999"
      }]
  },
  "checks": [
    {
      "Source": "Graduation",
      "checkFields": [
        {
          "Name": "Registration number/Seat number "
        },
        {
          "Name": "Mode of Study: Full time / Part time / Distance learning course"
        }
      ],
      "files": [{
          "fileContent": "<PDF URL>"
        }]
    },
    {
      "Source": "Previous",
      "checkFields": [
        {
          "Name": "Employee code"
        },
        {
          "Name": "Designation"
        }
      ],
      "files": [{
           "fileContent": "<PDF URL>"
        }]
    }
  ]
}

I am trying to achieve this on SAP CPI using XSLT Mapping. I have played around with the following code but I am not getting the desired output. Please find my code below:

<?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0="http://use your namespace">
<xsl:output method="text"/>
<xsl:template match="/root">{
    <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: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>

Through this code, I am getting "checks" and "checkFields" the way i require.

However, I am unable to enclose "emailIds","contactNumber","EICCaseDocuments" and any "files" nodes in an array.

I understand that this is a generic code. But really need some guidance on how to achieve the answer to my query above.

Could really use some help on this.

Thanks!

[sengodan]

Hi megrao1811,

first why are you using xslt. this can be achieved by normal message mapping and xml to json converter.

in the message mapping in the target structure make the fields as string and use the xml to json converter.

it will solve your issue.

Thanks,

Sen

[donepuna]

Hi Meghna,

You can use "XML to JSON Converter" and in the processing parameters use the following settings.

1.Uncheck "Suppress JSON Root element" to remove root.

2.Select "Streaming"

3. in the "Convert XML Elements to JSON Array (Specified Ones)" , click on add button on the right.

enter the following xml elements.

/root/contactDetails/emailIds
/root/contactDetails/contactNumber
/root/checks/files

You will get the result as you expected.

Thanks

Nagesh

[Robert-Fels]

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text" />

  <xsl:template match="/">
    {
      "firstName": "<xsl:value-of select="root/firstName"/>",
      "lastName": "<xsl:value-of select="root/lastName"/>",
      "contactDetails": {
        "emailIds": [
          <xsl:for-each select="root/contactDetails/emailIds">
            {
              "emailIdType": "<xsl:value-of select="emailIdType"/>",
              "emailId": "<xsl:value-of select="emailId"/>"
            }
          </xsl:for-each>
        ],
        "contactNumber": [
          <xsl:for-each select="root/contactDetails/contactNumber">
            {
              "contactType": "<xsl:value-of select="contactType"/>",
              "candidateContact": "<xsl:value-of select="candidateContact"/>"
            }
          </xsl:for-each>
        ]
      },
      "checks": [
        <xsl:for-each select="root/checks">
          {
            "Source": "<xsl:value-of select="Source"/>",
            "checkFields": [
              <xsl:for-each select="checkFields">
                {
                  "Name": "<xsl:value-of select="Name"/>"
                }
              </xsl:for-each>
            ],
            "files": [
              <xsl:for-each select="files">
                {
                  "fileContent": "<xsl:value-of select="fileContent"/>"
                }
              </xsl:for-each>
            ]
          }
        </xsl:for-each>
      ]
    }
  </xsl:template>
</xsl:stylesheet>

[rajaravi90_stuttgartnetze]

Hello Robert,

I have similar requirement to convert JSON into XML and parse only the rows values into target system. can you help me to achiever it through XSLT mapping as we are using PO. Like For Columns "Workcenter_Code" value of first record is "S_A114M1" and value of second record is "S_A114M1" so I need only the all the 10 values for each record and map it to target structure.

{"outputs":

{"Result_Code":null,

"Result_Error":null,

"Result_Message":null},

"tables":[

{"columns":

["Workcenter_Code","Part_No","Job_No","Operation_Code","Log_Date","Workcenter_Status","Duration","Op_No","Update_Date","Shift"],

"rows":[

["S_A114M1","6026581","7603942","Laserschweißen (laserweld)","2023-07-01T05:00:00Z","09 Aus (Off)",8.00000,10,"2023-08-30T09:57:00Z","3rd"],

["S_A114M1","6026581","7603942","Laserschweißen (laserweld)","2023-07-03T13:00:00Z","09 Aus (Off)",8.00000,10,"2023-08-30T09:57:00Z","2nd"],],

"rowLimitExceeded":false}],

"transactionNo":"258654"

}

Output XML

<MessageType>

<Workcenter_Code>S_A114M1</Workcenter_Code>

<Part_No>6026581</Part_No>