Convert XML to JSON using XSL in Salesforce Marketing Cloud

Using XSLT to transform XML to JSON with the help of AMPscript TransformXML().

XML to JSON

Code snippet #1: XSL File

An XSL file is a stylesheet that can be used to transform XML documents into other document types and to format the output. The AMPscript function TransformXML(1,2) utilises an XSL file as an input to apply an XSL transformation to the specified XML data. Therefore the XSL file is THE essential ingredient, specifying how the XML data should be transformed.

Luckily, Bojan Bjelic has already supplied us with XSL code we can use to convert XML to JSON beautifully.

Firstly, open the XSL file on github (Credit: Bojan Bjelic), copy all the code and paste it into a Code Snippet block within SFMC Content Builder. Ensure to remove any leading or lagging white spaces after you paste.

Xml2Json.xsl

Blog Post with some details here (https://www.bjelic.net/2012/08/01/coding/convert-xml-to-json-using-xslt/)

Code snippet #2: Sample XML

Next we need some XML data that we can transform to JSON. Below is an example script taken from the AMPscript guide. Copy this in to a new Code Snippet block within Content Builder.

%%[
VAR @xml
set @xml = ''
set @xml = concat(@xml,'<?xml version="1.0"?>')
set @xml = concat(@xml,'<loyaltyRewards>')
set @xml = concat(@xml,'    <emailAddress><![CDATA[suzy@limedash.com]]></emailAddress>')
set @xml = concat(@xml,'    <firstName><![CDATA[Suzy]]></firstName>')
set @xml = concat(@xml,'    <rewards>')
set @xml = concat(@xml,'        <reward>')
set @xml = concat(@xml,'            <rewardDate><![CDATA[2017-10-15]]></rewardDate>')
set @xml = concat(@xml,'            <name><![CDATA[Purchase]]></name>')
set @xml = concat(@xml,'            <description><![CDATA[Purchase cash back]]></description>')
set @xml = concat(@xml,'            <amount><![CDATA[3.21]]></amount>')
set @xml = concat(@xml,'        </reward>')
set @xml = concat(@xml,'        <reward>')
set @xml = concat(@xml,'            <rewardDate><![CDATA[2017-10-01]]></rewardDate>')
set @xml = concat(@xml,'            <name><![CDATA[Visit]]></name>')
set @xml = concat(@xml,'            <description><![CDATA[October visit reward]]></description>')
set @xml = concat(@xml,'            <amount><![CDATA[12.34]]></amount>')
set @xml = concat(@xml,'        </reward>')
set @xml = concat(@xml,'        <reward>')
set @xml = concat(@xml,'            <rewardDate><![CDATA[2017-12-01]]></rewardDate>')
set @xml = concat(@xml,'            <name><![CDATA[Referral]]></name>')
set @xml = concat(@xml,'            <description><![CDATA[Referral bonus]]></description>')
set @xml = concat(@xml,'            <amount><![CDATA[43.21]]></amount>')
set @xml = concat(@xml,'        </reward>')
set @xml = concat(@xml,'    </rewards>')
set @xml = concat(@xml,'</loyaltyRewards>')
]%%%%=v(@xml)=%%

Email Code

Take note of the Code Snippet Blocks’ ID as we will require this to retrieve the snippets via ContentBlockByID() function call. Replace “xxx” in the snippet below.

<!--%%[
SET @XML  = Trim(ContentBlockByID("243938"))
SET @XSLT = ContentBlockByID("242952")
SET @JSON = TransformXML(@XML, @XSLT)
]%%-->

<table border="1" width="600" cellspacing="0" cellpadding="15">
  <tr>
    <td width="600" style="width: 600px">
      <strong>JSON OUTPUT:</strong>
    </td>
  </tr>
  <tr>
    <td width="600" style="width: 600px">
      %%=v(@JSON)=%%
    </td>
  </tr>
</table>

Ensure to include the HTML comments <!-- --> wrapped around the top AMPscript block. If you remove these tags then SFMC may produce an error message that resembles the below.

Error Message when you omit the HTML Comment Tags Around the AMPscript block

When you run an email preview, the XML will be transformed per the rules in the XSL document to neatly convert the data to JSON.

We can also combine the code into a single self contained piece with the following:

function convertXMLToJSON(xml) {
    if (!xml) { 
        throw "[convertXMLToJSON] - Missing or wrong parameter";
    }

    var xml = xml.replace(/"/g, "'").replace(/'/g, "\''").replace(/&gt;/g, ">").replace(/&lt;/g, "<");
    
    var amp = "\%\%[ ";
        amp += "SET @amp__xml2json_xml = '"+xml+"' ";
        amp += "SET @amp__xml2json_xslt = '";
        amp += "<x";
        amp += "sl:stylesheet x";
        amp += "mlns:xsl=\"http://www.w3.org/1999/XSL/Transform\" version=\"1.0\"><x";
        amp += "sl:output method=\"text\" encoding=\"utf-8\" /><x";
        amp += "sl:template match=\"/node()\"><x";
        amp += "sl:text>{</x";
        amp += "sl:text><x";
        amp += "sl:apply-templates select=\".\" mode=\"detect\" /><x";
        amp += "sl:text>}</x";
        amp += "sl:text></x";
        amp += "sl:template><x";
        amp += "sl:template match=\"*\" mode=\"detect\"><x";
        amp += "sl:choose><x";
        amp += "sl:when test=\"name(preceding-sibling::*[1]) = name(current()) and name(following-sibling::*[1]) != name(current())\"><x";
        amp += "sl:apply-templates select=\".\" mode=\"obj-content\" /><x";
        amp += "sl:text>]</x";
        amp += "sl:text><x";
        amp += "sl:if test=\"count(following-sibling::*[name() != name(current())]) &gt; 0\">, </x";
        amp += "sl:if></x";
        amp += "sl:when><x";
        amp += "sl:when test=\"name(preceding-sibling::*[1]) = name(current())\"><x";
        amp += "sl:apply-templates select=\".\" mode=\"obj-content\" /><x";
        amp += "sl:if test=\"name(following-sibling::*) = name(current())\">, </x";
        amp += "sl:if></x";
        amp += "sl:when><x";
        amp += "sl:when test=\"following-sibling::*[1][name() = name(current())]\"><x";
        amp += "sl:text>\"</x";
        amp += "sl:text><x";
        amp += "sl:value-of select=\"name()\" /><x";
        amp += "sl:text>\" : [</x";
        amp += "sl:text><x";
        amp += "sl:apply-templates select=\".\" mode=\"obj-content\" /><x";
        amp += "sl:text>, </x";
        amp += "sl:text></x";
        amp += "sl:when><x";
        amp += "sl:when test=\"count(./child::*) &gt; 0 or count(@*) &gt; 0\"><x";
        amp += "sl:text>\"</x";
        amp += "sl:text><x";
        amp += "sl:value-of select=\"name()\" />\" :<x";
        amp += "sl:apply-templates select=\".\" mode=\"obj-content\" /><x";
        amp += "sl:if test=\"count(following-sibling::*) &gt; 0\">, </x";
        amp += "sl:if></x";
        amp += "sl:when><x";
        amp += "sl:when test=\"count(./child::*) = 0\"><x";
        amp += "sl:text>\"</x";
        amp += "sl:text><x";
        amp += "sl:value-of select=\"name()\" />\" : \"<x";
        amp += "sl:apply-templates select=\".\" /><x";
        amp += "sl:text>\"</x";
        amp += "sl:text><x";
        amp += "sl:if test=\"count(following-sibling::*) &gt; 0\">, </x";
        amp += "sl:if></x";
        amp += "sl:when></x";
        amp += "sl:choose></x";
        amp += "sl:template><x";
        amp += "sl:template match=\"*\" mode=\"obj-content\"><x";
        amp += "sl:text>{</x";
        amp += "sl:text><x";
        amp += "sl:apply-templates select=\"@*\" mode=\"attr\" /><x";
        amp += "sl:if test=\"count(@*) &gt; 0 and (count(child::*) &gt; 0 or text())\">, </x";
        amp += "sl:if><x";
        amp += "sl:apply-templates select=\"./*\" mode=\"detect\" /><x";
        amp += "sl:if test=\"count(child::*) = 0 and text() and not(@*)\"><x";
        amp += "sl:text>\"</x";
        amp += "sl:text><x";
        amp += "sl:value-of select=\"name()\" />\" : \"<x";
        amp += "sl:value-of select=\"text()\" /><x";
        amp += "sl:text>\"</x";
        amp += "sl:text></x";
        amp += "sl:if><x";
        amp += "sl:if test=\"count(child::*) = 0 and text() and @*\"><x";
        amp += "sl:text>\"text\" : \"</x";
        amp += "sl:text><x";
        amp += "sl:value-of select=\"text()\" /><x";
        amp += "sl:text>\"</x";
        amp += "sl:text></x";
        amp += "sl:if><x";
        amp += "sl:text>}</x";
        amp += "sl:text><x";
        amp += "sl:if test=\"position() &lt; last()\">, </x";
        amp += "sl:if></x";
        amp += "sl:template><x";
        amp += "sl:template match=\"@*\" mode=\"attr\"><x";
        amp += "sl:text>\"</x";
        amp += "sl:text><x";
        amp += "sl:value-of select=\"name()\" />\" : \"<x";
        amp += "sl:value-of select=\".\" /><x";
        amp += "sl:text>\"</x";
        amp += "sl:text><x";
        amp += "sl:if test=\"position() &lt; last()\">,</x";
        amp += "sl:if></x";
        amp += "sl:template><x";
        amp += "sl:template match=\"node/@TEXT | text()\" name=\"removeBreaks\"><x";
        amp += "sl:param name=\"pText\" select=\"normalize-space(.)\" /><x";
        amp += "sl:choose><x";
        amp += "sl:when test=\"not(contains($pText, '' ''))\"><x";
        amp += "sl:copy-of select=\"$pText\" /></x";
        amp += "sl:when><x";
        amp += "sl:otherwise><x";
        amp += "sl:value-of select=\"concat(substring-before($pText, '' ''), '' '')\" /><x";
        amp += "sl:call-template name=\"removeBreaks\"><x";
        amp += "sl:with-param name=\"pText\" select=\"substring-after($pText, '' '')\" /></x";
        amp += "sl:call-template></x";
        amp += "sl:otherwise></x";
        amp += "sl:choose></x";
        amp += "sl:template></x";
        amp += "sl:stylesheet>'";
        amp += "OUTPUT(TransformXML(@amp__xml2json_xml,@amp__xml2json_xslt))"
        amp += "]\%\%";
    
    return Platform.Function.ParseJSON(Platform.Function.TreatAsContent(amp));
}