Tuesday, September 14, 2021
Transform XML to CSV using XSLT in Oracle
You can create various transformation with SQL. You have to prepare XSLT transformation and use it.
SQL
SELECT
XMLTRANSFORM(xmltype(v_xml), v_482_xslt).GetClobVal() into v_csv
FROM dual;
XSLT transformation
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
<xsl:variable name="some_spaces" select="' '" />
<xsl:variable name="some_0s" select="'00000000000000000000'" />
<xsl:template match="/">
<!--xsl:value-of select="concat(substring-before(string(current-date()), '+'), '-', $timeNoMs, $timeMs)"/-->
<xsl:text> </xsl:text>
<xsl:for-each select="//FLAG_LIST/FLAG/DATA_LIST_REQ">
<xsl:text>DS 482</xsl:text>
<xsl:text>00</xsl:text>
<xsl:call-template name="pad0">
<xsl:with-param name="text" select="DATA[@NAME='AAAAAA']"/>
<xsl:with-param name="width" select="2"/>
</xsl:call-template>
<xsl:call-template name="pad">
<xsl:with-param name="text" select="../IX_LIST/IX[@NAME='BBBBB']"/>
<xsl:with-param name="width" select="31"/>
</xsl:call-template>
<xsl:call-template name="pad0">
<xsl:with-param name="text" select="DATA[@NAME='MMMMMM']"/>
<xsl:with-param name="width" select="100"/>
</xsl:call-template><xsl:call-template name="pad">
<xsl:with-param name="text" select="translate(DATA[@NAME='SSSSS'], 'ŠĐČĆŽšđčćž', 'SDCCZsdccz')"/>
<xsl:with-param name="width" select="25"/>
</xsl:call-template>
<xsl:text> </xsl:text>
</xsl:for-each>
<xsl:call-template name="pad0">
<xsl:with-param name="text" select="count(//FLAG_LIST/FLAG)"/>
<xsl:with-param name="width" select="6"/>
</xsl:call-template>
<xsl:text>
</xsl:template>
<xsl:template name="pad0">
<xsl:param name="text" />
<xsl:param name="width" />
<xsl:value-of select="substring(concat($some_0s, $text), 20+string-length($text)-$width+1, $width)"/>
</xsl:template>
<xsl:template name="pad">
<xsl:param name="text" />
<xsl:param name="width" />
<xsl:value-of select="substring(concat($text, $some_spaces), 1, $width)"/>
</xsl:template> </xsl:stylesheet>
Sample XML
<FLAG_LIST>
<FLAG>
<IX_LIST>
<IX NAE="BBBBB">IXVALUE1</IX>
</IX_LIST>
<DATA_LIST_REQ>
<DATA NAME="AAAAAA">AA1</DATA>
<DATA NAME="MMMMMM">MM1</DATA>
<DATA NAME="SSSSS">SS1</DATA>
</DATA_LIST_REQ>
</FLAG>
<FLAG>
<IX_LIST>
<IX NAE="BBBBB">IXVALUE2</IX>
</IX_LIST>
<DATA_LIST_REQ>
<DATA NAME="AAAAAA">AA2</DATA>
<DATA NAME="MMMMMM">MM2</DATA>
<DATA NAME="SSSSS">SS2</DATA>
</DATA_LIST_REQ>
</FLAG>
</FLAG_LIST>
# posted by Domen Dolar @ 11:11 PM 
