xmlxsltxslt-1.0visual-studio-2015epicorerp

CSV text output from XSLT transformation gets garbled when openend in a third-party application


Run into a bit of an issue.

I'm using VS2015 to build up an XSLT that will be used in a 3rd party program. I've generated the sample data from the 3rd party program (Epicor Service Connect) into XML, and have built the XSLT based on that. Now when I debug the stylesheet in VS, I have the expected result - columns up top, seperated by semi-colons, and then each block of data is underneath, as expected.

However, when I run it through the Service Connect program, I get this complete mystery:

Weird symbols rather than data

I need to be able to return my data in a CSV using semi-colon's as separators. A snip of the data I get in VS shows that this works:

Working data

And of course, when put into a CSV, it shows the correct information.

XSLT for anyone curious (please be aware this is my 2nd day of using XSLT, prior to this I only knew what it was an abbreviation for - so it's not fantastic - but if you have suggestions on improvement, I happily accept constructive criticism):

<xsl:stylesheet version="1.0" xmlns:csv="csv:csv" xmlns:message="http://Epicor.com/Message/2.0"
                                                            xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                                            xmlns:ext_UserSchema="http://Epicor.com/SC/UserSchema">
<xsl:output method="text" encoding="utf-8" />
<xsl:strip-space elements="*" />

<xsl:template match="message:Receiver"/>

<xsl:template match="message:Body">
    <xsl:apply-templates select="message:Req"/>
</xsl:template>

<xsl:template match="message:Req">
    <xsl:apply-templates select="message:Dta"/>
</xsl:template>

<xsl:template match="message:Dta">
    <xsl:call-template name="PrimaryDataLoadForESC"/>
</xsl:template>

<xsl:template match="*">
    <xsl:variable name="tessst" select="local-name()"/>
    <xsl:choose>
        <xsl:when test="$tessst = 'QueryResultDataSet'">
            <xsl:call-template name="PrimaryDataLoadNotESC"/>
        </xsl:when>
        <xsl:otherwise>
            <xsl:apply-templates select="message:Body"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

<xsl:template name="PrimaryDataLoadNotESC">
    <xsl:apply-templates select="/QueryResultDataSet/Results[1]/*" mode="header"/>
    <xsl:apply-templates select="/QueryResultDataSet/Results" />
</xsl:template>

<xsl:template name="PrimaryDataLoadForESC">
    <xsl:apply-templates select="ext_UserSchema:QueryResultDataSet/ext_UserSchema:Results[1]/*" mode="header"/>
    <xsl:apply-templates select="ext_UserSchema:QueryResultDataSet/ext_UserSchema:Results" />
</xsl:template>

<xsl:template match="*" mode="header">
    <xsl:value-of select="translate(local-name(), '_', ' ')"/>
    <xsl:choose>
        <xsl:when test="position()=last()">
            <xsl:text>&#xD;</xsl:text>
        </xsl:when>
        <xsl:otherwise>;</xsl:otherwise>
    </xsl:choose>
</xsl:template>

<xsl:template match="QueryResultDataSet/Results">
    <xsl:apply-templates select="*" mode="dataNodes"/>
</xsl:template>

<xsl:template match="ext_UserSchema:QueryResultDataSet/ext_UserSchema:Results">
    <xsl:apply-templates select="*" mode="dataNodes"/>
</xsl:template>

<xsl:template match="*" mode="dataNodes">
    <xsl:value-of select="."/>
    <xsl:choose>
        <xsl:when test="position()=last()">
            <xsl:text>&#xD;</xsl:text>
        </xsl:when>
        <xsl:otherwise>;</xsl:otherwise>
    </xsl:choose>
</xsl:template>

</xsl:stylesheet>

Some sample data:

Before Service Connect (post-formatted):

<QueryResultDataSet>
<Results>
    <Source_-_System>SOURCE SYSTEM 1</Source_-_System>
    <Customer>96247</Customer>
    <Description_-_Short>COMPANY DESCRIPTION SHORT</Description_-_Short>
    <Description_-_Medium>COMPANY DESCRIPTION MEDIUM</Description_-_Medium>
    <Description_-_Long>COMPANY DESCRIPTION LONG</Description_-_Long>
</Results>

After Service Connect:

<?xml version="1.0" encoding="utf-16"?>
<msg:Msg xsi:schemaLocation="http://Epicor.com/Message/2.0 http://scshost/schemas/epicor/ScalaMessage.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:msg="http://Epicor.com/Message/2.0">
<msg:Hdr>
    <msg:Ctrl>
        <msg:MsgID></msg:MsgID>
    </msg:Ctrl>
    <msg:Sender>
        <msg:Name></msg:Name>
        <msg:Subname></msg:Subname>
    </msg:Sender>
    <msg:Logon></msg:Logon>
</msg:Hdr>
<msg:Body>
    <msg:Req msg-type="DocumentToProcess" action="MapAndProcess">
        <msg:Dta>
            <ext_UserSchema:QueryResultDataSet xmlns:msg="http://Epicor.com/InternalMessage/1.1" xmlns:ext_UserSchema="http://Epicor.com/SC/UserSchema">
                <ext_UserSchema:Results>
                    <ext_UserSchema:Source_System>SOURCE_SYS1</ext_UserSchema:Source_System>
                    <ext_UserSchema:Vendor>96247</ext_UserSchema:Vendor>
                    <ext_UserSchema:Description_-_Short>COMPANY DESCRIPTION SHORT</ext_UserSchema:Description_-_Short>
                    <ext_UserSchema:Description_-_Medium>COMPANY DESCRIPTION MEDIUM</ext_UserSchema:Description_-_Medium>
                    <ext_UserSchema:Description_-_Long>COMPANY DESCRIPTION LONG</ext_UserSchema:Description_-_Long>
                </ext_UserSchema:Results>
            </ext_UserSchema:QueryResultDataSet>
        </msg:Dta>
    </msg:Req>
</msg:Body>
</msg:Msg>

Anyone out there have any ideas as to what could be causing this issue?


Solution

  • I'm surprised I didn't spot this earlier:

    Your rendering goes wrong because of the byte order mark! The first character is the rendering in ISO-8859-1, CP1252 (windows) or Unicode of 0xFF 'ÿ' and the second 0xFE 'þ', which are the first and second byte in a byte order mark when using UTF-16 (order depending on endianness), or when using UTF-8 and you apply the UTF-8 decoding (the bytes being 0xEF, 0xBB 0xBF).

    So, in short, to solve this, change the xsl:output to contain:

    <xsl:output byte-order-mark="no" />
    

    But this will only work with XSLT 2.0 or up. If you cannot switch to XSLT 2.0, you should check the documentation of your processor if it supports a UTF-8 encoding without the byte-order-mark.

    At least at some point, the Saxon processor output a byte order mark when using text output with UTF-8. Also, on Windows, Notepad and many other editors automatically emit the byte-order mark when saving a file (if you post-edit your CSV by hand, this may happen, for instance).

    To resolve this either: