Oracle XMLTransform
The example shows the use of XMLTransform function to output table names as a comma seperated string. Since the number of rows is restricted to four, only four WIP tables are selected.

SELECT
  RTRIM(Extractvalue
   ( XMLTransform
    ( XMLElement("tables", XMLAgg(XMLElement( "table", TABLE_NAME))),
     XMLTYPE( '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
     <xsl:template match="tables">
     <result> <xsl:apply-templates/></result></xsl:template>
     <xsl:template match="table"> <xsl:apply-templates/>, </xsl:template>
     </xsl:stylesheet>')), '/result' ),', ') table_list
FROM all_tables
WHERE table_name Like 'WIP%'
AND ROWNUM < 5;


Back


Oracle registered trademark of Oracle Corporation.
Last Revised On: November 02, 2013

  2320