Using XSLT to transform XML to JSON and add 2 Square bracket [[]]

Question

Request to share the xslt code for xml to json conversion. we have a requirement to convert xml to json. then Trim the OuterElement and multiple square brackets [[ a) Trim the OuterElement <ns0:PO_Message xmlns:ns0="http://test.com/Test"> b) The last 2 lineitems(ShipmentRefference, ShipGroupList) with 2 squarebrackets [[

Input xml file:

<?xml version="1.0" encoding="UTF-8"?>
<ns0:PO_Message
    xmlns:ns0="http://test.com/Test">
    <domainId>MAIL</domainId>
    <hubDomainId>MAIL</hubDomainId>
    <isForReference>false</isForReference>
    <status>releasedToVendor</status>
    <docStatus>active</docStatus>
    <editingStatus>confirmed</editingStatus>
    <vpoNo>2826118</vpoNo>
    <vpoDate>2021-02-23</vpoDate>
    <instructions>METAL-No Size:1360600001-Article Number</instructions>
    <businessRefNo>826118</businessRefNo>
    <totalItems>1</totalItems>
    <totalQty>160</totalQty>
    <season>
        <code>F21</code>
    </season>
    <custId>
        <refNo>C000001</refNo>
    </custId>
    <vendorId>
        <vendorCode>41843</vendorCode>
    </vendorId>
    <headerFactory>
        <refNo>F000026</refNo>
        <factCode>900088</factCode>
    </headerFactory>
    <vpoItemList>
        <itemNo>19979-2021-0002-41843</itemNo>
        <itemName>Base Camp 6 Footprint</itemName>
        <customerItemNo>19979</customerItemNo>
        <lotNo>1</lotNo>
        <itemDesc>Base Camp 6 Footprint</itemDesc>
        <shipQty>160</shipQty>
        <planedQty>160</planedQty>
        <qtyPerExportCarton>160</qtyPerExportCarton>
        <qtyPerInnerCarton>160</qtyPerInnerCarton>
        <factCode>900088</factCode>
        <refNo>19979-2021-0002-41843-1</refNo>
        <uom>
            <code>PCS</code>
        </uom>
        <brand>
            <code>MAIL</code>
        </brand>
        <itemId>
            <itemNo>19979-2021-0002-41843</itemNo>
        </itemId>
        <portOfLanding>
            <code>DLC Dalian</code>
        </portOfLanding>
        <vpoItemCsList>
            <itemLotNo>19979-2021-0002-41843-Lot1</itemLotNo>
            <vpoItemColorRef>METAL</vpoItemColorRef>
            <vpoItemColorId>
                <refNo>GUNMETAL</refNo>
                <shortName>METAL</shortName>
            </vpoItemColorId>
            <vpoItemSizeId>
                <refNo>No Size</refNo>
                <displayName>No Size</displayName>
            </vpoItemSizeId>
            <itemId>
                <iteamNo>19979-2021-0002-41843</iteamNo>
            </itemId>
            <lotNo>1</lotNo>
        </vpoItemCsList>
        <factId>
            <factCode>900088</factCode>
        </factId>
    </vpoItemList>
    <vpoShipDtlDtoGroupList>
        <qty>160</qty>
        <refNo>00001/19979-2021-0002-41843-1</refNo>
        <vpoItemRef
            xmlns:ns1="http://sap.com/xi/SAPGlobal/GDT">19979-2021-0002-41843-1
        </vpoItemRef>
        <vpoItemId>
            <itemId>
                <itemNo>19979-2021-0002-41843</itemNo>
            </itemId>
        </vpoItemId>
        <vpoShipRef>00001</vpoShipRef>
        <vpoShipId>
            <shipmentNo>00001</shipmentNo>
            <originalShipmentDate>2021-08-17</originalShipmentDate>
            <shipmentDate>2021-08-23</shipmentDate>
            <originalInDcDate>2021-09-15</originalInDcDate>
            <inDcDate>2021-09-15</inDcDate>
            <refNo>00001/19979-2021-0002-41843-1</refNo>
            <shipMode>
                <code>3</code>
            </shipMode>
            <finalDestination>
                <code>0001</code>
            </finalDestination>
            <portOfLoading>
                <code>DLC Dalian</code>
            </portOfLoading>
        </vpoShipId>
        <shipMode>
            <code>3</code>
        </shipMode>
        <portOfLoading>
            <code>DLC Dalian</code>
        </portOfLoading>
    </vpoShipDtlDtoGroupList>
    <vpoShipDtlCsGroupList>
        <itemLotNo>19979-2021-0002-41843-Lot1</itemLotNo>
        <shipmentNo>00001</shipmentNo>
        <colorSizeQty>160</colorSizeQty>
        <refNo>00001/19979-2021-0002-41843-1/GUNMETAL/No Size</refNo>
        <vpoItemRef>19979-2021-0002-41843-1</vpoItemRef>
        <vpoItemId>
            <itemId>
                <itemNo>19979-2021-0002-41843</itemNo>
            </itemId>
        </vpoItemId>
        <vpoShipRef>00001/19979-2021-0002-41843-1</vpoShipRef>
        <vpoItemColorRef>METAL</vpoItemColorRef>
        <vpoItemSizeRef>No Size</vpoItemSizeRef>
        <vpoShipDtlColorRef>1</vpoShipDtlColorRef>
        <vpoShipDtlSizeRef>1</vpoShipDtlSizeRef>
    </vpoShipDtlCsGroupList>
</ns0:PO_Message>

Expected Json Output File

  { "domainId" : "MAIL",
    "hubDomainId" : "MAIL",
    "isForReference" : "false",
    "status" : "releasedToVendor",
    "docStatus" : "active",
    "editingStatus" : "confirmed",
    "vpoNo" : "2118",
    "vpoDate" : "2021-02-23",
    "instructions" : "METAL-No Size:1360600001-Article Number",
    "businessRefNo" : "2818",
    "totalItems" : "1",
    "totalQty" : "160",
    "season" : 
    { "code" : "F21" },
    "custId" : 
    { "refNo" : "C000001" },
    "vendorId" : 
    { "vendorCode" : "41843" },
    "headerFactory" : 
    { "refNo" : "F000026",
      "factCode" : "900088" },
    "vpoItemList" : 
    [ 
      { "itemNo" : "19979-2021-0002-41843",
        "itemName" : "Base Camp 6 Footprint",
        "customerItemNo" : "19979",
        "lotNo" : "1",
        "itemDesc" : "Base Camp 6 Footprint",
        "shipQty" : "160",
        "planedQty" : "160",
        "qtyPerExportCarton" : "160",
        "qtyPerInnerCarton" : "160",
        "factCode" : "900088",
        "refNo" : "19979-2021-0002-41843-1",
        "uom" : 
        { "code" : "PCS" },
        "brand" : 
        { "code" : "Wide" },
        "itemId" : 
        { "itemNo" : "19979-2021-0002-41843" },
        "portOfLanding" : 
        { "code" : "DLC Dalian" },
        "vpoItemCsList" : 
        [ 
          { "itemLotNo" : "19979-2021-0002-41843-Lot1",
            "vpoItemColorRef" : "METAL",
            "vpoItemColorId" : 
            { "refNo" : "GUNMETAL",
              "shortName" : "METAL" },
            "vpoItemSizeId" : 
            { "refNo" : "No Size",
              "displayName" : "No Size" },
            "itemId" : 
            { "iteamNo" : "19979-2021-0002-41843" },
            "lotNo" : "1" } ],
        "factId" : 
        { "factCode" : "900088" } } ],
    "vpoShipDtlDtoGroupList" : 
    [ 
      [ 
        { "qty" : "160",
          "refNo" : "00001\/19979-2021-0002-41843-1",
          "vpoItemRef" : "19979-2021-0002-41843-1",
          "vpoItemId" : 
          { "itemId" : 
            { "itemNo" : "19979-2021-0002-41843" } },
          "vpoShipRef" : "00001",
          "vpoShipId" : 
          
            { "shipmentNo" : "00001",
              "originalShipmentDate" : "2021-08-17",
              "shipmentDate" : "2021-08-23",
              "originalInDcDate" : "2021-09-15",
              "inDcDate" : "2021-09-15",
              "refNo" : "00001\/19979-2021-0002-41843-1",
              "shipMode" : 
              { "code" : "3" },
              "finalDestination" : 
              { "code" : "0001" },
              "portOfLoading" : 
              { "code" : "DLC Dalian" } } ,
          "shipMode" : 
          { "code" : "3" },
          "portOfLoading" : 
          { "code" : "DLC Dalian" } } ] ],
    "vpoShipDtlCsGroupList" : 
    [ 
      [ 
        { "itemLotNo" : "19979-2021-0002-41843-Lot1",
          "shipmentNo" : "00001",
          "colorSizeQty" : "160",
          "refNo" : "00001\/19979-2021-0002-41843-1\/GUNMETAL\/No Size",
          "vpoItemRef" : "19979-2021-0002-41843-1",
          "vpoItemId" : 
          { "itemId" : 
            { "itemNo" : "19979-2021-0002-41843" } },
          "vpoShipRef" : "00001\/19979-2021-0002-41843-1",
          "vpoItemColorRef" : "METAL",
          "vpoItemSizeRef" : "No Size",
          "vpoShipDtlColorRef" : "1",
          "vpoShipDtlSizeRef" : "1" } ] ] }

XLST Code:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="3.0"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns="http://www.w3.org/2005/xpath-functions"
  exclude-result-prefixes="#all"
  expand-text="yes">

  <xsl:output method="text"/>

  <xsl:template match="/*" priority="5">
    <xsl:variable name="json-xml">
      <map>
        <xsl:apply-templates/>
      </map>        
    </xsl:variable>
    <xsl:value-of select="xml-to-json($json-xml, map { 'indent' : true() })"/>
  </xsl:template>
  
  <xsl:template match="*[not(*)]">
    <string key="{local-name()}">{.}</string>
  </xsl:template>
  
  <xsl:template match="*[(*) and . castable as xs:double]">
    <number key="{local-name()}">{.}</number>
  </xsl:template>
  
  <xsl:template match="*[* and not(*[6])]">
    <map key="{local-name()}">
        <xsl:apply-templates/>
    </map>
  </xsl:template>
  
  <xsl:template match="*[* and *[6]]">
    <array key="{local-name()}">
        <map>
          <xsl:apply-templates/>
        </map>
    </array>
  </xsl:template>
  
  <xsl:template match="vpoShipDtlDtoGroupList | vpoShipDtlCsGroupList" priority="10">
    <array key="{local-name()}">
        <array>
          <map>
            <xsl:apply-templates/>
          </map>          
        </array>
    </array>
  </xsl:template>

</xsl:stylesheet>

Thanks, Ravi

Are you aware of the built-in capabilities of XSLT and XPath 3 to represent JSON in an XML format w3.org/TR/xpath-functions/#json-to-xml-mapping you can then convert to JSON using the function xml-to-json? So all you need is an XML to XML transformation to map your input to the format that function expects and then to apply it. Give it a try and come back when you are stuck. — Martin Honnen
– Martin Honnen, Commented Apr 28, 2021 at 7:13
Hi Martin, I tried with the below code but the output is not as expected. Request to check and modify the code to get the expected output.<xsl:stylesheet version="3.0" xmlns:xsl="w3.org/1999/XSL/Transform" xmlns="w3.org/2005/xpath-functions"> <xsl:output method="text" encoding="UTF-8"/> <xsl:output method="text"/> <xsl:template match="/"> <xsl:value-of select="xml-to-json(.)"/> </xsl:template> </xsl:stylesheet> — raviteja
– raviteja, Commented Apr 28, 2021 at 7:24
Please put your code into well-formatted code samples inside your question, not in comments. And take some time to understand the format the xml-to-json function expects, there is no magic arbitrary XML to JSON transformation, as I said, you first need to write templates to convert your XML into the format the function expects. — Martin Honnen
– Martin Honnen, Commented Apr 28, 2021 at 7:28

Martin Honnen · Accepted Answer · 2021-04-28 08:59:11Z

1

That is basically a two step transformation from your XML to the XML format the xml-to-json function expects. You would use

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="3.0"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns="http://www.w3.org/2005/xpath-functions"
  exclude-result-prefixes="#all"
  expand-text="yes">

  <xsl:output method="xml" indent="yes"/>

  <xsl:mode on-no-match="shallow-skip"/>
  
  <xsl:template match="/*">
    <map>
      <xsl:apply-templates/>
    </map>
  </xsl:template>
  
  <xsl:template match="*[not(*)]">
    <string key="{local-name()}">{.}</string>
  </xsl:template>
  
  <xsl:template match="*[not(*) and . castable as xs:double]">
    <number key="{local-name()}">{.}</number>
  </xsl:template>
  
  <xsl:template match="*[* and not(*[2])]">
    <map key="{local-name()}">
        <xsl:apply-templates/>
    </map>
  </xsl:template>
  
  <xsl:template match="*[* and *[2]]">
    <array key="{local-name()}">
        <map>
          <xsl:apply-templates/>
        </map>
    </array>
  </xsl:template>
  
  <xsl:template match="ShipGroupList | ShipmentRefference" priority="10">
    <array key="{local-name()}">
        <array>
          <map>
            <xsl:apply-templates/>
          </map>          
        </array>
    </array>
  </xsl:template>

  <xsl:template match="/" name="xsl:initial-template">
    <xsl:next-match/>
    <xsl:comment xmlns:saxon="http://saxon.sf.net/">Run with {system-property('xsl:product-name')} {system-property('xsl:product-version')} {system-property('Q{http://saxon.sf.net/}platform')}</xsl:comment>
  </xsl:template>

</xsl:stylesheet>

for the first step which gives (https://xsltfiddle.liberty-development.net/asoTJB/0) e.g.

<?xml version="1.0" encoding="UTF-8"?>
<array xmlns="http://www.w3.org/2005/xpath-functions" key="PO_Message">
   <map>
      <string key="domainId">Mail</string>
      <string key="hubId">Mail</string>
      <array key="MailItemList">
         <map>
            <string key="itemNo">2021-0002</string>
            <string key="itemName">Camp</string>
            <map key="factId">
               <number key="factCode">188</number>
            </map>
         </map>
      </array>
      <array key="ShipGroupList">
         <array>
            <map>
               <number key="GroupList1">123</number>
               <number key="qty">160</number>
               <map key="shipMode">
                  <number key="code">3</number>
               </map>
            </map>
         </array>
      </array>
      <array key="ShipmentRefference">
         <array>
            <map>
               <number key="itemLotNo">199791</number>
               <number key="shipmentNo">1</number>
            </map>
         </array>
      </array>
   </map>
</array>
<!--Run with SAXON HE 9.8.0.15 -->

then a complete stylesheet would use the first step in a variable and apply the xml-to-json function to the variable:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="3.0"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns="http://www.w3.org/2005/xpath-functions"
  exclude-result-prefixes="#all"
  expand-text="yes">

  <xsl:output method="text"/>

  <xsl:template match="/*" priority="5">
    <xsl:variable name="json-xml">
      <map>
        <xsl:apply-templates/>
      </map>        
    </xsl:variable>
    <xsl:value-of select="xml-to-json($json-xml, map { 'indent' : true() })"/>
  </xsl:template>
  
  <xsl:template match="*[not(*)]">
    <string key="{local-name()}">{.}</string>
  </xsl:template>
  
  <xsl:template match="*[not(*) and . castable as xs:double]">
    <number key="{local-name()}">{.}</number>
  </xsl:template>
  
  <xsl:template match="*[* and not(*[2])]">
    <map key="{local-name()}">
        <xsl:apply-templates/>
    </map>
  </xsl:template>
  
  <xsl:template match="*[* and *[2]]">
    <array key="{local-name()}">
        <map>
          <xsl:apply-templates/>
        </map>
    </array>
  </xsl:template>
  
  <xsl:template match="ShipGroupList | ShipmentRefference" priority="10">
    <array key="{local-name()}">
        <array>
          <map>
            <xsl:apply-templates/>
          </map>          
        </array>
    </array>
  </xsl:template>

</xsl:stylesheet>

https://xsltfiddle.liberty-development.net/asoTJB/1

answered Apr 28, 2021 at 8:59

Martin Honnen

169k6 gold badges100 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

raviteja Over a year ago

Thanks Martin, The json file is having additional square brackets for few lineitems.

raviteja Over a year ago

Thanks Martin, The json file is having additional square brackets in the header section "headerFactory" & for Internal lineitems are not arrays. Request to check the code to remove the the square brackets [ for lineitems a) "vpoItemColorId", b) "vpoItemSizeId", c) "itemId" we are using saxon-ee-10.5.jar for conversion.

Martin Honnen Over a year ago

@raviteja, you got a link to the documentation of the format the xml-to-json function expects plus an answer with a working sample and example for the original input; don't expect us to implement any refinements if you edit the question after receiving an answer and change the input data. Try to learn from the answer and to adapt it to your needs.

raviteja Over a year ago

Hi Martin,I have modified the xslt file as <xsl:template match="[ and not(*[6])]"> but still am getting the array [ for one of the line item i.e vpoShipDtlDtoGroupList/ "vpoShipId"[ . we dont want this to be as array. The values are converting as 1 instead of '0001'. we are close to the expected output. Request to the check the xslt code once.

raviteja Over a year ago

Hi Martin - I fixed the values are converting as 1 instead of '0001'. Request to check the array issue for "vpoShipId"[

|

Collectives™ on Stack Overflow

Using XSLT to transform XML to JSON and add 2 Square bracket [[]]

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related