Hi Team,
I am trying to parse below XSL xml with sql query but its is not giving expected outputs.
Could you please help on this.
XSL XML:
===========
<?xml version = '1.0' encoding = 'utf-8'?>
<!--Generated by Oracle BI Publisher 12.2.1.2.0-->
<!--xslt1.0-compatibility-->
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.xdo.template.rtf.XSLTFunctions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xlink="http://www.w3.org/1999/xlink">
<xsl:param name="_XDOCALENDAR">GREGORIAN</xsl:param>
<xsl:param name="_XDOLOCALE">en-us</xsl:param>
<xsl:param name="_XDOTIMEZONE">GMT</xsl:param>
<xsl:param name="_XDODFOVERRIDE">;</xsl:param>
<xsl:param name="_XDOCURMASKS">;</xsl:param>
<xsl:param name="_XDONFSEPARATORS"></xsl:param>
<xsl:param name="_XDOCHARTTYPE">image/svg+xml</xsl:param>
<xsl:param name="_XDOOUTPUTFORMAT">application/pdf</xsl:param>
<xsl:param name="_XDOSVGFONTEMBED">true</xsl:param>
<xsl:param name="_XDODEFDATE"/>
<xsl:param name="_XDODEFTIME"/>
<xsl:param name="_XDODEFNUM"/>
<xsl:param name="_XDODEFCC"/>
<xsl:param name="_XDOCTX">#</xsl:param>
<xsl:variable name="_XDOXSLTCTX" select="xdoxslt:set_xslt_locale($_XDOCTX, $_XDOLOCALE, $_XDOTIMEZONE, $_XDOCALENDAR, $_XDODFOVERRIDE, $_XDOCURMASKS, $_XDONFSEPARATORS)"/>
<xsl:variable name="titlevar" select="'RTF Template'" xdofo:tobetranslated="true"/>
<xsl:variable name="_XDOFOPOS" select="''"/>
<xsl:variable name="_XDOFOPOS2" select="number(1)"/>
<xsl:variable name="_XDOFOTOTAL" select="number(1)"/>
<xsl:variable name="_XDOFOOSTOTAL" select="number(0)"/>
<xsl:template match="/">
\<fo:root xdofo:nf-separator="{$\_XDONFSEPARATORS}">
\<fo:layout-master-set>
\<fo:simple-page-master master-name="master0" margin-left="30.6pt" margin-right="30.6pt" page-height="792.0pt" page-width="612.0pt" margin-top="36.0pt" margin-bottom="36.0pt">
\<fo:region-before region-name="region-header" extent="0.0pt"/>
\<fo:region-body region-name="region-body" margin-top="0.0pt" margin-bottom="0.0pt"/>
\<fo:region-after region-name="region-footer" extent="0.0pt" display-align="after"/>
\</fo:simple-page-master>
\</fo:layout-master-set>
\<fo:page-sequence master-reference="master0">
\<fo:title>
\<xsl:value-of select="xdoxslt:one($titlevar)" xdofo:field-name="$titlevar"/>
\</fo:title>
\<xsl:variable name="\_PW" select="number(612.0)" xdofo:alt="internal"/>
\<xsl:variable name="\_PH" select="number(792.0)" xdofo:alt="internal"/>
\<xsl:variable name="\_ML" select="number(36.0)" xdofo:alt="internal"/>
\<xsl:variable name="\_MR" select="number(36.0)" xdofo:alt="internal"/>
\<xsl:variable name="\_MT" select="number(36.0)" xdofo:alt="internal"/>
\<xsl:variable name="\_MB" select="number(36.0)" xdofo:alt="internal"/>
\<xsl:variable name="\_HY" select="number(36.0)" xdofo:alt="internal"/>
\<xsl:variable name="\_FY" select="number(36.0)" xdofo:alt="internal"/>
\<xsl:variable name="\_SECTION\_NAME" select="string('master0')" xdofo:alt="internal"/>
\<fo:static-content flow-name="region-header">
\<fo:block padding-bottom="0.25pt" padding-top="0.25pt">
\<fo:table start-indent="6.4pt" style-id="ts-1" xdofo:align="start" xdofo:table-summary="Template Table 1" xdofo:row-header-count="0">
\<xsl:variable name="\_XDOFOPOS2" select="number(1)"/>
\<xsl:variable name="\_XDOFOTOTAL" select="number(1)"/>
\<fo:table-column column-width="108.0pt"/>
\<fo:table-column column-width="288.0pt"/>
\<fo:table-column column-width="144.0pt"/>
\<fo:table-body>
\<fo:table-row>
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#ffffff" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start" height="0.0pt">
\<fo:instream-foreign-object content-type="image/png" width="69.75pt" height="10.05pt" xdofo:alt="An Image" xdofo:image-uid="">8wAAAABJRU5ErkJggg==
\</fo:instream-foreign-object>
\</fo:block>
\</fo:table-cell>
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#ffffff" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="center">
\<fo:inline color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman">Oracle BI Publisher\</fo:inline>
\</fo:block>
\</fo:table-cell>
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#ffffff" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start" height="0.0pt">
\<fo:inline xml:space="preserve" color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman">Date: \<xsl:value-of select="xdoxslt:format\_date(xdoxslt:current\_date($\_XDOLOCALE, $\_XDOTIMEZONE), 'yyyy-MM-dd', 'yyyy-MM-dd', $\_XDOLOCALE, $\_XDOTIMEZONE)" xdofo:field-name="xdoxslt:format\_date(xdoxslt:current\_date($\_XDOLOCALE,$\_XDOTIMEZONE),'yyyy-MM-dd','yyyy-MM-dd',$\_XDOLOCALE,$\_XDOTIMEZONE)"/> \<xsl:value-of select="xdoxslt:current\_time($\_XDOLOCALE, $\_XDOTIMEZONE)" xdofo:field-name="xdoxslt:current\_time($\_XDOLOCALE,$\_XDOTIMEZONE)"/>\</fo:inline>
\</fo:block>
\<fo:block padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start" height="0.0pt">
\<fo:inline xml:space="preserve" color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman">Page \<fo:page-number/> Of \<fo:page-number-citation ref-id="xdofo:lastpage-joinseq"/>\</fo:inline>
\</fo:block>
\</fo:table-cell>
\</fo:table-row>
\</fo:table-body>
\</fo:table>
\</fo:block>
\<fo:block/>
\</fo:static-content>
\<fo:static-content flow-name="region-footer"/>
\<fo:flow flow-name="region-body">
\<xsl:variable name="\_XDOFOOSTOTAL" select="(number(concat('0', $\_XDOFOPOS2)) - 1) \* number(concat('0', $\_XDOFOTOTAL))"/>
\<xsl:for-each select=".//G\_1">
\<xsl:value-of select="xdoxslt:chkPrcCancel($\_XDOXSLTCTX)"/>
\<xsl:variable name="\_XDOFOPOS" select="concat($\_XDOFOPOS, '\_', position())"/>
\<xsl:variable name="\_XDOFOPOS2" select="number(concat('0', $\_XDOFOOSTOTAL)) + position()"/>
\<fo:block text-align="start" padding-bottom="0.25pt" end-indent="5.4pt" padding-top="0.25pt" linefeed-treatment="preserve">
\<fo:inline style-id="s0" font-size="10.0pt" height="11.56pt" style-name="Normal" white-space-collapse="false" font-family="Arial" font-family-generic="roman"> \</fo:inline>
\<fo:table start-indent="5.4pt" style-id="ts-1" xdofo:align="start" xdofo:table-summary="Template Table 2" xdofo:row-header-count="0">
\<xsl:variable name="\_XDOFOPOS2" select="number(1)"/>
\<xsl:variable name="\_XDOFOTOTAL" select="number(1)"/>
\<fo:table-column column-width="540.0pt"/>
\<fo:table-body>
\<fo:table-row>
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#b9d1e3" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start">
\<fo:inline color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman">DEPTNO\</fo:inline>
\</fo:block>
\</fo:table-cell>
\</fo:table-row>
\<fo:table-row>
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#ffffff" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block xdofo:xliff-note="DEPTNO" padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start" height="0.0pt">
\<fo:inline color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman">
\<xsl:value-of select="(.//DEPTNO)\[1\]" xdofo:field-name="DEPTNO"/>
\</fo:inline>
\</fo:block>
\</fo:table-cell>
\</fo:table-row>
\</fo:table-body>
\</fo:table>
\</fo:block>
\<fo:block text-align="start" padding-bottom="0.25pt" end-indent="5.4pt" padding-top="0.25pt" linefeed-treatment="preserve">
\<fo:inline style-id="s0" font-size="10.0pt" height="11.56pt" style-name="Normal" white-space-collapse="false" font-family="Arial" font-family-generic="roman"> \</fo:inline>
\<fo:table start-indent="5.4pt" style-id="ts-1" xdofo:align="start" xdofo:table-summary="Template Table 3" xdofo:row-header-count="0">
\<xsl:variable name="\_XDOFOPOS2" select="number(1)"/>
\<xsl:variable name="\_XDOFOTOTAL" select="number(1)"/>
\<fo:table-column column-width="270.0pt"/>
\<fo:table-column column-width="270.0pt"/>
\<fo:table-body>
\<fo:table-row>
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#f5fafd" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start">
\<fo:inline color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman">EMP\_ID\</fo:inline>
\</fo:block>
\</fo:table-cell>
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#f5fafd" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start">
\<fo:inline color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman">ENAME\</fo:inline>
\</fo:block>
\</fo:table-cell>
\</fo:table-row>
\<xsl:variable name="\_XDOFOOSTOTAL" select="(number(concat('0', $\_XDOFOPOS2)) - 1) \* number(concat('0', $\_XDOFOTOTAL))"/>
\<xsl:variable name="\_XDOFOTOTAL" select="count(.//G\_2)"/>
\<xsl:for-each select=".//G\_2" xdofo:ctx="3">
\<xsl:value-of select="xdoxslt:chkPrcCancel($\_XDOXSLTCTX)"/>
\<xsl:variable name="\_XDOFOPOS" select="concat($\_XDOFOPOS, '\_', position())"/>
\<xsl:variable name="\_XDOFOPOS2" select="number(concat('0', $\_XDOFOOSTOTAL)) + position()"/>
\<fo:table-row xdofo:repeat="R">
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#ffffff" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block xdofo:xliff-note="G, EMP\_ID" padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start" height="0pt">
\<fo:inline xml:space="preserve" color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman"> \<xsl:value-of select="(.//EMP\_ID)\[1\]" xdofo:field-name="EMP\_ID"/>\</fo:inline>
\</fo:block>
\</fo:table-cell>
\<fo:table-cell vertical-align="top" border-start-color="#dddddd" background-color="#ffffff" height="0.0pt" padding-start="0.0pt" border-start-style="solid" border-start-width="0.5pt" border-bottom="0.5pt solid #dddddd" padding-top="0.0pt" padding-end="0.0pt" number-columns-spanned="1" border-top="0.5pt solid #dddddd" border-end-color="#dddddd" border-end-style="solid" padding-bottom="0.0pt" border-end-width="0.5pt">
\<fo:block xdofo:xliff-note="ENAME, E" padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="0.0pt" start-indent="0.0pt" text-align="start" height="0pt">
\<fo:inline xml:space="preserve" color="#000000" font-size="9.0pt" style-name="Normal" height="9.0pt" white-space-collapse="false" font-family="Arial" font-family-generic="roman">\<xsl:value-of select="(.//ENAME)\[1\]" xdofo:field-name="ENAME"/> \</fo:inline>
\</fo:block>
\</fo:table-cell>
\</fo:table-row>
\</xsl:for-each>
\</fo:table-body>
\</fo:table>
\</fo:block>
\<fo:block padding-top="0.0pt" linefeed-treatment="preserve" padding-bottom="0.0pt" end-indent="5.4pt" start-indent="5.4pt" text-align="start">
\<fo:inline xml:space="preserve" style-id="s0" font-size="9.0pt" height="10.404pt" style-name="Normal" white-space-collapse="false" font-family="Arial" font-family-generic="roman" color="#000000"> \</fo:inline>
\</fo:block>
\</xsl:for-each>
\<fo:block height="0pt">
\<fo:inline id="{concat('page-total-', $\_SECTION\_NAME, $\_XDOFOPOS)}"/>
\<fo:inline id="{concat('page-total', $\_XDOFOPOS)}"/>
\</fo:block>
\</fo:flow>
\</fo:page-sequence>
\</fo:root>
</xsl:template>
</xsl:stylesheet>
SQL Query 1:
============
To parse below xsl XML
Select Display_Name, Display_Value
From Data_Xml T,
XMLTable(XMLNamespaces(default '[http://www.w3.org/1999/XSL/Transform](http://www.w3.org/1999/XSL/Transform)',
'[http://www.w3.org/1999/XSL/Transform](http://www.w3.org/1999/XSL/Transform)' as "xsl",
'[http://www.w3.org/1999/XSL/Format](http://www.w3.org/1999/XSL/Format)' as "fo"),
'for $i in /stylesheet/template/root/page-sequence/flow/for-each/block/table/table-body/table-row/table-cell/block,
$j in $i/inline/value-of
return element r {
$i
}'
Passing T.Doc\_Xml
Columns
Display\_Name Varchar2(200) path 'block/inline',
Display\_Value Varchar2(200) path 'value-of/select') X
Expected Output:
==============
SQL Query 2:
============
To parse below xsl XML
Select Display_Name, Display_Value
From Data_Xml T,
XMLTable(XMLNamespaces(default '[http://www.w3.org/1999/XSL/Transform](http://www.w3.org/1999/XSL/Transform)',
'[http://www.w3.org/1999/XSL/Transform](http://www.w3.org/1999/XSL/Transform)' as "xsl",
'[http://www.w3.org/1999/XSL/Format](http://www.w3.org/1999/XSL/Format)' as "fo"),
'for $i in /stylesheet/template/root/page-sequence/flow/for-each/block/table/table-body/table-row/table-cell/block,
$j in $i/inline/value-of
return element r {
$i
}'
Passing T.Doc\_Xml
Columns
Display\_Name Varchar2(200) path 'block/inline',
Display\_Value Varchar2(200) path 'value-of/select') X
Expected Output:
==============
Thanks & Regards,
Sridhar.V