Harsha Harsha - 2 months ago 19
MySQL Question

How to convert Schema XML to CSV using XSLT

The belwo is the xml from soapUI, I am facing diffulty to covnert this XML to CSV with the expected output bleow, I have tried multiple xsl but no hope. looking for xsl for this scenario.

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body>
<ReadResponse xmlns="http://abcde">
<ReadResult xmlns:a="http://abcde" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<a:Failures xmlns:b="http://abcde"/>
<a:GeneralErrorMessage i:nil="true"/>
<a:Successes xmlns:b="http://abcde">
<b:OpenSuiteStatus i:type="c:QueryStatus" xmlns:c="http://abcde">
<b:Code i:nil="true"/>
<b:ErrorMessage i:nil="true"/>
<b:SourceIndex>0</b:SourceIndex>
<c:Dto xmlns:d="http://abcde">
<d:ColumnNames xmlns:e="http://abcde/Arrays">
<e:string>code</e:string>
<e:string>period_number</e:string>
<e:string>period_start</e:string>
<e:string>period_finish</e:string>
</d:ColumnNames>
<d:ColumnTypes xmlns:e="http://abcde/Arrays">
<e:string>string</e:string>
<e:string>int</e:string>
<e:string>dateTime</e:string>
<e:string>dateTime</e:string>
</d:ColumnTypes>
<d:ParsedSqlQuery>Select * from Table</d:ParsedSqlQuery>
<d:QueryResults xmlns:e="http://abcde/Arrays">
<e:ArrayOfanyType>
<e:anyType i:type="f:string" xmlns:f="http://www.w3.org/2001/XMLSchema">ABC</e:anyType>
<e:anyType i:type="f:int" xmlns:f="http://www.w3.org/2001/XMLSchema">1</e:anyType>
<e:anyType i:type="f:dateTime" xmlns:f="http://www.w3.org/2001/XMLSchema">2012-12-30T00:00:00</e:anyType>
<e:anyType i:type="f:dateTime" xmlns:f="http://www.w3.org/2001/XMLSchema">2013-01-06T00:00:00</e:anyType>
</e:ArrayOfanyType>
<e:ArrayOfanyType>
<e:anyType i:type="f:string" xmlns:f="http://www.w3.org/2001/XMLSchema">DEF</e:anyType>
<e:anyType i:type="f:int" xmlns:f="http://www.w3.org/2001/XMLSchema">2</e:anyType>
<e:anyType i:type="f:dateTime" xmlns:f="http://www.w3.org/2001/XMLSchema">2013-01-06T00:00:00</e:anyType>
<e:anyType i:type="f:dateTime" xmlns:f="http://www.w3.org/2001/XMLSchema">2013-01-13T00:00:00</e:anyType>
</e:ArrayOfanyType>

</e:ArrayOfanyType>
</d:QueryResults>
<d:SqlQuery>Select * from Table</d:SqlQuery>
</c:Dto>
</b:OpenSuiteStatus>
</a:Successes>
<a:Warnings xmlns:b="http://abcde"/>
</ReadResult>
</ReadResponse>
</s:Body>
</s:Envelope>


Expected OUtput:



ABC,1,2013-01-06T00:00:00,2013-01-06T00:00:00

DEF,2,2013-01-06T00:00:00,2013-01-06T00:00:00

Deployed XSL: I have tried below but result is not as expected



<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="text" encoding="ISO-8859-1" />

<xsl:template match="/QueryResults">
<xsl:apply-templates select="ArrayOfanyType" />
</xsl:template>

<xsl:template match="ArrayOfanyType">
<xsl:apply-templates select="*" />
<xsl:if test="not(last())">
<xsl:value-of select="'&#10;'" />
</xsl:if>
</xsl:template>

<xsl:template match="ArrayOfanyType/*">
<xsl:value-of select="." />
<xsl:if test="not(last())">
<xsl:value-of select="','" />
</xsl:if>
</xsl:template>
</xsl:stylesheet>


Output:

ABC
1
2017-12-24T00:00:00
2017-12-31T00:00:00


DEF
2
2017-12-31T00:00:00
2018-01-07T00:00:00


Select * from Table

Answer

First thing you need to understand that a node in a namespace cannot be selected by its local name without a prefix. You must declare the same namespace in your stylesheet, assign it a prefix and use that prefix in your select and match expressions.

Next, the root element of your XML is s:Envelope - so a template matching /QueryResults will never be applied.

Now, the specified result can be achieved quite simply by:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:e="http://abcde/Arrays">
<xsl:output method="text" encoding="ISO-8859-1" />

<xsl:template match="/">
    <xsl:for-each select="//e:ArrayOfanyType">
        <xsl:for-each select="e:anyType">
            <xsl:value-of select="."/>
            <xsl:if test="position()!=last()">
                <xsl:text>,</xsl:text>
            </xsl:if>
        </xsl:for-each>
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

For better performance, replace the // part with the full path to e:ArrayOfanyType. But for this, you will need to add namespace declarations for all the namespaces used by the elements in that path.