Oracle XML - extractValue()
-- The query below uses extractValue() with other Oracle XML functions to output a ", "
-- separated list of values.  The values used in XMLElement should match the values 
-- used in the stylesheet definition.

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,
 RTRIM(extractValue(XMLTransform(XMLElement("rows",
 XMLAgg(XMLElement("row_count", NUM_ROWS))), 
  XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:template match="rows">
          <result><xsl:apply-templates/></result></xsl:template>
           <xsl:template match="row_count">
           <xsl:apply-templates/>, </xsl:template>
          </xsl:stylesheet>')),
          '/result' ),', ') row_counts
FROM all_tables
WHERE ROWNUM <= 4;

Oracle XML - extractValue() Output

Oracle XMLAgg

Oracle registered trademark of Oracle Corporation.

Last Revised On: August 29, 2014

  7617