CSV to XML conversion with XSLT 2.0

Question

I have been trying to write an XSLT with 2.0 version to convert CSV data (which is again embedded in XML element) to XML.

The following is my sample CSV data

<csv>
"Id","Success","Created","Error"
"001P000000aXgRAIA0","true","true",""
"","false","false","REQUIRED_FIELD_MISSING:Required fields are missing: [Name, Man1__c, man2__c]:Name Man1__c man2__c --"
</csv>

Here the first row is header fields and for the above data my output XML should be like

<results xmlns = "http://www.force.com/2009/06/asyncapi/dataload">
    <result>
        <id>001D000000ISUr3IAH</id>
        <success>true</success>
        <created>true</created>
    </result>
    <result>
        <errors>
            <fields>Name</fields>
            <fields>Man1__c</fields>
            <fields>man2__c</fields>
            <message>Required fields are missing: [Name, Man1__c, man2__c]</message>
            <statusCode>REQUIRED_FIELD_MISSING</statusCode>
        </errors>
        <success>false</success>
        <created>false</created>
    </result>
</results>

and my conversion should have the following logic to perform transformation.

As you can see in the first record (not header row, actually second row in the csv data) the success value is true, hence the result will be populated as is, with id, success and created information.

For the second row, the success is false, hence there will be no id element in the result but errors should be populated. To populate the errors, the error field from CSV data should be parsed and first token of : (colon), should be put into statusCode element and last token of : (colon) should be put into fields (by parsing it with space separated) and rest middle string should be put into message field of errors.

I have searched sample XSLT here and could not get how to start with. Could you please help me start with sample XSLT so that I can develop in achieving this ?

Daniel Haley · Accepted Answer · 2013-05-27 20:03:44Z

This may not work 100% for all of your data, but it works for the example given and should give you a decent start.

XML Input

<csv>
"Id","Success","Created","Error"
"001P000000aXgRAIA0","true","true",""
"","false","false","REQUIRED_FIELD_MISSING:Required fields are missing: [Name, Man1__c, man2__c]:Name Man1__c man2__c --"
</csv>

XSLT 2.0

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="csv">
        <results xmlns="http://www.force.com/2009/06/asyncapi/dataload">
            <xsl:analyze-string select="." regex="(.*)\n">
                <xsl:matching-substring>
                    <xsl:if test="not(regex-group(1)='' or contains(regex-group(1),'Id'))">
                        <result>
                            <xsl:variable name="fields" as="element()">
                                <fields>
                                    <xsl:analyze-string select="regex-group(1)" regex="&quot;([^&quot;]*)&quot;,?">
                                        <xsl:matching-substring>
                                            <field>
                                                <xsl:value-of select="regex-group(1)"/>                                             
                                            </field>
                                        </xsl:matching-substring>
                                    </xsl:analyze-string>                                                                   
                                </fields>
                            </xsl:variable>
                            <xsl:choose>
                                <xsl:when test="$fields/*[2]='true'">
                                    <id><xsl:value-of select="$fields/*[1]"/></id>
                                    <success><xsl:value-of select="$fields/*[2]"/></success>
                                    <created><xsl:value-of select="$fields/*[3]"/></created>
                                </xsl:when>
                                <xsl:otherwise>
                                    <errors>
                                        <xsl:variable name="errorTokens" select="tokenize($fields/*[4],':')"/>
                                        <xsl:for-each select="tokenize(substring-before(substring-after($errorTokens[3],'['),']'),',')">
                                            <fields><xsl:value-of select="normalize-space(.)"/></fields>                                            
                                        </xsl:for-each>
                                        <message><xsl:value-of select="$errorTokens[not(position()=1) and not(position()=4)]" separator=":"/></message>
                                        <statusCode><xsl:value-of select="$errorTokens[1]"/></statusCode>
                                    </errors>
                                    <success><xsl:value-of select="$fields/*[2]"/></success>
                                    <created><xsl:value-of select="$fields/*[3]"/></created>
                                </xsl:otherwise>
                            </xsl:choose>
                        </result>
                    </xsl:if>
                </xsl:matching-substring>
            </xsl:analyze-string>
        </results>
    </xsl:template>

</xsl:stylesheet>

XML Output

<results xmlns="http://www.force.com/2009/06/asyncapi/dataload">
   <result>
      <id>001P000000aXgRAIA0</id>
      <success>true</success>
      <created>true</created>
   </result>
   <result>
      <errors>
         <fields>Name</fields>
         <fields>Man1__c</fields>
         <fields>man2__c</fields>
         <message>Required fields are missing: [Name, Man1__c, man2__c]</message>
         <statusCode>REQUIRED_FIELD_MISSING</statusCode>
      </errors>
      <success>false</success>
      <created>false</created>
   </result>
</results>

Hi Daniel Haley This is really cool. This works. I have modified it to make my different input work. Thanks for the help. Thanks to stackoverflow. With XSLT analyze-string and regex-group functions, I have become fan of XSLT. Its just awesome. Thanks for the reply. Full marks.

Collectives™ on Stack Overflow

CSV to XML conversion with XSLT 2.0

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related