XSLT : How to create XSLT process Workday report - XML to JSON output - method="json" <xsl:output method="json" />

Question

Created new XSLT process using below XML DATA. When I use output method = "text" below XSLT code working but output method changed to (Method="json") XSLT process failing. my goal generate file/data as JSON.

Note: I am looking generic process. My XSLT code implemented as generic method and NO HARDCODED columns in XSLT code.

-- XML Data

<wd:Report_Data xmlns:wd="urn:com.workday.report/EntryReason">
<wd:Report_Entry>
<wd:EntryReasonRefID>ABC_123</wd:EntryReasonRefID>
<wd:EntryReason>ABC_123 Reimbursement - Non Taxable</wd:EntryReason>
<wd:CreatedMoment>2022-08-30T18:35:34.040-07:00</wd:CreatedMoment>
<wd:LastFunctionallyUpdated>2022-09-01T12:37:48.427-07:00</wd:LastFunctionallyUpdated>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:EntryReasonRefID>ABC_456</wd:EntryReasonRefID>
<wd:EntryReason>ABC_456 - Air Travel</wd:EntryReason>
<wd:CreatedMoment>2022-08-30T18:35:34.370-07:00</wd:CreatedMoment>
<wd:LastFunctionallyUpdated>2022-09-01T12:37:48.552-07:00</wd:LastFunctionallyUpdated>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:EntryReasonRefID>ABC_789</wd:EntryReasonRefID>
<wd:EntryReason>ABC_789 - Car Rental</wd:EntryReason>
<wd:CreatedMoment>2022-08-30T18:35:34.501-07:00</wd:CreatedMoment>
<wd:LastFunctionallyUpdated>2022-09-01T12:37:48.672-07:00</wd:LastFunctionallyUpdated>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:EntryReasonRefID>ABC_999</wd:EntryReasonRefID>
<wd:EntryReason>ABC_999 - Hotel</wd:EntryReason>
<wd:EntryReason>ABC_999 - Rental</wd:EntryReason>
<wd:CreatedMoment>2022-08-30T18:35:34.636-07:00</wd:CreatedMoment>
<wd:LastFunctionallyUpdated>2022-09-01T12:37:48.779-07:00</wd:LastFunctionallyUpdated>
</wd:Report_Entry>
</wd:Report_Data>

============================================ --- XSLT Code

<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:wd="urn:com.workday.report/EntryReason" version="3.0">
     <xsl:output method="json" indent="yes"/>
    <xsl:template match="/wd:Report_Data">
    <xsl:text>&#xA0;[&#xa;</xsl:text>
        <xsl:for-each select="wd:Report_Entry">
            <xsl:text>&#xA0;{&#xa;</xsl:text>
            
            <xsl:for-each select="./*"> 
                <xsl:text>&#xA0;</xsl:text>
                <xsl:choose> 
                    <xsl:when test="@wd:Descriptor"><xsl:value-of select="concat('&quot;',local-name(),'&quot;:&quot;',@wd:Descriptor,'&quot;')"/></xsl:when>
                    <xsl:otherwise><xsl:value-of select="concat('&quot;',local-name(),'&quot;:&quot;',.,'&quot;')"/></xsl:otherwise>
                </xsl:choose>
                <xsl:if test="position() != last()">
                    <xsl:text>,&#xa; </xsl:text>
                </xsl:if>
            </xsl:for-each>
             
            <xsl:text>&#xa; &#xA0;}</xsl:text>
            <xsl:if test="position() != last()">
                <xsl:text>,&#xa;</xsl:text>
            </xsl:if>
        </xsl:for-each>
        
        <xsl:text>&#xa;&#xA0;]</xsl:text>
    </xsl:template>
    <xsl:template match="node() | @*"/>
</xsl:stylesheet>

--Error Details :

Error executing XSLT at line 8 : JSON output method cannot handle sequences of two or more items

//Expected Result as output Method = json :

[
    {
        "EntryReasonRefID": "ABC_123 Reimbursement - Non Taxable",
        "EntryReason": "ABC_123",
        "CreatedMoment": "2022-08-30T18:35:34.040-07:00",
        "LastFunctionallyUpdated": "2022-09-01T12:37:48.427-07:00"
    },
    {
        "EntryReasonRefID": "ABC_456 - Air Travel",
        "EntryReason": "ABC_456",
        "CreatedMoment": "2022-08-30T18:35:34.040-07:00",
        "LastFunctionallyUpdated": "2022-09-01T12:37:48.427-07:00"
    },
    {
        "EntryReasonRefID": "ABC_789 - Car Rental",
        "EntryReason": "ABC_789",
        "CreatedMoment": "2022-08-30T18:35:34.040-07:00",
        "LastFunctionallyUpdated": "2022-09-01T12:37:48.427-07:00"
    },
    {
        "EntryReasonRefID": "ABC_999 - Hotel; ABC_999 - Rental",
        "EntryReason": "ABC_999",
        "CreatedMoment": "2022-08-30T18:35:34.040-07:00",
        "LastFunctionallyUpdated": "2022-09-01T12:37:48.427-07:00"
    }
]

What is the status of your previous question on the same topic: stackoverflow.com/q/75794497/3016153? — michael.hor257k
– michael.hor257k, Commented Mar 28, 2023 at 5:56

Conal Tuohy · Accepted Answer · 2023-03-29 02:54:13Z

1

You don't say what happens when you run your code, only that it's "failing". You really should report the error message you receive, because it can be helpful to people trying to help you.

I am guessing it fails because your stylesheet produces as output a sequence of text nodes, and tries to serialize that as JSON, which should produce a serialization error err:SERE0023 because a sequence of more than one item is not a valid JSON document.

To produce JSON output your stylesheet should do either one of these two things:

generate a sequence of text nodes which represent a serialized JSON object, and use the text output method to convert that sequence to a single string
generate a sequence of one data item which can be represented in JSON (i.e. a single string or number, a map, or an array), and use the json output method to serialize that data structure as JSON.

You have generated a plain text representation of a serialized JSON object (an array), but you've used the json output method instead of the text output method to serialize it. NB you haven't dealt with escaping text which might contain a " character.

An alternative and better approach would be generate an array of maps, and serialize that array using the json output method. There are various ways you could do that. One way would be to generate fn:array, fn:map, fn:string etc XML elements and then convert that data structure to JSON using the xml-to-json XPath function. In this case the conversion is simple enough that you can do the whole thing in a single XPath expression:

<xsl:stylesheet
    xmlns:map="http://www.w3.org/2005/xpath-functions/map"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:wd="urn:com.workday.report/EntryReason" version="3.0">
    <xsl:output method="json" indent="yes"/>
    <xsl:template match="/wd:Report_Data">
        <xsl:sequence select="
            array{
                for $entry in wd:Report_Entry return map:merge(
                    for $element in $entry/* return map{
                        local-name($element): 
                        string-join(
                          for $value in 
                            $entry/*[local-name()=local-name($element)] 
                          return
                            string($element),
                          '; '
                        )
                    }
                )
            }
        "/>
    </xsl:template>
</xsl:stylesheet>

The XPath expression in that xsl:sequence statement means:

Construct an array out of the following sequence:
- For each ReportEntry, return a map produced by merging the following maps:
  - For each element in the ReportEntry, return a map containing a single key/value pair where the key is this element's name, and the value is a concatenation of the values of all child elements of this ReportEntry which have this element's name

edited Mar 29, 2023 at 2:54

answered Mar 28, 2023 at 6:36

Conal Tuohy

3,3431 gold badge10 silver badges16 bronze badges

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

3 Comments

Mahari Over a year ago

Thank you so much support and explained. tested your code and worked!!! but below scenario missed, two "EntryReason" data but result populated only one dataset. Request you to please verify and help me on this. <wd:Report_Entry> <wd:EntryReasonRefID>ABC_999</wd:EntryReasonRefID> <wd:EntryReason>ABC_999 - Hotel</wd:EntryReason> <wd:EntryReason>ABC_999 - Rental</wd:EntryReason> <wd:CreatedMoment>2022-08-30T18:35:34.636-07:00</wd:CreatedMoment> <wd:LastFunctionallyUpdated>2022-09-01T12:37:48.779-07:00</wd:LastFunctionallyUpdated> </wd:Report_Entry>

Mahari Over a year ago

Thank you so much for hep. Is any possible to include below code choose method "@wd:Descriptor" data verification to validate Workday dataset? refer below Code <xsl:choose> <xsl:when test="@wd:Descriptor"><xsl:value-of select="concat('"',local-name(),'":"',@wd:Descriptor,'"')"/></xsl:when> <xsl:otherwise><xsl:value-of select="concat('"',local-name(),'":"',.,'"')"/></xsl:otherwise> </xsl:choose>

Conal Tuohy Over a year ago

Can you update your question to include an example of this @wd:Descriptor and how it should be formatted in the JSON?

sglmr · Accepted Answer · 2023-05-01 23:50:51Z

Since you are using a Workday RaaS for your data source, Workday will give you JSON if you just add ?format=json to the end of the URL.

There are also some additional options for CSV, Gdata, and RSS:

# XML (default)
https://services.workday.com/ccx/service/customreport2/tenant_name/owner/report_id

# JSON
https://services.workday.com/ccx/service/customreport2/tenant_name/owner/report_id?format=json

# CSV
https://services.workday.com/ccx/service/customreport2/tenant_name/owner/report_id?format=csv

# RSS
https://services.workday.com/ccx/service/customreport2/tenant_name/owner/report_id?format=rss

# Gdata
https://services.workday.com/ccx/service/customreport2/tenant_name/owner/report_id?format=gdata

Collectives™ on Stack Overflow

XSLT : How to create XSLT process Workday report - XML to JSON output - method="json" <xsl:output method="json" />

============================================ --- XSLT Code

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

============================================ --- XSLT Code

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related