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 <tag>value</tag> 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 <, > and
& 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, & for an &, < 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 :
- Static SQL : No reparse issues when using dynamic SQL (such as DBMS_XMLGEN)
- Ease of use : No complex OBJECT TYPEs and / or OBJECT VIEWs to build up complex hierarchies
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.