0

Usign XSLT we wish to transform the JSON to XML :

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:math="http://www.w3.org/2005/xpath-functions/math" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs math" version="3.0">
<xsl:output indent="yes" />
    <xsl:template match="data">
        <xsl:copy-of select="json-to-xml(.)" />
    </xsl:template>
</xsl:stylesheet>

We have follwing JSON output from Microsoft Dynamics 365 that we wish to convert to XML.

JSON :

<data>
{
    "@odata.context": "https:\/\/xxx.api.crm4.dynamics.com\/api\/data\/v9.0\/$metadata#msdyn_bookingjournals(msdyn_journaltype,msdyn_name,msdyn_starttime,msdyn_endtime,msdyn_duration,foc_sapstatisticalkeyfigurecod,statecode)",
    "value": [
    {
        "@odata.etag": "W\/\"3935842\"",
        "msdyn_journaltype@OData.Community.Display.V1.FormattedValue": "Travel",
        "msdyn_journaltype": 690970002,
        "msdyn_name": "Wartung  - Wartung - 101 Innener Verkehrsring\/D\u00fcsseldorferstr ",
        "msdyn_starttime@OData.Community.Display.V1.FormattedValue": "13.11.2018 13:11",
        "msdyn_starttime": "2018-11-13T13:11:58Z",
        "msdyn_endtime@OData.Community.Display.V1.FormattedValue": "13.11.2018 13:15",
        "msdyn_endtime": "2018-11-13T13:15:58Z",
        "msdyn_duration@OData.Community.Display.V1.FormattedValue": "4",
        "msdyn_duration": 4,
        "foc_sapstatisticalkeyfigurecod": "",
        "statecode@OData.Community.Display.V1.FormattedValue": "Active",
        "statecode": 0,
        "msdyn_bookingjournalid": "cb6d62ee-49e7-e811-a958-000d3a29fea4"
    },
    {
        "@odata.etag": "W\/\"3935846\"",
        "msdyn_journaltype@OData.Community.Display.V1.FormattedValue": "Working Hours",
        "msdyn_journaltype": 690970000,
        "msdyn_name": "Wartung  - Wartung - 101 Innener Verkehrsring\/D\u00fcsseldorferstr ",
        "msdyn_starttime@OData.Community.Display.V1.FormattedValue": "13.11.2018 13:15",
        "msdyn_starttime": "2018-11-13T13:15:58Z",
        "msdyn_endtime@OData.Community.Display.V1.FormattedValue": "13.11.2018 13:20",
        "msdyn_endtime": "2018-11-13T13:20:57Z",
        "msdyn_duration@OData.Community.Display.V1.FormattedValue": "4",
        "msdyn_duration": 4,
        "foc_sapstatisticalkeyfigurecod": "",
        "statecode@OData.Community.Display.V1.FormattedValue": "Active",
        "statecode": 0,
        "msdyn_bookingjournalid": "fe6d62ee-49e7-e811-a958-000d3a29fea4"
    }
    ],
    "@odata.nextLink": "https:\/\/xxx.api.crm4.dynamics.com\/api\/data\/v9.0\/bookableresourcebookings(b0fe5834-45e7-e811-a958-000d3a29fb7a)\/msdyn_bookableresourcebooking_msdyn_bookingjournal_Booking?$select=msdyn_journaltype,msdyn_name,msdyn_starttime,msdyn_endtime,msdyn_duration,foc_sapstatisticalkeyfigurecod,statecode&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%2520parentEntityId%253d%2522b0fe5834-45e7-e811-a958-000d3a29fb7a%2522%2520parentAttributeName%253d%2522msdyn_booking%2522%2520parentEntityObjectTypeCode%253d%25221145%2522%253e%253cmsdyn_bookingjournalid%2520last%253d%2522%257bFE6D62EE-49E7-E811-A958-000D3A29FEA4%257d%2522%2520first%253d%2522%257bCB6D62EE-49E7-E811-A958-000D3A29FEA4%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20\/%3E"
}
</data>

When testing this XSLT transformation on https://www.freeformatter.com/xsl-transformer.html, I get following error

Unable to generate the XML document using the provided XML/XSL input.
org.xml.sax.SAXParseException; lineNumber: 38; columnNumber: 325;
The entity name must immediately follow the '&' in the entity reference.
You most likely forgot to escape '&' into '&amp;'

Apparently this is due to the unescaped ampersand after statecode in

"@odata.nextLink":"...foc_sapstatisticalkeyfigurecod,statecode&$skiptoken="

Conversion works fine when replacing the & with the escaped value :

"@odata.nextLink":"...foc_sapstatisticalkeyfigurecod,statecode&amp;$skiptoken="

Is there a way to escape forbidden XML characters in the XSLT conversion from JSON to XML?

user2215655
  • 71
  • 8
  • 2
    Well, how do you try to generate that `data` element with the JSON content? You should fix that step to use a CDATA section or other proper means to make sure you create well-formed XML, otherwise you will have a hard time to use any XML tool or any XSLT version (didn't know there is version 4 now :)). Note that your snippet only works at https://xsltfiddle.liberty-development.net/6r5Gh2B after first fixing the JSON `value` property to be properly quoted, so the stuff you have there is not even following the JSON grammar. – Martin Honnen Jan 08 '19 at 17:31
  • Sorry for the XSLT 4.0 error. I corrected this in the original post. Microsoft Dynamics only returns the JSON output without elements, which I added manualIy after reading a similar json-to-xml xslt post online. – user2215655 Jan 08 '19 at 23:04
  • Your CDATA suggestion seems to do the trick. The way the transformation from JSON to XML is performed with json-to-xml(.), can you recommend this or is there a better way? I'm converting the JSON to XML as it's easier to parse XML in Groovy script which will be used for timestamp aggregations / calculations. – user2215655 Jan 08 '19 at 23:13
  • 1
    The function `json-to-xml` https://www.w3.org/TR/xpath-functions/#func-json-to-xml is part of XPath 3.1 as well so you don't need XML input at all but could just use XPath 3.1 or XQuery 3.1 or XSLT 3.0 evaluation with the JSON string passed in a an `xs:string` variable/parameter or read from a JSON text file with `unparsed-text`. That way you could avoid the step to ensure the JSON is properly escaped in your XSLT input. – Martin Honnen Jan 09 '19 at 11:28
  • On the other hand if you use XML tools to create the XML input containing the JSON and not string concatenation or simply copy/paste or whatever you used then the XML tools will take care of escaping the JSON properly. – Martin Honnen Jan 09 '19 at 11:28

2 Answers2

1

There is no XSLT version 4.0.

At some stage in your processing chain, you wrapped some JSON (or JSON-like) text in <data>...</data> tags without taking care to ensure that special characters in the JSON text were properly escaped.

In fact, wrapping the JSON in an XML wrapper isn't necessary. You can read the JSON input file directly using json-to-xml(unparsed-text('input.json')). You would then typically start the transformation by executing a named template (called, say, 'main' or 'xsl:initial-template') rather than by supplying a source XML document and using match="/".

Michael Kay
  • 156,231
  • 11
  • 92
  • 164
  • Sorry for the XSLT 4.0 error & missing quote in "value". I corrected this in the original post. Microsoft Dynamics only returns the JSON output without elements, which I added manualIy after reading a similar json-to-xml xslt post online. The CDATA suggestion seems to do the trick. I'm converting the JSON to XML as it's easier to parse XML in Groovy script, which will be used for timestamp aggregations / calculations and putting out a new XML file. – user2215655 Jan 08 '19 at 23:16
1

No, if you don't have well formed XML, there is not much you can do with XSLT.

First repair the XML, then do the XSLT processing. Options for fixing the XML are presented in another answer.

Thomas W
  • 14,757
  • 6
  • 48
  • 67