SQL/XML and XPath

Oracle has been criticised for many things (some warranted, some unwarranted), but if I had a personal gripe against it, then it was the way that the generation of XML from a SQL query was handled. Basically, prior to 9i, the only way of generating XML was to utilise dynamic SQL within built-in package(s), such as DBMS_XMLGEN, DBMS_XMLQUERY or DBMS_XMLDOM, and build up complex object views using specially-crafted object types, except in the case of the simplest of XML hierarchies.

With the advent of 9i, and the XMLTYPE native datatype, Oracle has started to support the emergent ISO standard, SQL/XML (see www.sqlx.org for further information). The first edition of the SQL/XML standard has ISO reference of ISO/IEC 9075-14:2003 (part 14 of the SQL:2003 standard).

SQL/XML consists of a series of built-in functions which allow a static definition of the XML hierarchy. One of the powers of SQL/XML, of course, is that it is static SQL, not dynamic SQL like in the DBMS_XML* packages, so we get a significant reduction in the parsing associated with dynamic SQL.

However, you have to be aware that SQL/XML is SQL driven, and not procedural like DBMS_XML*, so, you'll find your life a lot easier if your XML is already nicely nested and has a relatively easy mapping to your relational schema. I've also put some basic notes about the general performance characteristics of SQL/XML compared with other XML generation mechanisms (such as DBMS_XMLGEN etc.) which may be surprising.

This document also covers some of the basics of using XPath extracts to get data out of your XML documents and process it whichever way you see fit.

Generating XML using SQL/XML

XMLELEMENT

XMLELEMENT is the "core" function within SQL/XML. It allows definition of an XML element, surprise surprise. The basic syntax is XMLELEMENT("tag_name", element_value), i.e. 
SQL> SELECT XMLELEMENT("test", dummy)
  2    FROM dual;

XMLELEMENT("TEST",DUMMY)
-------------------------------------
<test>X</test>
Note, that to create an XML hierarchy, XMLELEMENT definitions can be specified as the value parameter of XMLELEMENT, i.e. 
SQL> SELECT XMLELEMENT("test", XMLELEMENT("test2", dummy))
  2    FROM dual;

XMLELEMENT("TEST",XMLELEMENT("TEST2",DUMMY))
-----------------------------------------------------------
<test>
  <test2>X</test2>
</test>
Sometimes, it is required that an XML element's value contains XML elements within a character string, such as the following, perfectly valid, XML fragment : 
<test>This is a <tag>value</tag> and has a value</test>
Is usually approached (incorrectly) by most people as 
SQL> SELECT XMLELEMENT("test", 'This is a ' || XMLELEMENT("tag", 'value') || ' and has a value')
  2    FROM dual;

XMLELEMENT("TEST",'THISISA'||XMLELEMENT("TAG",'VALUE')||'ANDHASAVALUE')
--------------------------------------------------------------------------------
<test>This is a &lt;tag&gt;value&lt;/tag&gt; and has a value</test>
The problem with this is that any string within an XMLELEMENT is considered a encodable string, and as such, any characters which are reserved XML characters, such as <, >, & etc., are encoded to &lt;, &gt; and &amp; respectively.
In order to correctly place actual XML tags within the string, then you need to use a form of XMLELEMENT which specifies the relevant strings and XMLELEMENT calls as parameters, not as just a big string, i.e. 
SQL> SELECT XMLELEMENT("test",
  2                    'This is a ',
  3                    XMLELEMENT("tag", 'value'),
  4                    ' and has a value')
  5    FROM dual;

XMLELEMENT("TEST",'THISISA',XMLELEMENT("TAG",'VALUE'),'ANDHASAVALUE')
--------------------------------------------------------------------------------
<test>This is a <tag>value</tag> and has a value</test>
Of course, this can be carried on indefinitely, i.e. : 
SQL> SELECT XMLELEMENT("test",
  2                    'This is a ',
  3                    XMLELEMENT("tag", 'value'),
  4                    ' and has a value of ',
  5                    XMLELEMENT("value", 'value'),
  6                    ' etc. etc.')
  7    FROM dual;

XMLELEMENT("TEST",'THISISA',XMLELEMENT("TAG",'VALUE'),'ANDHASAVALUEOF',XMLELEMENT("VALUE",'VALUE'),'ETC.ETC')
--------------------------------------------------------------------------------------------------------------

<test>This is a <tag>value</tag> and has a value of <value>value</value> etc. etc.</test>

XMLATTRIBUTES

XMLATTRIBUTES is the SQL/XML function which allows the definition of XML tag attributes. It is specified as a parameter of XMLELEMENT using the following syntax, XMLELEMENT("tag_name", XMLATTRIBUTES(attribute_value "attribute_name"), element_value), i.e. 
SQL> SELECT XMLELEMENT("test", XMLATTRIBUTES(1 "test_attribute"), dummy)
  2    FROM dual;

XMLELEMENT("TEST",XMLATTRIBUTES(1"TEST_ATTRIBUTE"),DUMMY)
-------------------------------------------------------------------------
<test test_attribute="1">X</test>

XMLFOREST

XMLFOREST (which, apparently, is named because it's a collection of XML "trees" (or nodes), although I don't know how true that is! ;-)) is a shorthand mechanism for generating multiple XMLELEMENTs (well, sort of, see the end of the section for a slight difference!).

The basic syntax is XMLFOREST(value1 "alias", value2 "alias",...), it can be specified on it's own or as the value parameter of XMLELEMENT, which creates an XML hierarchy, i.e. 
SQL> SELECT XMLFOREST(1 "test", 2 "test2") from dual;

XMLFOREST(1"TEST",2"TEST2")
-------------------------------------------------------
<test>1</test>
<test2>2</test2>

SQL> SELECT XMLELEMENT("test", XMLFOREST(1 "test1", 2 "test2")) FROM dual;

XMLELEMENT("TEST",XMLFOREST(1"TEST1",2"TEST2"))
---------------------------------------------------------------------------
<test>
  <test1>1</test1>
  <test2>2</test2>
</test>
Note, you cannot specify XML element attributes against an entry in an XMLFOREST, therefore, if necessary, you have to replace the forest with a series of XMLELEMENT tags, i.e. 
SQL> SELECT XMLELEMENT("test", XMLELEMENT("test2", XMLATTRIBUTES(1 "test2_attribute")),
  2                            XMLELEMENT("test3", XMLATTRIBUTES(2 "test3_attribute")))
  3  FROM dual;

XMLELEMENT("TEST",XMLELEMENT("TEST2",XMLATTRIBUTES(1"TEST2_ATTRIBUTE")),XMLELEMENT("TEST3
-----------------------------------------------------------------------------------------
<test>
  <test2 test2_attribute="1"/>
  <test3 test3_attribute="2"/>
</test>
Note, that there is a distinct difference between multiple XMLELEMENT calls and XMLFOREST, specifically in the way that NULL element values are handled. Basically, XMLFOREST will NOT generate an XML element if the value of the forest element is NULL, i.e. compare the following "equivalent" statements : 
SQL> SELECT XMLELEMENT("test", XMLELEMENT("test2", NULL),
  2                            XMLELEMENT("test3", NULL))
  3    FROM dual;

XMLELEMENT("TEST",XMLELEMENT("TEST2",NULL),XMLELEMENT("TEST3",NULL))
----------------------------------------------------------------------
<test><test2></test2><test3></test3></test>

SQL> SELECT XMLELEMENT("test", XMLFOREST(NULL "test2",
  2                                      NULL "test3"))
  3    FROM dual;

XMLELEMENT("TEST",XMLFOREST(NULL"TEST2",NULL"TEST3"))
----------------------------------------------------------------------
<test></test>
For a further, more thorough discussion on the handling of NULL elements, see here.

XMLAGG

The basic functions described above operate in a static manner, i.e. the number of subelements needs to be known in advance. This is, probably in the vast majority of cases, not going to meet the requirements, and will be impossible or impractical to do. Therefore, there needs to be the ability to define the number of subelements to be driven from the results of a query. This is where XMLAGG comes in.

XMLAGG is an aggregate function which aggregates the results of multiple XML documents into a single document which, importantly, can itself be passed as a parameter to XMLELEMENT, i.e. 
SQL> SELECT XMLELEMENT("test", ( SELECT XMLAGG(XMLELEMENT("test2", a)) FROM t ))
  2  FROM dual;

XMLELEMENT("TEST",(SELECTXMLAGG(XMLELEMENT("TEST2",A))FROMT))
----------------------------------------------------------------------------------
<test>
  <test2>3</test2>
  <test2>2</test2>
  <test2>1</test2>
  <test2>5</test2>
  <test2>4</test2>
</test>
Note, that XMLAGG is an aggregate function just like any other, and requires GROUP BY if not the only term in the SELECT. 
SQL> select dummy, xmlagg(xmlelement("test", dummy))
  2  FROM DUAL
  3  GROUP BY dummy;

D
-
XMLAGG(XMLELEMENT("TEST",DUMMY))
------------------------------------------------------
X
<test>X</test>

1 row selected.
You can specify an optional ORDER BY clause to XMLAGG, for example: 
SQL> SELECT XMLELEMENT("test", ( SELECT XMLAGG(XMLELEMENT("test2", a) ORDER BY a) FROM t ))
  2  FROM dual;

XMLELEMENT("TEST",(SELECTXMLAGG(XMLELEMENT("TEST2",A))FROMT))
----------------------------------------------------------------------------------
<test>
  <test2>1</test2>
  <test2>2</test2>
  <test2>3</test2>
  <test2>4</test2>
  <test2>5</test2>
</test>
Note, also, that the use of XMLAGG by itself is one of the few situations where you can generate an invalid XML document, since by definition every XML document must have one (and only one) root element, i.e. : 
SQL> SELECT XMLAGG(XMLELEMENT("dummy", dummy))
  2    FROM
  3      ( SELECT dummy FROM dual UNION ALL SELECT dummy FROM dual );

XMLAGG(XMLELEMENT("DUMMY",DUMMY))
-------------------------------------------------------------------------
<dummy>X</dummy><dummy>X</dummy>

1 row selected.
Which is, technically, an invalid XML document (no root element), i.e. 
SQL> SELECT XMLTYPE.CreateXML('<dummy>X</dummy><dummy>X</dummy>')
  2    FROM dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing

LPX-00245: extra data after end of document
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 54
ORA-06512: at line 1
Note, though, this is not a bug, it is by design. XMLAGG is intended to generate XML fragments, not documents.

For more information on the use of SQL/XML to generate invalid XML documents, see here.

XMLCONCAT

It is sometimes required to merge the output of two or more XML fragments, to create a single XML fragment. 
SQL> SELECT XMLCONCAT(XMLELEMENT("test", dummy), XMLELEMENT("test", dummy))
  2  FROM dual;

XMLCONCAT(XMLELEMENT("TEST",DUMMY),XMLELEMENT("TEST",DUMMY))
----------------------------------------------------------------------------
<test>X</test>
<test>X</test>

1 row selected.

UPDATEXML

What about if we actually want to modify an XMLTYPE instance? We can see how to generate it, but what tools do we have for actually modifying the values?

UPDATEXML allows us to do just this. Basically, it allows us to define a set of XPath expressions and values as sets of name / value pairs. The XPath expressions point to the element or attribute that you want to modify to the specified value. The best mechanism for explaining this is via an example.

Let's say we have an XML document thus : 
<DEPT>
  <EMPID>1</EMPID>
  <EMPNAME>Martin Chadderton</EMPNAME>
  <SALARY>1000</SALARY>
</DEPT>
Now, let's say that we need to modify the SALARY value upping it to 1100 (it's been a good year! ;-)). We use an XPath expression using the text() function to return the value of the element in question, and then specify the value to update it to, i.e. 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3                         <EMPID>1</EMPID>
  4                         <EMPNAME>Martin Chadderton</EMPNAME>
  5                         <SALARY>1000</SALARY>
  6                       </DEPT>'),
  7    '/DEPT/SALARY/text()', '1100')
  8  FROM dual
  9  /

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>MART
------------------------------------------------------
<DEPT>
  <EMPID>1</EMPID>
  <EMPNAME>Martin Chadderton</EMPNAME>
  <SALARY>1100</SALARY>
</DEPT>
It is important to note at this point, that due to Oracle bug 2962474, trying to do a text()-based update on an element that is NULL will result in no change, i.e. 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3  <EMPID>1</EMPID>
  4    <EMPNAME>Martin Chadderton</EMPNAME>
  5    <SALARY />
  6  </DEPT>'),
  7    '/DEPT/SALARY/text()', '1100')
  8  FROM dual
  9  /

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>
--------------------------------------------------
<DEPT>
  <EMPID>1</EMPID>
  <EMPNAME>Martin Chadderton</EMPNAME>
  <SALARY/>
</DEPT>
In this situation, you need to override the whole tag (I go into depth on this syntax a bit later in this article) : 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3  <EMPID>1</EMPID>
  4    <EMPNAME>Martin Chadderton</EMPNAME>
  5    <SALARY />
  6  </DEPT>'),
  7    '/DEPT/SALARY', XMLTYPE('<SALARY>1100</SALARY>'))
  8  FROM dual;

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>MARTINCHADDERT
----------------------------------------------------------------
<DEPT>
  <EMPID>1</EMPID>
  <EMPNAME>Martin Chadderton</EMPNAME>
  <SALARY>1100</SALARY>
</DEPT>
Updating an attribute just requires us to use the @ XPath attribute syntax, i.e. 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3                         <EMPID>1</EMPID>
  4                         <EMPNAME>Martin Chadderton</EMPNAME>
  5                         <SALARY taxable="yes">1000</SALARY>
  6                       </DEPT>'),
  7    '/DEPT/SALARY/@taxable', 'no')
  8  FROM dual
  9  /

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>MARTINCHADDERTON</EMPNAME><SAL
--------------------------------------------------------------------------------
<DEPT>
  <EMPID>1</EMPID>
  <EMPNAME>Martin Chadderton</EMPNAME>
  <SALARY taxable="no">1000</SALARY>
</DEPT>
Of course, we can go a lot further than this. UPDATEXML has the syntax 
UPDATEXML(xmltype_instance, xpath_expression, value_expr, namespace_expr)
So, we can, for example, specify an XPath expression which points to an XML fragment, and then update the whole fragment to a new fragment defined within an XMLTYPE instance, i.e. 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3                         <EMPID>1</EMPID>
  4                         <EMPNAME>Martin Chadderton</EMPNAME>
  5                         <SALARY>1000</SALARY>
  6                       </DEPT>'),
  7    '/DEPT/SALARY', XMLTYPE('<NEW_SALARY>1100</NEW_SALARY>'))
  8  FROM dual;

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>MARTINCHADDERTON
------------------------------------------------------------------
<DEPT>
  <EMPID>1</EMPID>
  <EMPNAME>Martin Chadderton</EMPNAME>
  <NEW_SALARY>1100</NEW_SALARY>
</DEPT>
Updating the value to NULL does what you would expect, i.e. the element remains, but is a null tag, i.e. 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3                         <EMPID>1</EMPID>
  4                         <EMPNAME>Martin Chadderton</EMPNAME>
  5                         <SALARY>1000</SALARY>
  6                       </DEPT>'),
  7    '/DEPT/SALARY/text()', NULL)
  8  FROM dual
  9  /

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>MARTINCHADDE
--------------------------------------------------------------
<DEPT>
  <EMPID>1</EMPID>
  <EMPNAME>Martin Chadderton</EMPNAME>
  <SALARY></SALARY>
</DEPT>
Updating the parent tag to NULL has the effect of removing all child elements, leaving you with a NULL parent tag, i.e. 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3                         <EMPID>1</EMPID>
  4                         <EMPNAME>Martin Chadderton</EMPNAME>
  5                         <SALARY taxable="yes">1000</SALARY>
  6                       </DEPT>'),
  7    '/DEPT', NULL)
  8  FROM dual
  9  /

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>MARTINCHADDERTON</EMPNAME><SAL
--------------------------------------------------------------------------------
<DEPT/>
So, what about a quick way of removing the values of all tags under a parent? Well, we're into the bowels of XPath here, but we use the XPath "wildcard" operator for all child tags, i.e. 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3                         <EMPID>1</EMPID>
  4                         <EMPNAME>Martin Chadderton</EMPNAME>
  5                         <SALARY>1000</SALARY>
  6                       </DEPT>'),
  7  '/DEPT//*', NULL)
  8  FROM dual
  9  /

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>MAR
-----------------------------------------------------
<DEPT>
  <EMPID/>
  <EMPNAME/>
  <SALARY/>
</DEPT>
Which is equivalent to : 
SQL> SELECT
  2    UPDATEXML(XMLTYPE('<DEPT>
  3  <EMPID>1</EMPID>
  4    <EMPNAME>Martin Chadderton</EMPNAME>
  5    <SALARY>1000</SALARY>
  6  </DEPT>'),
  7    '/DEPT/EMPID/text()', NULL,
  8    '/DEPT/EMPNAME/text()', NULL,
  9    '/DEPT/SALARY/text()', NULL)
 10  FROM dual;

UPDATEXML(XMLTYPE('<DEPT><EMPID>1</EMPID><EMPNAME>MARTINCH
----------------------------------------------------------
<DEPT>
  <EMPID></EMPID>
  <EMPNAME></EMPNAME>
  <SALARY></SALARY>
</DEPT>
So, as we can see, UPDATEXML is VERY powerful, and makes the modification of XML documents efficient and easy, especially when combined with some of the more "advanced" XPath expressions.

For further information on UPDATEXML, see http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/functions190.htm

UPDATEXML and XML namespaces

Using namespaces within UPDATEXML is very easy, you just specify the namespace as the fourth parameter to the function. The syntax (as stated above) is : 
UPDATEXML(xmltype_instance, xpath_expression, value_expr, namespace_expr)
Here is an example of updating a specific XML element (where y="10") to another value ("30") which is in a given namespace : 
SQL> DECLARE
  2    x  XMLTYPE := XMLTYPE('<x xmlns="xyz"><y>10</y><y>20</y></x>');
  3  BEGIN
  4    SELECT UPDATEXML(x, '/x/y[. = "10"]/text()', '30', 'xmlns="xyz"')
  5      INTO x
  6      FROM dual;
  7    dbms_output.put_line(x.getclobval);
  8  END;
  9  /
<x xmlns="xyz"><y>30</y><y>20</y></x>

PL/SQL procedure successfully completed.

DELETEXML

Okay, so we've seen how to UPDATE values in XML elements by using UPDATEXML, but another common requirement is to remove XML elements entirely. In 10g release 1 and below, any mechanism you used had to (at some point) utilise string manipulation to remove the tag, e.g. here's an XML document where the requirement is to remove the "b" tag(s) which have a value of anything beginning with '10'. In 10.1 and below, you had to do something similar to (again there are many ways to skin this cat, but the following is a common mechanism using UPDATEXML) : 
SQL> DECLARE
  2    x   XMLTYPE := XMLTYPE('<a><b><c>10X</c></b><b><c>20</c></b><b><c>10Y</c></b></a>');
  3  BEGIN
  4    SELECT UPDATEXML(x, '/a/b[starts-with(c,10)]', NULL)
  5          INTO x
  6          FROM dual;
  7    dbms_output.put_line(REPLACE(x.getstringval,'<b/>',''));
  8  END;
  9  /
<a><b><c>20</c></b></a>

PL/SQL procedure successfully completed.
But, as mentioned, there's still a requirement for manually removing the element (here, using REPLACE since UPDATEXML (as mentioned here) will return an empty tag if the whole tag is updated to NULL, i.e. : 
SQL> DECLARE
  2    x   XMLTYPE := XMLTYPE('<a><b><c>10X</c></b><b><c>20</c></b><b><c>10Y</c></b></a>');
  3  BEGIN
  4    SELECT UPDATEXML(x, '/a/b[starts-with(c,10)]', NULL)
  5          INTO x
  6          FROM dual;
  7    dbms_output.put_line(x.getstringval);
  8  END;
  9  /
<a><b/><b><c>20</c></b><b/></a>

PL/SQL procedure successfully completed.
However, in 10.2 and above, we get the lovely DELETEXML function, i.e. 
SQL> DECLARE
  2    x   XMLTYPE := XMLTYPE('<a><b><c>10X</c></b><b><c>20</c></b><b><c>10Y</c></b></a>');
  3  BEGIN
  4    SELECT DELETEXML(x, '/a/b[starts-with(c,10)]')
  5          INTO x
  6          FROM dual;
  7    dbms_output.put_line(x.getstringval);
  8  END;
  9  /
<a><b><c>20</c></b></a>
For further information, see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions042.htm#CIHEGJCB.

DELETEXML and XML namespaces

Using namespaces within DELETEXML is very easy. The syntax (which is similar to UPDATEXML) is : 
DELETEXML(xmltype_instance, xpath_expression, namespace_expr)
Here is an example of removing a specific XML element (where y="10") which is in a given namespace : 
SQL> DECLARE
  2    x  XMLTYPE := XMLTYPE('<x xmlns="xyz"><y>10</y><y>20</y></x>');
  3  BEGIN
  4    SELECT DELETEXML(x, '/x/y[. = "10"]', 'xmlns="xyz"')
  5      INTO x
  6      FROM dual;
  7    dbms_output.put_line(x.getclobval);
  8  END;
  9  /
<x xmlns="xyz"><y>20</y></x>

PL/SQL procedure successfully completed.

XMLROOT

XMLELEMENT will not generate the familiar XML "prolog" at the top of the document, and hence it was always necessary to "add" it on manually afterwards, i.e. 
SQL> SELECT XMLELEMENT("x", dummy) FROM dual;

XMLELEMENT("X",DUMMY)
---------------------------------------------------
<x>X</x>
XMLROOT comes to the rescue here, allowing specification of the prolog values of "version" and "standalone", i.e. 
SQL> SELECT XMLROOT(XMLELEMENT("x", dummy), VERSION '1.0', STANDALONE YES)
  2    FROM dual;

XMLROOT(XMLELEMENT("X",DUMMY),VERSION'1.0',STANDALONEYES)
-------------------------------------------------------------------------------
<?xml version="1.0" standalone="yes"?>
<x>X</x>
Not specifying any of the VERSION or STANDALONE attributes omits them from the prolog element, i.e. 
SQL> SELECT XMLROOT(XMLELEMENT("x", dummy), VERSION '1.0')
  2    FROM dual;

XMLROOT(XMLELEMENT("X",DUMMY),VERSION'1.0')
----------------------------------------------------------------
<?xml version="1.0"?>
<x>X</x>
Note, that the XML standard allows specification of an encoding scheme for XML documents, and this is specified in the XML prolog, however, XMLROOT does not allow you to specify this at this time : 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
For more information, see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions225.htm#CIHDAHBJ.

XMLCOMMENT

XMLCOMMENT allows specification of XML comments (surprise, surprise) which appear via the familiar "<!-- -->" syntax, (here, using the EXTRACT('/') method to turn on pretty printing) : 
SQL> SELECT XMLELEMENT("x",
  2           XMLELEMENT("x1", dummy),
  3           XMLCOMMENT('Test Comment'),
  4           XMLELEMENT("x2", dummy)
  5         ).EXTRACT('/')
  6    FROM dual;

XMLELEMENT("X",XMLELEMENT("X1",DUMMY),XMLCOMMENT
------------------------------------------------
<x>
  <x1>X</x1>
  <!--Test Comment-->
  <x2>X</x2>
</x>
For further information, see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions218.htm#CIHJIBJA.

XMLCDATA

In XML, CDATA sections are used to tell the XML parser to treat everything within it as data. This is handy if you have a requirement to send information which, for example, has a lot of XML "illegal" characters, such as &, < or >. By default, XMLELEMENTs containing these characters will be encoded into XML-friendly notation, for example, &amp; for an &, &lt; for < etc. This behavior is not always required, so XML allows definition of CDATA sections where this encoding does not occur.See the following from w3schools.com for more information on CDATA sections.

The syntax of a CDATA section is : <![CDATA[ string ]]>

Here's an example XML document : 
<parent>
  <![CDATA[Here is a string with a < and a >]]>
</parent>
Prior to 10g, the only way of generating CDATA sections was via a custom PL/SQL function, such as : 
CREATE OR REPLACE FUNCTION XMLCDATA (elementname VARCHAR2, cdatavalue VARCHAR2)
  RETURN XMLTYPE
AS
BEGIN
   RETURN XMLTYPE (   '<'
                   || elementname
                   || '><![CDATA['
                   || cdatavalue
                   || ']]></'
                   || elementname
                   || '>'
                  );
END;
and then subsequently calling it in your SQL/XML query, i.e. 
SQL> SELECT XMLCDATA('xyz', '123') x FROM dual;

X
--------------------------------------------------------------------------------
<xyz><![CDATA[123]]></xyz>
Note, that the function has to return the CDATA section within an XML element, if returning XMLTYPE (as the above function does), this is because XMLTYPE instances cannot be just CDATA sections, since this is not considered valid XML, i.e. 
SQL> CREATE OR REPLACE FUNCTION XMLCDATA (cdatavalue VARCHAR2)
  2    RETURN XMLTYPE
  3  AS
  4  BEGIN
  5    RETURN XMLTYPE (   '<![CDATA['
  6                    || cdatavalue
  7                    || ']]>'
  8                   );
  9  END;
 10  /

Function created.

SQL> select XMLCDATA('123') FROM dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: invalid character 33 ('!') found in a Name or Nmtoken
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 301
ORA-06512: at "MARTIN.XMLCDATA", line 5
Fortunately, in 10g, the XMLCDATA function can be used to create CDATA sections, and can be embedded at the logical position in your SQL/XML query.

The syntax is : XMLCDATA( string )

Here's an example which generates the XML above : 
SQL> SELECT XMLELEMENT("parent", XMLCDATA('Here is a string with a < and a >'))
  2    FROM dual;

XMLELEMENT("PARENT",XMLCDATA('HEREISASTRINGWITHA<ANDA>'))
--------------------------------------------------------------------------------
<parent><![CDATA[Here is a string with a < and a >]]></parent>
An important restriction of XML CDATA sections is that they cannot contain the literal string "]]>", therefore, the following would fail : 
SQL> SELECT XMLELEMENT("x", XMLCDATA(']]>'))
  2  FROM dual;
ERROR:
ORA-19041: Comment data cannot contain two consecutive '-'s

no rows selected
However, it's a bizarre error, since it seems to imply that you can't have the string -- in your data, which is not correct, i.e. 
SQL> SELECT XMLELEMENT("x", XMLCDATA('--'))
  2    FROM dual;

XMLELEMENT("X",XMLCDATA('--'))
-----------------------------------------------------------------------
<x><![CDATA[--]]></x>
It's also important to note, that if the input to XMLCDATA is NULL, then a CDATA section is not created, i.e. 
SQL> SELECT XMLELEMENT("x", XMLCDATA(NULL))
  2  FROM dual;

XMLELEMENT("X",XMLCDATA(NULL))
-------------------------------------------------
<x></x>
For more information, see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions216.htm#CIHFBEGB.

XMLPI

XML documents can have processing instructions (PIs) associated with them, which are application specific information. An example of a PI is the use of the xml-stylesheet which can be used to inform an application that the current XML document should be transformed using the specified XSL document. PIs can be anything, though, and a lot of the rules of XML do not apply to PIs.

In SQL/XML, the XMLPI function is used to create PIs, the syntax of which is : XMLPI([NAME] "PI name", "PI value"). 
SQL> SELECT XMLPI("custom-pi", 'app-specific info')
  2    FROM dual;

XMLPI("CUSTOM-PI",'APP-SPECIFICINFO')
---------------------------------------------------------
<?custom-pi app-specific info?>
Here's an example to generate an xml-stylesheet PI : 
SQL> SELECT XMLPI("xml-stylesheet", 'href="test.xsl"')
  2    FROM dual;

XMLPI("XML-STYLESHEET",'HREF="TEST.XSL"')
-------------------------------------------------------------------
<?xml-stylesheet href="test.xsl"?>
There are a few restrictions for the value of "PI name". Firstly, it cannot be the string "xml" in any case combination, i.e. 
SQL> SELECT XMLPI("xml", 'x')
  2  FROM dual;
ERROR:
ORA-19042: Enclosing tag xml cannot be xml in any case combination
The PI name or value can also not include the consecutive characters ?>, i.e. 
SQL> SELECT XMLPI("custom-pi", '?>')
  2    FROM dual;
ERROR:
ORA-19041: Comment data cannot contain two consecutive '-'s
However, at 10.2.0.1.0, there is a bug where the name CAN have the ?> characters : 
SQL> SELECT XMLPI("custom-pi?>", 'x')
  2    FROM dual
  3  /

XMLPI("CUSTOM-PI?>",'X')
-----------------------------------------
<?custom-pi?> x?>
If the value of the PI is NULL, then no PI is generated : 
SQL> SELECT XMLPI("custom-pi", NULL)
  2    FROM dual;

XMLPI("CUSTOM-PI",NULL)
-------------------------------------------
For more information, see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions223.htm#CIHGJCBF.

Extracting data using SQL/XML (post-11g)

As of 11.2.0.1, Oracle deprecated a significant number of the Oracle-proprietary functions for extracting information from XML documents, such as TABLE, XMLSEQUENCE, EXTRACTVALUE, EXTRACT etc., and instead recommend the use of the SQL/XML standard functions such as XMLTABLE, XMLEXISTS and XMLQUERY for all operations. For a full list of deprecated mechanisms (and for more detailed information), see What's New in Oracle 11.2 XML DB? (docs.oracle.com).

Fortunately, the new functions are generally easier to understand and perform better in some circumstances (especially when Oracle can perform XML xpath optimisation), so there are no real reasons why they should not be used.

XMLTABLE

XMLTABLE is a very powerful way of specifying the mapping of XML data into columns for use in a SQL statement. The basic usage is : 
XMLTABLE(<xpath/query> PASSING <xmltype document>
                         COLUMNS <column alias> <datatype> PATH <xpath/query>, {<column alias> <datatype> PATH <xpath/query>...})
An example SQL statement for processing an XML document would be : 
select *
from xmltable('/a' passing xmltype('<a><b>10</b><c>20</c></a>')
              columns b varchar2(2) path 'b',
                      c varchar2(2) path 'c')
/

B  C
-- --
10 20
Note, that column definitions are now declarative in the statement, rather than having to use multiple EXTRACTVALUE functions to get the values. As a comparison, the following SQL statement is generally how you would achieve the functionality above : 
SELECT
  EXTRACTVALUE(VALUE(t), '/a/b') b,
  EXTRACTVALUE(VALUE(t), '/a/c') a
FROM
  TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<a><b>10</b><c>20</c></a>'), '/a'))) t
/
Using XMLTABLE against an XML document contained in a table column, is done via the following syntax : 
SELECT t.b
FROM
  x,
  XMLTABLE('/a' passing x.a columns b varchar2(2) path 'b' ) t
/

B
--
10

The importance of datatype

It is EXTREMELY important to correctly specify the datatype of what you want to return, either NUMBER, XMLTYPE or the correctly sized VARCHAR2. Incorrect choices can result in incorrect output from your application's point of view. This differs somewhat from the "old" way of using EXTRACTVALUE (which always returns VARCHAR2) and EXTRACT (which returns XMLTYPE).

For example, specifying an xpath expression specified to be VARCHAR2 will return the XML element value, whereas specifying XMLTYPE for the same xpath expression results in the XML element that the xpath expression points to, e.g. : 
SELECT *
  FROM XMLTABLE('/a'
                PASSING XMLTYPE('<a><b>10</b></a>')
                COLUMNS b_as_varchar2 VARCHAR2(2) PATH 'b',
                        b_as_xmltype  XMLTYPE     PATH 'b')
/

B_AS_VARCHAR2   B_AS_XMLTYPE
--------------- ---------------------------------------------------
10              <b>10</b>
Specifying a VARCHAR2(n) value which is too small for your return value will essentially SUBSTR the value down to that, e.g. : 
SELECT *
  FROM XMLTABLE('/a'
                PASSING XMLTYPE('<a><b>ABCDEF</b></a>')
                COLUMNS b_as_varchar2 VARCHAR2(2) PATH 'b')
/
B_AS_VARCHAR2
---------------
AB

Obtaining attributes

Getting defined attributes (as opposed to elements) has not changed, you still use the @ syntax for extracting, i.e. 
select *
from xmltable('/a' passing xmltype('<a><b battr="1">10</b><c>20</c></a>'')
              columns b varchar2(2) path 'b',
                      battr varchar2(1) path 'b/@battr',
                      c varchar2(2) path 'c')
/

B  B C
-- - --
10 1 20

Using XML namespaces

Querying of XML data utilising a namespace is done by specifying the XMLNAMESPACES function as the first argument to XMLTABLE, which specifies the namespace and alias, and then using that alias in the Xpath/XQuery path, i.e. 
select t.b, t.c
from XMLTABLE(XMLNAMESPACES('namespace1' AS "ns1"),
              '/ns1:a'
              PASSING XMLTYPE('<a xmlns="namespace1"><b>10</b><c>20</c></a>')
              COLUMNS b VARCHAR2(2) PATH 'ns1:b',
                      c VARCHAR2(2) PATH 'ns1:c') t
/

B  C
-- --
10 20
Prior to using XMLTABLE, the querying of XML data which utilised multiple namespaces was quite tricky, requiring use of wildcard Xpath queries. With this functionality, it now becomes trivial, you just specify each namespace consecutively in XMLNAMESPACES each with a different alias : 
select t.b, t.c
from XMLTABLE(XMLNAMESPACES('namespace1' AS "ns1", 'namespace2' AS "ns2"),
              '/ns1:a'
              PASSING XMLTYPE('<a xmlns="namespace1"><b xmlns="namespace2">10</b><c>20</c></a>')
              COLUMNS b VARCHAR2(2) PATH 'ns2:b',
                      c VARCHAR2(2) PATH 'ns1:c') t

Flattening the XML structure

Given an XML document of the following form: 
<a>
  <b>
    <ref>1</ref>
    <c>10</c>
    <c>20</c>
    <c>30</c>
  </b>
</a>
to get output where you have a flattened structure, there are two main ways of acheiving this, depending on what version of Oracle you have.

From 12c and above, Oracle has enabled the RETURNING SEQUENCE BY REF mechanism of XMLTABLE, which allows you to extract at one level, but use xpath reverse axes to access parent XML element information, e.g: 
SELECT c.ref, c.val
  FROM XMLTABLE('/a/b/c'
                PASSING XMLTYPE('<a><b><ref>1</ref><c>10</c><c>20</c><c>30</c></b></a>')
                RETURNING SEQUENCE BY REF
                COLUMNS ref INTEGER PATH '../ref',
                        val INTEGER PATH '/') p
/

      REF      VAL
--------- --------
        1       10
        1       20
        1       30
Prior to 12c, to achieve the same result, you have to self-join multiple XMLTABLE structures, passing an XMLTYPE instance out of one and into the other, e.g. : 
SELECT p.ref, c.val
  FROM XMLTABLE('/a/b'
                PASSING XMLTYPE('<a><b><ref>1</ref><c>10</c><c>20</c><c>30</c></b></a>')
                COLUMNS ref INTEGER PATH 'ref',
                        cxml  XMLTYPE PATH 'c') p,
       XMLTABLE('/c'
                PASSING p.cxml
                COLUMNS val INTEGER PATH '/') c
/

      REF      VAL
--------- --------
        1       10
        1       20
        1       30

XMLEXISTS

XMLEXISTS is a SQL function that can be used wherever a BOOLEAN datatype can be used, such as in predicate or in a CASE statement. It is used to determine if a given XML node exists in an XML document. Note, the functional equivalent of this prior to the appearance of this function was either the EXISTSNODE function, but EXISTSNODE returns a numeric 1 or 0, not a BOOLEAN, or to do the check via XPath in XMLTABLE (or via EXTRACT).

The essential syntax is : 
XMLEXISTS(<xpath/xquery> PASSING <xmltype document>)
e.g. assuming that table "x" has an XMLTYPE column "a" : 
SELECT 'Y'
FROM x
WHERE XMLEXISTS('/a/b' PASSING x.a)
/

'
-
Y

SELECT CASE WHEN XMLEXISTS('/a/b' PASSING x.a) THEN 'TRUE' ELSE 'FALSE' END
FROM x
/

CASEW
-----
TRUE

Using namespaces in XMLEXISTS

In order to use namespaces in XMLEXISTS, unfortunately, you cannot use XMLNAMESPACES as you can with XMLTABLE. So, you have to declare the namespaces using XQuery syntax and then use a prefix notation to specify which namespace is required : 
SELECT 'Y'
FROM x
WHERE XMLEXISTS('declare namespace ns1="namespace1"; (::) /ns1:a/ns1:b' PASSING x.a)
/

'
-
Y

Extracting data using SQL/XML (pre-11g)

Extract method of XMLTYPE

One of the member functions of the XMLTYPE type is that of "Extract", and it allows you to specify an XPath expression to extract an XML fragment from an XMLTYPE document, i.e. this example shows how to extract all the "y" tags from an XML document directly contained with the "x" tag : 
SQL> SELECT XMLTYPE.CreateXML('<x><y>value</y></x>').Extract('x/y') test_output
  2    FROM dual;

TEST_OUTPUT
-------------------------
<y>value</y>

1 row selected.
Or, of course, if there's more than one : 
SQL> SELECT XMLTYPE.CreateXML('<x><y>value</y><y>value2</y></x>').Extract('x/y') test_output
  2    FROM dual;

TEST_OUTPUT
--------------------------
<y>value</y>
<y>value2</y>
See EXTRACT for an alternative mechanism for extracting XML fragments.

Ok, so given we can do this, how do we get the values of the tags themselves? This is where EXTRACTVALUE comes in.

EXTRACTVALUE

EXTRACTVALUE allows you to access the value of the specified element (via XPath expression), i.e. 
SQL> SELECT EXTRACTVALUE( XMLTYPE.CreateXML('<x><y>value</y></x>').Extract('x/y'), 'y' ) test_output
  2    FROM dual;

TEST_OUTPUT
----------------------------------------------------------------------------------------------------
value

1 row selected.
Since you can specify an XPath expression in the EXTRACTVALUE second parameter, there is technically no need for the Extract method on the above call, i.e. : 
SQL> SELECT EXTRACTVALUE( XMLTYPE.CreateXML('<x><y>value</y></x>'), 'x/y') test_output
  2    FROM dual;

TEST_OUTPUT
---------------------------------------------------------------------------------------
value

1 row selected.
Note, that specifying an XPath expression that does not correspond to an entry in the document does not result in error, but just a NULL value, i.e. 
SQL> SELECT EXTRACTVALUE( XMLTYPE.CreateXML('<x><y>value</y></x>'), 'x/z') test_output
  2    FROM dual;

TEST_OUTPUT
---------------------------------------------------------------------------------------

1 row selected.
The only error is by specifying an invalid XPath expression format, i.e. 
SQL> SELECT EXTRACTVALUE( XMLTYPE.CreateXML('<x><y>value</y></x>'), 'x/z/') test_output
  2    FROM dual;
  FROM dual
       *
ERROR at line 2:
ORA-31013: Invalid XPATH expression
However, EXTRACTVALUE is designed to extract a single value, what if you need multiple values from the same XPath extract, i.e. (from a previous example) : 
SQL> SELECT XMLTYPE.CreateXML('<x><y>value</y><y>value2</y></x>').Extract('x/y') test_output
  2    FROM dual;

TEST_OUTPUT
--------------------------
<y>value</y>
<y>value2</y>
Trying to use EXTRACTVALUE on the above XML fragment results in error, i.e. : 
SQL> SELECT EXTRACTVALUE( XMLTYPE.CreateXML('<x><y>value</y><y>value2</y></x>'), 'x/y')
  2  FROM dual;
SELECT EXTRACTVALUE( XMLTYPE.CreateXML('<x><y>value</y><y>value2</y></x>'), 'x/y')
                     *
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node
This is where you need XMLSEQUENCE.....

XMLSEQUENCE

XMLSEQUENCE is technically part of SQL/XML, but is mostly used when dealing with XPath extracts. Basically, it converts an XMLTYPE document into a VARRAY of XMLTYPE elements, which can then be queried using the TABLE operator just like any other nested collection type, i.e. 
SQL> SELECT *
  2    FROM TABLE(XMLSEQUENCE(XMLTYPE.CreateXML('<x><y>value</y><y>value2</y></x>').Extract('x/y')));

COLUMN_VALUE
-------------------------------
<y>value</y>
<y>value2</y>

2 rows selected.
I now have two rows containing all elements which match the "x/y" XPath expression, the value of each of which can then be obtained using EXTRACTVALUE, but now having to use the VALUE(table alias) syntax, since we're dealing with VARRAYs, i.e. 
SQL> SELECT EXTRACTVALUE(VALUE(t), 'y') test_output
  2    FROM TABLE(XMLSEQUENCE(XMLTYPE.CreateXML('<x><y>value</y><y>value2</y></x>').Extract('x/y'))) t;

TEST_OUTPUT
---------------------------------------------------------------------------------------------------------
value
value2

2 rows selected.
Note, that in the above example, we couldn't specify the XPath expression in the EXTRACTVALUE call, since, removing the Extract method would result in a single row in the VARRAY, which is the "x" element, rather than multiple rows of "y" elements, and hence we would end up with ORA-19025 again, i.e. 
SQL> SELECT EXTRACTVALUE(VALUE(t), 'x/y') test_output
  2    FROM TABLE(XMLSEQUENCE(XMLTYPE.CreateXML('<x><y>value</y><y>value2</y></x>'))) t;
SELECT EXTRACTVALUE(VALUE(t), 'x/y') test_output
*
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node
Having multiple elements and values simply requires multiple EXTRACTVALUE calls, i.e. 
SQL> SELECT
  2    EXTRACTVALUE(VALUE(t), 'x/y') value_of_y,
  3    EXTRACTVALUE(VALUE(t), 'x/z') value_of_z
  4  FROM
  5    TABLE(XMLSEQUENCE(XMLTYPE.CreateXML('<x><y>value</y><z>value2</z></x>'))) t;

VALUE_OF_Y VALUE_OF_Z
---------- ----------
value      value2

1 row selected.
Noting in the above example, that the XPath expression now differs for each element that is being extracted.

EXTRACT

It's worth noting at this point that there is an EXTRACT function which is very similar to EXTRACTVALUE, however, as you might have guessed, whereas the EXTRACTVALUE extracts the value of the element, the EXTRACT function performs the same operation but returns the whole XML fragment, not just the value. It works very much the same as the .EXTRACT method of the XMLTYPE object.

Here is a comparison of EXTRACT and EXTRACTVALUE : 
SQL> SELECT
  2    EXTRACTVALUE( XMLTYPE.CreateXML('<x><y>value</y></x>'), 'x/y') extractvalue,
  3    EXTRACT( XMLTYPE.CreateXML('<x><y>value</y></x>'), 'x/y') extract
  4  FROM dual;

EXTRACTVALUE                                       EXTRACT
-------------------------------------------------- --------------------------------------------------
value                                              <y>value</y>

1 row selected.

EXISTSNODE

EXISTSNODE is a relatively simple function, basically, it returns 1 if an XPath expression results in output, 0 otherwise, i.e. 
SQL> SELECT EXISTSNODE(VALUE(t), '/x/y'),
  2         EXISTSNODE(VALUE(t), '/x/z')
  3    FROM TABLE(XMLSEQUENCE(XMLTYPE.CREATEXML('<x><y>1</y></x>'))) t;

EXISTSNODE(VALUE(T),'/X/Y') EXISTSNODE(VALUE(T),'/X/Z')
--------------------------- ---------------------------
                          1                           0
For further information, see http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/functions043.htm.

Extracting element attributes

XPath is an extremely extensive subject, but one thing that is worth knowing is how to extract the values of attributes of an element, not necessarily the value of the element itself, for example, how do I extract the "attribute" value of the following document? : 
<test attribute="attr_value">X</test>
The answer is by using the @ syntax in XPath, which specifies that the expression points to an attribute of an element, i.e. 
SQL> SELECT
  2    EXTRACTVALUE(XMLTYPE.CreateXML('<test attribute="attr_value">X</test>'), '/test/@attribute') attr_value
  3  FROM dual;

ATTR_VALUE
---------------------------------------------------------------------------------------------------------------
attr_value

1 row selected.

XML unnesting

It is often the case when dealing with XML that you need to extract "parent" data and then process subsequent "child" data within it. The best way of demonstrating this is with an example, so here goes... 
Assume we have the following XML :

<A>
  <B>
    <ID>1</ID>
    <C>
      <D>
        <ID>1</ID>
      </D>
      <D>
        <ID>2</ID>
      </D>
    </C>
  </B>
  <B>
    <ID>2</ID>
    <C>
      <D>
        <ID>3</ID>
      </D>
      <D>
        <ID>4</ID>
      </D>
    </C>
  </B>
</A>
And, what I want to do is unnest the B "id" tags and the D "id" tags into a "flattened" row(s), i.e. 
B    D
1    1
1    2
2    3
2    4
The way to achieve this is, perhaps, alien to how you may think of doing it. Since XPath is, in itself, a matching technology, simply trying to extract /A/B/C/D/ID will not work correctly, since it would bring back four rows, not the two for each ID value of B. We need some way of correlating the extracts themselves, and this is done by including a second EXTRACT in the FROM clause referencing the results of the first EXTRACT, i.e. 
SQL> DECLARE
  2    x   XMLTYPE := XMLTYPE.CREATEXML('
  3  <A>
  4    <B>
  5      <ID>1</ID>
  6      <C>
  7        <D>
  8          <ID>1</ID>
  9        </D>
 10        <D>
 11          <ID>2</ID>
 12        </D>
 13      </C>
 14    </B>
 15    <B>
 16      <ID>2</ID>
 17      <C>
 18        <D>
 19          <ID>3</ID>
 20        </D>
 21        <D>
 22          <ID>4</ID>
 23        </D>
 24      </C>
 25    </B>
 26  </A>');
 27    x1  XMLTYPE;
 28  BEGIN
 29    FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/B/ID') b_id,
 30                      EXTRACTVALUE(VALUE(tc),'/D/ID') d_id
 31                 FROM TABLE(XMLSEQUENCE(x.EXTRACT('A/B'))) t,
 32                      TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), 'B/C/D'))) tc )
 33    LOOP
 34      dbms_output.put_line('B id : ' || i.b_id || ' : D id : ' || i.d_id);
 35    END LOOP;
 36  END;
 37  /
B id : 1 : D id : 1
B id : 1 : D id : 2
B id : 2 : D id : 3
B id : 2 : D id : 4

PL/SQL procedure successfully completed.
If you need to cater for parent elements without child element(s), then you have to use the correlated FROM "outer join" syntax, which involves placing the Oracle outer join operator, i.e. (+), on the "child" (i.e. "deficient") component of the FROM clause. In the following XML, we have two "B" parent elements, but only one of them has child "D" elements, but we still need to return both "B"s, hence, why we need to use the "outer join" : 
SQL> DECLARE
  2    x   XMLTYPE := XMLTYPE.CREATEXML('
  3  <A>
  4    <B>
  5      <ID>1</ID>
  6      <C>
  7        <D>
  8          <ID>1</ID>
  9        </D>
 10        <D>
 11          <ID>2</ID>
 12        </D>
 13      </C>
 14    </B>
 15    <B>
 16      <ID>2</ID>
 17      <C>
 18      </C>
 19    </B>
 20  </A>');
 21    x1  XMLTYPE;
 22  BEGIN
 23    FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/B/ID') b_id,
 24                      EXTRACTVALUE(VALUE(tc),'/D/ID') d_id
 25                 FROM TABLE(XMLSEQUENCE(x.EXTRACT('A/B'))) t,
 26                      TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), 'B/C/D'))) (+) tc )
 27    LOOP
 28      dbms_output.put_line('B id : ' || i.b_id || ' : D id : ' || i.d_id);
 29    END LOOP;
 30  END;
 31  /
B id : 1 : D id : 1
B id : 1 : D id : 2
B id : 2 : D id :

PL/SQL procedure successfully completed.
Once we have this output, we can then programmatically determine when the "parent" (i.e. "B") information has changed, and execute the relevant parent / child processing.

However, this approach does assume that there IS a unique parent "primary key" which we can use to process the parent information (in the above case, when B changes from 1 to 2 etc.). This is not always the case.

In the above situation, using this technique to extract information where the B "id" element can be NULL, the output would be : 
SQL> DECLARE
  2    x   XMLTYPE := XMLTYPE.CREATEXML('
  3  <A>
  4    <B>
  5      <ID />
  6      <C>
  7        <D>
  8          <ID>1</ID>
  9        </D>
 10        <D>
 11          <ID>2</ID>
 12        </D>
 13      </C>
 14    </B>
 15    <B>
 16      <ID />
 17      <C>
 18        <D>
 19          <ID>3</ID>
 20        </D>
 21        <D>
 22          <ID>4</ID>
 23        </D>
 24      </C>
 25    </B>
 26  </A>');
 27    x1  XMLTYPE;
 28  BEGIN
 29    FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/B/ID') b_id,
 30                      EXTRACTVALUE(VALUE(tc),'/D/ID') d_id
 31                 FROM TABLE(XMLSEQUENCE(x.EXTRACT('A/B'))) t,
 32                      TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), 'B/C/D'))) tc )
 33    LOOP
 34      dbms_output.put_line('B id : ' || i.b_id || ' : D id : ' || i.d_id);
 35    END LOOP;
 36  END;
 37  /
B id :  : D id : 1
B id :  : D id : 2
B id :  : D id : 3
B id :  : D id : 4

PL/SQL procedure successfully completed.
Hence, we wouldn't have any clue about which "D"s related to which "B"s when subsequently processing the results, i.e. were there one, two, three or four B "rows"? And, importantly, any attempt to programmatically infer which "row" is fundamentally flawed.

So, what can we do in this situation? Well, as per usual, there are many ways to skin a cat, but a favourite of mine is to use the EXTRACT function to extract an XML fragment for each row, and then process that within the main PL/SQL LOOP, i.e. 
SQL> DECLARE
  2    x   XMLTYPE := XMLTYPE.CREATEXML('
  3  <A>
  4    <B>
  5      <ID />
  6      <C>
  7        <D>
  8          <ID>1</ID>
  9        </D>
 10        <D>
 11          <ID>2</ID>
 12        </D>
 13      </C>
 14    </B>
 15    <B>
 16      <ID />
 17      <C>
 18        <D>
 19          <ID>3</ID>
 20        </D>
 21        <D>
 22          <ID>4</ID>
 23        </D>
 24      </C>
 25    </B>
 26  </A>');
 27    x1  XMLTYPE;
 28  BEGIN
 29    FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/B/ID') b_id,
 30                      EXTRACT(VALUE(t), '/B/C/D') d_xml,
 31                      rownum rn
 32                 FROM TABLE(XMLSEQUENCE(x.EXTRACT('A/B'))) t )
 33    LOOP
 34      FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/D/ID') d_id
 35                   FROM TABLE(XMLSEQUENCE(i.d_xml)) t )
 36      LOOP
 37        dbms_output.put_line('b row : ' || i.rn || ' : id : ' || i.b_id || ' : d id : ' || j.d_id);
 38      END LOOP;
 39    END LOOP;
 40  END;
 41  /
b row : 1 : id :  : d id : 1
b row : 1 : id :  : d id : 2
b row : 2 : id :  : d id : 3
b row : 2 : id :  : d id : 4

PL/SQL procedure successfully completed.
So, what we can see here is that while we don't have a unique "B" attribute, we are now in the context of a "parent" PL/SQL loop which relates to the "B" element.

Handling of Namespaces

How does Oracle XPath functionality handle namespaces within XML documents? Prior to 9.2, you could not specify namespace information, however, at 9.2 (and above), the EXTRACT and EXTRACTVALUE (along with EXISTSNODE etc.) allows specification of namespace information as a seperate parameter :

Here's a quick example : 
SQL> SELECT XMLTYPE.CreateXML('<x xmlns="x"><y>value</y></x>').Extract('x/y', 'xmlns="x"') test_output
  2  FROM dual
  3  /

TEST_OUTPUT
---------------------------------------------------------------------------------------------------------
<y xmlns="x">value</y>

1 row selected.
Notice the output XML fragment now is qualified by an xmlns="x" component.

When using EXTRACTVALUE, you can specify the namespace as the second parameter : 
SQL> SELECT EXTRACTVALUE(VALUE(t), 'y', 'xmlns="x"')
  2  FROM
  3    TABLE(XMLSEQUENCE(XMLTYPE.CreateXML('<x xmlns="x"><y xmlns="x">value</y></x>').Extract('x/y', 'xmlns="x"'))) t;

EXTRACTVALUE(VALUE(T),'Y','XMLNS="X"')
-------------------------------------------------------------------------------------------------------------------------
value

1 row selected.
However, be aware that there are special considerations when querying information out of an XML document which contains multiple namespaces.

For example, you cannot do the following (well, you can, but it won't give the correct result...) : 
SQL> SELECT EXTRACTVALUE(VALUE(t), 'y', 'xmlns="ns2"')
  2  FROM
  3    TABLE(XMLSEQUENCE(XMLTYPE.CreateXML('<x xmlns="ns1"><y xmlns="ns2">value</y></x>').Extract('x', 'xmlns="ns1"'))) t
  4  /

EXTRACTVALUE(VALUE(T),'Y','XMLNS="NS2"')
----------------------------------------

1 row selected.
This returns NULL, because the Extract still retains the xmlns="ns1" on the resultant XML document, so you still have a mix of ns1 and ns2 namespaces. Multiple namespaces cannot easily be queried in a namespace-specific sense, because Oracle only allows Extract'ing for a single namespace. It is possible to extract the data out in this case, but any approach you use is technically incorrect, since you have to use mechanisms which effectively "ignore" the namespace component, and, hence, could return incorrect results for certain XML documents.

An example of one approach is to use the XPath function local-name(), which can be used to return the element name regardless of namespace. 
SQL> SELECT EXTRACTVALUE(VALUE(t), '/*[local-name()="x"]/y', 'xmlns="ns2"')
  2 FROM
  3   TABLE(XMLSEQUENCE(XMLTYPE('<x xmlns="ns1"><y xmlns="ns2">value</y></x>'))) t;

EXTRACTVALUE(VALUE(T),'/*[LOCAL-NAME()="X"]/Y','XMLNS="NS2"')
--------------------------------------------------------------------------------
value
Attributes in different namespaces are extracted similarly using the @ syntax, i.e. 
SQL> SELECT EXTRACTVALUE(VALUE(t), '/*[local-name()="x"]/y/@*[local-name()="attr"]', 'xmlns="ns2"')
  2    FROM
  3      TABLE(XMLSEQUENCE(XMLTYPE('<x xmlns="ns1"><y xmlns="ns2" xmlns:z="ns3" z:attr="attr_value">value</y></x>'))) t;

EXTRACTVALUE(VALUE(T),'/*[LOCAL-NAME()="X"]/Y/@*[LOCAL-NAME()="ATTR"]','XMLNS="N
--------------------------------------------------------------------------------
attr_value

SQL/XML and XPath points of note

Limitations of ORDER BY

There seems to be a problem with the use of ORDER BY clause directly in a subquery as a parameter to XMLELEMENT, i.e. 
SQL> SELECT XMLELEMENT("test", (  SELECT XMLELEMENT("test2", dummy) FROM dual ORDER BY dummy ))
  2  FROM dual;
SELECT XMLELEMENT("test", (  SELECT XMLELEMENT("test2", dummy) FROM dual ORDER BY dummy ))
                                                                         *
ERROR at line 1:
ORA-00907: missing right parenthesis
The workaround to this is to utilise an inline view within the sub-query, which shouldn't impact performance, since predicate pushing is not precluded by the use of ORDER BY, i.e. 
SQL> SELECT
  2    XMLELEMENT("test",
  3               ( SELECT
  4                   XMLELEMENT("test2", dummy)
  5                 FROM
  6                   ( SELECT dummy FROM dual ORDER BY dummy )  ))
  7  FROM dual;
Note, this is still true as of 10.2.0.1.0.

Generating "NULL" XML Elements

In XML terms, there is no logical difference between the following XML fragments 
<test></test>
and 
<test/>
and certainly, no properly written XML application should care which is used, i.e. the XPath expression /test will return the correct element "value" in both cases.

Oracle are aware that, because of this logical equality in the two cases, they are in a sort-of "no-win" situation, some customers will want the former and some will want the latter. They have therefore decided to implement that for most situations generating a tag with no content will generate the former case, i.e. 
<test></test>
For example, 
SQL> SELECT XMLELEMENT("dummy", NULL) FROM dual;

XMLELEMENT("DUMMY",NULL)
-----------------------------------------------------
<dummy></dummy>
I said that this is true in most cases, for the following reasons.

Firstly, as mentioned in the XMLFOREST discussion earlier, an element generated as part of an XMLFOREST call will NOT generate an element at all (something to be aware of if working against a "minOccurs="1"" XML Schema!).

Secondly, there is a way of generating the latter syntax for null XML elements, but you have to utilise the EXTRACT method of each XMLELEMENT, i.e. 
SQL> SELECT XMLELEMENT("dummy", NULL) xml_1,
  2         XMLELEMENT("dummy", NULL).EXTRACT('/') xml_2,
  3         EXTRACT(XMLELEMENT("dummy", NULL), '/') xml_3
  4    FROM dual;

XML_1                XML_2                XML_3
-------------------- -------------------- --------------------
<dummy></dummy>      <dummy/>             <dummy></dummy>
But, even this is not consistent across database versions. The above example is on a 10g release 2 database, but look what happens to the same query on 9i release 2 : 
SQL> SELECT XMLELEMENT("dummy", NULL) xml_1,
  2         XMLELEMENT("dummy", NULL).EXTRACT('/') xml_2,
  3         EXTRACT(XMLELEMENT("dummy", NULL), '/') xml_3
  4    FROM dual;

XML_1                XML_2                XML_3
-------------------- -------------------- --------------------
<dummy></dummy>      <dummy/>             <dummy/>
Basically, Oracle changed the behaviour of the EXTRACT function (as opposed to the EXTRACT method) at 10.1.0.3, see Metalink note : 303875.1.

Note, this is actually part of the "pretty" printing routine examined in the next section.

"Pretty" printing using XMLSERIALIZE

One of the downsides with the SQL/XML functions (in later versions of 9i release 2 and above, i.e. 9.2.0.6 etc.) is that the generated XML in SQL*Plus comes out just a large un-formatted "string", i.e. 
SQL> SELECT XMLELEMENT("test", XMLELEMENT("test2", NULL),
  2                            XMLELEMENT("test3", NULL))
  3    FROM dual;

XMLELEMENT("TEST",XMLELEMENT("TEST2",NULL),XMLELEMENT("TEST3",NULL))
---------------------------------------------------------------------------
<test><test2></test2><test3></test3></test>
You used to be able to just utilise the .EXTRACT method of XMLTYPE to pretty print an XML document, but this was almost certainly a bug. EXTRACT should produce an XMLTYPE output, not introduce a load of whitespace.

The correct way of doing this is to serialize the XML into a string (or CLOB) and introduce the whitespace manually. You can do this via the XMLSERIALIZE function, i.e. 
SQL> SELECT XMLSERIALIZE(DOCUMENT XMLELEMENT("x", XMLELEMENT("y")) AS CLOB INDENT SIZE=2) xml_out FROM dual;

XML_OUT
-------------------------
<x>
  <y/>
</x>

Generation of invalid XML documents

According to the documentation, most SQL/XML operators are defined to return an instance of XMLTYPE, for example, XMLELEMENT, implying that the result is always a valid XML document, since this is the only value an XMLTYPE can take.

However, there are many ways of easily generating an XML fragment from SQL/XML functions, which cannot possibly be an XMLTYPE instance. Here's one example : 
SQL> SELECT XMLELEMENT("dummy", XMLATTRIBUTES('true' "xsi:nil"))
  2    FROM dual;

XMLELEMENT("DUMMY",XMLATTRIBUTES('TRUE'"XSI:NIL"))
---------------------------------------------------------------------
<dummy xsi:nil="true"></dummy>
However, trying to put this result into an XMLTYPE variable results in error, since the "xsi" namespace prefix is not declared anywhere : 
SQL> DECLARE
  2    x  XMLTYPE := XMLTYPE('<dummy xsi:nil="true"></dummy>');
  3  BEGIN
  4    NULL;
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00234: namespace prefix "xsi" is not declared
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 301
ORA-06512: at line 2

SQL/XML Performance

So, how does SQL/XML compare with other XML generation mechanisms, such as DBMS_XMLGEN etc., in terms of performance? Well, it's actually not that good! There are plenty of good reasons why you should use SQL/XML, and I've mentioned them above, but to recap : However, there's no getting around the fact that SQL/XML is a Java implementation, and while the Oracle JVM gets quicker and quicker with every release, even at 10gR2, there's still no beating the C-based implementation of DBMS_XML*. Here's the results of an investigation carried out on 10gR2.

Two tests were carried out to generate a simple XML from SQL query involving "n" number of rows from all_objects of the form : 
<ROWSET>
  <ROW>
    <OBJECT_NAME>...</OBJECT_NAME>
  </ROW>
  ...
</ROWSET>
The following script was used to generate a full XML document of the above form using ALL the rows in ALL_OBJECTS, currently, 49721 rows : 
SQL> SELECT COUNT(*) FROM all_objects;

  COUNT(*)
----------
     49721

SQL> DECLARE
  2    x   XMLTYPE;
  3    vTime NUMBER;
  4  BEGIN
  5    vTime := dbms_utility.get_time;
  6    x := DBMS_XMLGEN.GETXMLTYPE('SELECT object_name FROM all_objects');
  7    vTime := (dbms_utility.get_time-vTime)/100;
  8
  9    dbms_output.put_line('DBMS_XMLGEN : ' || vTime || ' secs');
 10
 11    vTime := dbms_utility.get_time;
 12
 13    SELECT
 14      XMLELEMENT("ROWSET",
 15        XMLAGG(XMLELEMENT("ROW", XMLELEMENT(object_name))))
 16    INTO x
 17    FROM all_objects;
 18
 19    vTime := (dbms_utility.get_time-vTime)/100;
 20
 21    dbms_output.put_line('SQL/XML : ' || vTime || ' secs');
 22  END;
 23  /
DBMS_XMLGEN : 5.62 secs
SQL/XML : 10.22 secs

PL/SQL procedure successfully completed.
Just look at the difference in performance. Nearly 100% quicker to do this query in DBMS_XMLGEN in 10gR2 (again,as Tom Kyte says, "your mileage may vary"), but it's an even bigger gap in prior releases.

So, what about "n" rows, how does SQL/XML "scale"? Well, again, nowhere near as good as DBMS_XML* packages. I used the following script to generate "n" rows from ALL_OBJECTS, in a loop, incrementing "n" by 500 each time, up to a maximum of 3000, so the idea is to get a representation of the time at which both DBMS_XMLGEN and SQL/XML can generate 500, 1000, 1500, 2000, 2500 and 3000 rows of XML : 
SQL> SET SERVEROUT ON
SQL> DECLARE
  2    x   XMLTYPE;
  3    vTime NUMBER;
  4    row_s  PLS_INTEGER;
  5
  6    TYPE typ_e IS RECORD ( num_rows   PLS_INTEGER,
  7                           io_type    VARCHAR2(100),
  8                           time_taken NUMBER );
  9
 10    TYPE tab2 IS TABLE OF typ_e INDEX BY BINARY_INTEGER;
 11    tab tab2;
 12
 13    inc_rement   PLS_INTEGER := 250;
 14    iters   PLS_INTEGER := 3000 / inc_rement;
 15  BEGIN
 16    FOR i IN 1..iters
 17    LOOP
 18      row_s := i * inc_rement;
 19      vTime := dbms_utility.get_time;
 20
 21      x := DBMS_XMLGEN.GETXMLTYPE('SELECT object_name FROM all_objects WHERE rownum < ' || row_s);
 22
 23      tab(i).num_rows := row_s;
 24      tab(i).io_type  := 'DBMS_XMLGEN';
 25      tab(i).time_taken := (dbms_utility.get_time-vTime)/100;
 26    END LOOP;
 27
 28    FOR i IN 1..iters
 29    LOOP
 30      row_s := i * inc_rement;
 31
 32      vTime := dbms_utility.get_time;
 33
 34      SELECT
 35        XMLELEMENT("ROWSET",
 36          XMLAGG(XMLELEMENT("ROW", XMLELEMENT(object_name))))
 37      INTO x
 38      FROM all_objects
 39      WHERE rownum < row_s;
 40
 41      tab(i+iters).num_rows := row_s;
 42      tab(i+iters).io_type  := 'SQL/XML';
 43      tab(i+iters).time_taken := (dbms_utility.get_time-vTime)/100;
 44    END LOOP;
 45
 46    FOR i IN 1..iters*2
 47    LOOP
 48      dbms_output.put_line(tab(i).io_type || ' : ' ||
 49                           tab(i).num_rows || ' : ' ||
 50                           tab(i).time_taken);
 51    END LOOP;
 52   END;
 53  /
Note, the "raw" data has been omitted for brevity, but the summarised results are (again, "your mileage may vary") :

Mechanism Num rows 250 500 750 1000 1250 1500 1750 2000 2250 2500 2750 3000
DBMS_XMLGEN Time (secs) 0.02 0.03 0.04 0.05 0.06 0.08 0.08 0.09 0.10 0.11 0.12 0.13
SQL/XML Time (secs) 0.23 0.36 0.53 0.70 0.68 0.67 0.58 0.67 0.81 0.88 0.95 1.17

So, as you can see, generating 250 rows in this simple test, takes nearly twice as long as DBMS_XMLGEN does to generate 3000 rows. It simply doesn't scale as well as C-based implementations.

So, what about playing to SQL/XML's strength(s) and testing out a more complex XML hierarchy? Well, let's try it.

Let's say I need to generate this XML structure for every row in ALL_OBJECTS : 
<ROWSET>
 <ROW>
  <object_name>...</object_name>
  <CHILD_TYPE>
   <TEST_OBJECT_TYPE>
    <dummy_value>1</dummy_value>
   </TEST_OBJECT_TYPE>
   <TEST_OBJECT_TYPE>
    <dummy_value>2</dummy_value>
   </TEST_OBJECT_TYPE>
  </CHILD_TYPE>
 </ROW>
 ...
</ROWSET>
How do we write this using DBMS_XMLGEN? Well, this is where the complex OBJECT TYPE hierarchies I mentioned earlier come into it. First of all, you have to construct your OBJECT TYPE hierarchy to match your XML hierarchy, so in this case : 
SQL> CREATE OR REPLACE TYPE test_object_type AS OBJECT ( "dummy_value" NUMBER(10) )
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE test_object_tab AS TABLE OF test_object_type
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE test_parent_type AS OBJECT ( "object_name" VARCHAR2(100),
  2                                                      child_type    test_object_tab );
  3  /

Type created.
Then, I have to create (well, I don't have to, but it makes it easier) an OBJECT VIEW, i.e. 
SQL> CREATE OR REPLACE VIEW v_test OF test_parent_type
  2  WITH OBJECT IDENTIFIER ("object_name")
  3  AS
  4  SELECT
  5    ao.object_name,
  6    CAST(MULTISET(SELECT num FROM ( SELECT 1 num FROM dual
  7                                     UNION ALL
  8                                    SELECT 2 num FROM dual)) AS test_object_tab )
  9  FROM
 10    all_objects ao
 11  /

View created.
Now, a SELECT from this view inside DBMS_XMLGEN generates the XML structure, i.e. 
SQL> SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT * FROM v_test WHERE rownum = 1')
  2  FROM dual
  3  /

DBMS_XMLGEN.GETXMLTYPE('SELECT*FROMV_TESTWHEREROWNUM=1')
------------------------------------------------------------------------------------
<ROWSET>
 <ROW>
  <object_name>ICOL$</object_name>
  <CHILD_TYPE>
   <TEST_OBJECT_TYPE>
    <dummy_value>1</dummy_value>
   </TEST_OBJECT_TYPE>
   <TEST_OBJECT_TYPE>
    <dummy_value>2</dummy_value>
   </TEST_OBJECT_TYPE>
  </CHILD_TYPE>
 </ROW>
</ROWSET>
So, what's the equivalent SQL/XML? Well, it's : 
SQL> SELECT XMLELEMENT("ROWSET",
  2           XMLAGG(XMLELEMENT("ROW",
  3                             XMLELEMENT("object_name", ao.object_name),
  4                             ( SELECT XMLELEMENT("CHILD_TYPE",
  5                                        XMLAGG(XMLELEMENT("TEST_OBJECT_TYPE",
  6                                                 XMLELEMENT("dummy_value", x.num))))
  7                                 FROM ( SELECT 1 num FROM dual
  8                                         UNION ALL
  9                                        SELECT 2 num FROM dual ) x ))))
 10  FROM
 11    all_objects ao
 12  WHERE rownum = 1
 13  /

XMLELEMENT("ROWSET",XMLAGG(XMLELEMENT("ROW",XMLELEMENT("OBJECT_NAME",AO.OBJECT_NAME),(SELECTXMLELEME
----------------------------------------------------------------------------------------------------
<ROWSET>
  <ROW>
    <object_name>ICOL$</object_name>
    <CHILD_TYPE>
      <TEST_OBJECT_TYPE>
        <dummy_value>1</dummy_value>
      </TEST_OBJECT_TYPE>
      <TEST_OBJECT_TYPE>
        <dummy_value>2</dummy_value>
      </TEST_OBJECT_TYPE>
    </CHILD_TYPE>
  </ROW>
</ROWSET>
So, if we run these two approaches through our timing script for ALL objects in ALL_OBJECTS, how do they fare? Here's our test script again : 
SQL> DECLARE
  2    x   XMLTYPE;
  3    vTime NUMBER;
  4  BEGIN
  5    vTime := dbms_utility.get_time;
  6    x := DBMS_XMLGEN.GETXMLTYPE('SELECT * FROM v_test');
  7    vTime := (dbms_utility.get_time-vTime)/100;
  8
  9    dbms_output.put_line('DBMS_XMLGEN : ' || vTime || ' secs');
 10
 11    vTime := dbms_utility.get_time;
 12
 13    SELECT XMLELEMENT("ROWSET",
 14             XMLAGG(XMLELEMENT("ROW",
 15                               XMLELEMENT("object_name", ao.object_name),
 16                               ( SELECT XMLELEMENT("CHILD_TYPE",
 17                                          XMLAGG(XMLELEMENT("TEST_OBJECT_TYPE",
 18                                                   XMLELEMENT("dummy_value", x.num))))
 19                                   FROM ( SELECT 1 num FROM dual
 20                                          UNION ALL
 21                                          SELECT 2 num FROM dual ) x ))))
 22    INTO x
 23    FROM
 24      all_objects ao;
 25
 26    vTime := (dbms_utility.get_time-vTime)/100;
 27
 28    dbms_output.put_line('SQL/XML : ' || vTime || ' secs');
 29  END;
 30  /
DBMS_XMLGEN : 7.81 secs
SQL/XML : 12.47 secs

PL/SQL procedure successfully completed.
Well, SQL/XML loses out again in the full test ("mileage may vary"), but the difference is only approx. 5 seconds to generate nearly 50000 XML elements. SQL/XML "wins out" on the ease of writing, one cursor as opposed to three custom database TYPEs and an OBJECT VIEW and a cursor.

So, what about the "n" row test script for this? As before, the script is presented and the results are tabulated : 
SQL> DECLARE
  2     x   XMLTYPE;
  3     vTime NUMBER;
  4     row_s  PLS_INTEGER;
  5
  6     TYPE typ_e IS RECORD ( num_rows   PLS_INTEGER,
  7                            io_type    VARCHAR2(100),
  8                            time_taken NUMBER );
  9
 10     TYPE tab2 IS TABLE OF typ_e INDEX BY BINARY_INTEGER;
 11     tab tab2;
 12
 13     inc_rement   PLS_INTEGER := 250;
 14     iters   PLS_INTEGER := 3000 / inc_rement;
 15  BEGIN
 16    FOR i IN 1..iters
 17    LOOP
 18      row_s := i * inc_rement;
 19      vTime := dbms_utility.get_time;
 20
 21      x := DBMS_XMLGEN.GETXMLTYPE('SELECT * FROM v_test WHERE rownum < ' || row_s);
 22
 23      tab(i).num_rows := row_s;
 24      tab(i).io_type  := 'DBMS_XMLGEN';
 25      tab(i).time_taken := (dbms_utility.get_time-vTime)/100;
 26    END LOOP;
 27
 28    FOR i IN 1..iters
 29    LOOP
 30      row_s := i * inc_rement;
 31
 32      vTime := dbms_utility.get_time;
 33
 34      SELECT XMLELEMENT("ROWSET",
 35               XMLAGG(XMLELEMENT("ROW",
 36                             XMLELEMENT("object_name", ao.object_name),
 37                             ( SELECT XMLELEMENT("CHILD_TYPE",
 38                                        XMLAGG(XMLELEMENT("TEST_OBJECT_TYPE",
 39                                                 XMLELEMENT("dummy_value", x.num))))
 40                                 FROM ( SELECT 1 num FROM dual
 41                                         UNION ALL
 42                                        SELECT 2 num FROM dual ) x ))))
 43       INTO x
 44       FROM
 45         all_objects ao
 46      WHERE rownum < row_s;
 47
 48      tab(i+iters).num_rows := row_s;
 49      tab(i+iters).io_type  := 'SQL/XML';
 50      tab(i+iters).time_taken := (dbms_utility.get_time-vTime)/100;
 51    END LOOP;
 52
 53    FOR i IN 1..iters*2
 54    LOOP
 55      dbms_output.put_line(tab(i).io_type || ' : ' ||
 56                           tab(i).num_rows || ' : ' ||
 57                           tab(i).time_taken);
 58    END LOOP;
 59  END;
 60  /
The results show that, again, SQL/XML is a relatively poor performer compared with DBMS_XML* :

Mechanism Num rows 250 500 750 1000 1250 1500 1750 2000 2250 2500 2750 3000
DBMS_XMLGEN Time (secs) 0.02 0.05 0.06 0.08 0.07 0.13 0.14 0.16 0.17 0.20 0.22 0.25
SQL/XML Time (secs) 0.22 0.40 0.65 0.81 0.72 0.95 0.83 0.81 0.95 1.18 1.43 1.71

SQL/XML Performance : Conclusion

No matter how much you like SQL/XML, the fact that it is static SQL, or the fact that it is simply easier to write XML generation SQL, you cannot ignore the fact that it's a relatively poor performer, compared with the DBMS_XML* packages. So, if you really do need those extra few seconds, it may be worth investing time into constructing your OBJECT TYPE hierarchy.

However, if you want a reasonably efficient mechanism that uses static SQL (and hence doesn't have the parsing issues associated with dynamic SQL, which will help scalability in itself, of course, if multiple sessions are generating the XML at any one time) and is very easy to write for even REALLY complex XML hierarchies (the same cannot be said for the DBMS_XML* packages), then SQL/XML would be your choice.