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.
Standard SQL/XML functions
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>1</test2>
<test2>1</test2>
<test2>1</test2>
<test2>1</test2>
<test2>1</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.
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.
Proprietary SQL/XML functions
Oracle have implemented their own "SQL/XML" functions, over and above the functions defined in the
ISO standard.
XMLCOLATTVAL
It is sometimes required to generate XML of a specific format for describing relational data.
XMLCOLATTVAL is a shorthand way of generating <column> tag(s) with the column data as it's value,
and the name of the column as a "name" attribute, i.e.
SQL> SELECT XMLCOLATTVAL(dummy) FROM dual;
XMLCOLATTVAL(DUMMY)
--------------------------------------------
<column name="DUMMY">X</column>
Note, you can alias the column name if required, i.e.
SQL> SELECT XMLCOLATTVAL(dummy "test") from dual;
XMLCOLATTVAL(DUMMY"TEST")
--------------------------------------------------
<column name="test">X</column>
Of course, generating multiple column elements means just adding them to the XMLCOLATTVAL
parameter list, i.e.
SQL> SELECT XMLCOLATTVAL(dummy "dummy1", dummy "dummy2") FROM dual;
XMLCOLATTVAL(DUMMY"DUMMY1",DUMMY"DUMMY2")
---------------------------------------------------------------------
<column name="dummy1">X</column>
<column name="dummy2">X</column>
SYS_XMLGEN
Basically, SYS_XMLGEN accepts an XMLTYPE fragment, say an XMLELEMENT function, wraps a ROW tag around
the XML fragment (to ensure it's a valid XML document) and puts the XML prolog at the top.
SQL> SELECT SYS_XMLGEN(XMLELEMENT("dummy", dummy)) FROM dual;
SYS_XMLGEN(XMLELEMENT("DUMMY",DUMMY))
-----------------------------------------------------------------------
<?xml version="1.0"?>
<ROW>
<dummy>X</dummy></ROW>
See XMLFORMAT for information on how to change the ROW tag.
SYS_XMLAGG
SYS_XMLAGG works similarly to XMLAGG, except that SYS_XMLAGG
puts a ROWSET tag around the XML fragment (again, to ensure it's a valid XML document), and puts
the XML prolog at the top :
SQL> SELECT SYS_XMLAGG(XMLELEMENT("dummy", dummy)) FROM dual;
SYS_XMLAGG(XMLELEMENT("DUMMY",DUMMY))
----------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<dummy>X</dummy></ROWSET>
SYS_XMLAGG is, after all, a SQL/XML function that works "across" rows (i.e. aggregates), therefore,
with multiple rows, there'll only be one ROWSET tag, i.e. :
SQL> SELECT SYS_XMLAGG(XMLELEMENT("dummy", dummy))
2 FROM
3 ( SELECT dummy FROM dual
4 UNION ALL
5 SELECT dummy FROM dual )
6 /
SYS_XMLAGG(XMLELEMENT("DUMMY",DUMMY))
-----------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<dummy>X</dummy><dummy>X</dummy>
</ROWSET>
See XMLFORMAT for information on how to change the ROWSET tag.
XMLFORMAT
It is possible to use the XMLFORMAT object to modify certain aspects of the results of the
SYS_XMLGEN and SYS_XMLAGG functions. It is specified as the second parameter of the functions.
A DESC of XMLFORMAT produces the following object specification (excluding member procedures and
methods) :
SQL> DESC XMLFORMAT
Name Null? Type
----------------------------------------- -------- ---------------------
ENCLTAG VARCHAR2(4000)
SCHEMATYPE VARCHAR2(100)
SCHEMANAME VARCHAR2(4000)
TARGETNAMESPACE VARCHAR2(4000)
DBURLPREFIX VARCHAR2(4000)
PROCESSINGINS VARCHAR2(4000)
CONTROLFLAG RAW(4)
This is an example of how to modify the resultant ROW tag of SYS_XMLGEN, and the ROWSET tag
of SYS_XMLAGG :
SQL> SELECT SYS_XMLGEN(XMLELEMENT("dummy", dummy),
2 XMLFORMAT('XYZ'))
3 FROM dual
4 /
SYS_XMLGEN(XMLELEMENT("DUMMY",DUMMY),XMLFORMAT('XYZ'))
------------------------------------------------------------
<?xml version="1.0"?>
<XYZ>
<dummy>X</dummy>
</XYZ>
SQL> SELECT SYS_XMLAGG(XMLELEMENT("dummy", dummy),
2 XMLFORMAT('XYZ'))
3 FROM dual;
SYS_XMLAGG(XMLELEMENT("DUMMY",DUMMY),XMLFORMAT('XYZ'))
----------------------------------------------------------
<?xml version="1.0"?>
<XYZ>
<dummy>X</dummy>
</XYZ>
Note, that one of the member functions is CreateFormat, which can be used to similar effect,
instead of using the constructor method above. They are pretty much interchangeable.
STATIC FUNCTION CREATEFORMAT RETURNS XMLGENFORMATTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ENCLTAG VARCHAR2 IN DEFAULT
SCHEMATYPE VARCHAR2 IN DEFAULT
SCHEMANAME VARCHAR2 IN DEFAULT
TARGETNAMESPACE VARCHAR2 IN DEFAULT
DBURLPREFIX VARCHAR2 IN DEFAULT
PROCESSINGINS VARCHAR2 IN DEFAULT
The same as above, but using CreateFormat instead :
SQL> SELECT SYS_XMLGEN(XMLELEMENT("dummy", dummy),
2 XMLFORMAT.CreateFormat('XYZ'))
3 FROM dual;
SYS_XMLGEN(XMLELEMENT("DUMMY",DUMMY),XMLFORMAT.CREATEFORMAT('XYZ'))
---------------------------------------------------------------------
<?xml version="1.0"?>
<XYZ>
<dummy>X</dummy>
</XYZ>
XMLFORMAT will also allow many other options, but one of interest is the PROCESSINGINS parameter
which allows specification of XML Processing Instructions (PIs), an example of which is the addition
of XSL stylesheets etc, i.e.
SQL> SELECT SYS_XMLGEN(XMLELEMENT("dummy", dummy),
2 XMLFORMAT('XYZ', NULL, NULL, NULL, NULL,
3 '<?xml-stylesheet href="test.xsl" type="text/xsl" ?>'))
4 FROM dual;
SYS_XMLGEN(XMLELEMENT("DUMMY",DUMMY),XMLFORMAT('XYZ',NULL,NULL,NULL,NULL,'<?XML-
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<?xml-stylesheet href="test.xsl" type="text/xsl" ?>
<XYZ>
<dummy>X</dummy>
</XYZ>
For further information on XMLFORMAT format models, see
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements004.htm#i54997.
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.
10g Release 2 New XML functions
In 10g release 2, Oracle introduces a whole new raft of functions for creating and
modifying XML documents. Most of the functions are to bring it further into line with the
SQL/XML standard.
For a full list of all XML (and other) functions added in 10g release 2, see
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/wnsql.htm#sthref11.
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.
Other XML mechanisms
While this page does concentrate on SQL/XML and XPath as XML generation / extraction methods, there are
plenty of other mechanisms available for doing this in Oracle (as mentioned above, however, this can
be a problem for developers). While I do not want to go into detail on these (some of which I do think
are inferior to SQL/XML and XPath), there are useful mechanisms. I highlight some of them here.
DBMS_XMLSTORE
DBMS_XMLSTORE is a package which was originally released in Oracle 10g release 1, the sole purpose
of which is for extracting information quickly out of XML documents and storing it in a relational table.
It does have significant performance benefits for a few reasons :
It does rely on certain things, however :
- The XML document should be in a canonical ROWSET / ROW form like that produced by DBMS_XMLGEN,
(although, the expected name of the ROW tag can be changed programmatically by using the setRowTag
procedure)
- The "data" tags have to have the same name as the columns into which the relevant data will be placed.
Here's a quick example of how to utilise DBMS_XMLSTORE.
First thing is to create the relational table you want to store the data into :
SQL> CREATE TABLE t ( id NUMBER, clob CLOB );
Table created.
Next, you run a PL/SQL routine similar to this, which programmatically defines which table to put the
data into (i.e. DBMS_XMLSTORE.newContext) and also specifies the columns to "update" / "insert", via
DBMS_XMLSTORE.SetUpdateColumn (this step is optional, if you do not specify the "update" columns, DBMS_XMLSTORE will update / insert
all columns with the corresponding XML elements of the same name) :
SQL> DECLARE
2 qryCtx dbms_xmlgen.ctxHandle;
3 result CLOB;
4 savCtx DBMS_XMLSTORE.ctxType;
5 n NUMBER;
6 BEGIN
7 result:='<ROWSET>
8 <ROW>
9 <ID>123</ID>
10 <CLOB>XYZ</CLOB>
11 </ROW>
12 </ROWSET>';
13 savCtx := DBMS_XMLSTORE.newContext('t');
14 DBMS_XMLSTORE.clearUpdateColumnList(savCtx);
15
16 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'ID');
17 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'CLOB');
18
19 n := DBMS_XMLSTORE.insertxml(savCtx,result);
20 DBMS_XMLSTORE.closeContext(savCtx);
21 END;
22 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM t;
ID CLOB
---------- --------------------
123 XYZ
1 row selected.
For more information, see
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb_dbmstore.htm.
XPath in Oracle
The basic idea behind any XML document exchange is to get the data back out again. Again, as with the generation
of XML from SQL, there are many ways to skin the proverbial cat. This section will explore one of those
methods, XPath.
Note, that the use of XPath involves extensive usage of the 9i XMLTYPE datatype, and as such will not work
on any version of the database prior to 9i.
Note, also, that this document is not intended to be an extensive work on XPath expressions etc. It simply
shows how to utilise XPath efficiently within Oracle SQL and PL/SQL.
For a detailed list of XPath functions and operators (not all of which are guaranteed to work with
your version of Oracle), see
http://www.w3schools.com/xpath/xpath_functions.asp.
Note, that an important thing to remember about
XPath in Oracle is that (currently, up to 10.2), both the EXTRACT and EXTRACTVALUE functions
only permit XPath functions which return a node-set, otherwise you get the all to obvious
ORA-31012: Given XPATH expression not supported error. What this basically means is that you can
use XPath to filter a set of XML elements, i.e. you can successfully extract only the set of data which
matches a given criteria :
SQL> DECLARE
2 x XMLTYPE := XMLTYPE('<a>
3 <b>ABCDEF</b>
4 <b>ABC123</b>
5 </a>');
6 BEGIN
7 FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/b') b_val
8 FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/a/b[starts-with(., "ABCD")]'))) t )
9 LOOP
10 dbms_output.put_line('B : ' || i.b_val);
11 END LOOP;
12 END;
13 /
B : ABCDEF
PL/SQL procedure successfully completed.
BUT, you can't use it to operate on a single XML element (here, trying to get the equivalent of
SUBSTR on the value of each "b" element) :
SQL> DECLARE
2 x XMLTYPE := XMLTYPE('<a>
3 <b>ABCDEF</b>
4 <b>ABC123</b>
5 </a>');
6 BEGIN
7 FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), 'substring(/b, 1, 4)') b_val
8 FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/a/b'))) t )
9 LOOP
10 dbms_output.put_line('B : ' || i.b_val);
11 END LOOP;
12 END;
13 /
DECLARE
*
ERROR at line 1:
ORA-31012: Given XPATH expression not supported
ORA-06512: at line 7
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 acheive 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 : Known Oracle Bugs
As with all technologies, there are going to be limitations and bugs. SQL/XML has it's fair share, not
only because it's a relatively new mechanism for generating XML. This section attempts to highlight
commonly experienced problems, and potential workarounds.
ORA-22905: cannot access rows from a non-nested table item
Sometimes, in PL/SQL when dealing with an INSERT INTO .. SELECT .. which utilises XMLSEQUENCE
and which references PL/SQL variables, you can get the ORA-22905 error (here, I'm using an XMLTYPE
variable "x" in the FROM clause). The first example shows that putting the XMLTYPE directly in the
FROM clause (via the constructor method) works OK :
SQL> BEGIN
2 INSERT INTO x_test ( x, y, z )
3 SELECT EXTRACTVALUE(x.t_val, '/w/x') x_val,
4 EXTRACTVALUE(x.t_val, '/w/y') y_val,
5 EXTRACTVALUE(x.t_val, '/w/z') z_val
6 FROM
7 ( SELECT VALUE(t) t_val
8 FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<v><w><x>T</x></w></v>'), '/v/w'))) t ) x;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 x XMLTYPE := XMLTYPE('<v><w><x>T</x></w></v>');
3 BEGIN
4 INSERT INTO x_test ( x, y, z )
5 SELECT EXTRACTVALUE(x.t_val, '/w/x') x_val,
6 EXTRACTVALUE(x.t_val, '/w/y') y_val,
7 EXTRACTVALUE(x.t_val, '/w/z') z_val
8 FROM
9 ( SELECT VALUE(t) t_val
10 FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/v/w'))) t ) x;
11 END;
12 /
DECLARE
*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at line 4
This corresponds to Oracle bug 4187886. There's no known workaround, but it is fixed in 10.2.0.1.
ORA-19011: Character string buffer too small
There is a problem with the appending of strings directly onto XMLTYPE variables within PL/SQL, when
dealing with large XML documents, i.e.
SQL> DECLARE
2 l_clob CLOB;
3 BEGIN
4 SELECT '<?xml version="1.0"?>' || XMLAGG(XMLELEMENT("object_name", object_name))
5 INTO l_clob
6 FROM all_objects;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-19011: Character string buffer too small
ORA-06512: at line 4
The problem here, seemingly, is that PL/SQL is assigning the output to a temporary area, which is more
like a VARCHAR2(4000), rather than a CLOB, and hence it exceeds the limit of that datatype.
The reality of the situation is that, if you are attempting to do this, then you need to explicitly
convert to a clob (usually via the .getClobVal() member function of XMLTYPE), and this is best done in a
view (well, can ONLY be done in a view, since PL/SQL will not understand the getClobVal() call), i.e.
SQL> CREATE OR REPLACE VIEW v ( xml_output )
2 AS
3 SELECT XMLAGG(XMLELEMENT("object_name", object_name)).getClobVal()
4 FROM all_objects;
View created.
SQL> DECLARE
2 l_clob CLOB;
3 BEGIN
4 SELECT '<?xml version="1.0"?>' || xml_output
5 INTO l_clob
6 FROM v;
7 END;
8 /
PL/SQL procedure successfully completed.
LPX-00234: namespace prefix ".." is not declared
Using the EXTRACT method of an XMLTYPE instance can result in Oracle not being able to validate the
XML document when dealing with namespaces. Here's an example :
SQL> DECLARE
2 x XMLTYPE := XMLTYPE('<x:a xmlns:x="x.y.z"><x:b>10</x:b></x:a>');
3 p VARCHAR2(10);
4 BEGIN
5 SELECT EXTRACTVALUE(VALUE(t), '/b', 'xmlns="x.y.z"')
6 INTO p
7 FROM TABLE(XMLSEQUENCE(x.EXTRACT('/a/b', 'xmlns="x.y.z"'))) t;
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00234: namespace prefix "x" is not declared
Error at line 1
ORA-06512: at line 5
So, as you can see, it's valid XML but Oracle declares it invalid. It's the combination of
EXTRACTVALUE and the EXTRACT method which causes the problem, i.e.
SQL> DECLARE
2 x XMLTYPE := XMLTYPE('<x:a xmlns:x="x.y.z"><x:b>10</x:b></x:a>');
3 p VARCHAR2(10);
4 BEGIN
5 SELECT 1
6 INTO p
7 FROM TABLE(XMLSEQUENCE(x.EXTRACT('/a/b', 'xmlns="x.y.z"'))) t;
8 END;
9 /
PL/SQL procedure successfully completed.
As you can see, works OK when you remove the EXTRACTVALUE, for example.
A "workaround" is to use the EXTRACT function instead of the EXTRACT method, i.e.
SQL> DECLARE
2 x XMLTYPE := XMLTYPE('<x:a xmlns:x="x.y.z"><x:b>10</x:b></x:a>');
3 p VARCHAR2(10);
4 BEGIN
5 SELECT EXTRACTVALUE(VALUE(t), '/b', 'xmlns="x.y.z"')
6 INTO p
7 FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/a/b', 'xmlns="x.y.z"'))) t;
8 END;
9 /
PL/SQL procedure successfully completed.
Note, that this problem only occurs when dealing with non schema-based XML documents. Base
the XMLTYPE instance on a registered XML Schema Document (XSD), and the problem does not occur.
ORA-00978: nested group function without GROUP BY
Using solely aggregate functions in SQL/XML with the use of XMLAGG can result in ORA-00978.
SQL> SELECT XMLAGG(XMLELEMENT("test", SUM(1)))
2 FROM dual;
SELECT XMLAGG(XMLELEMENT("test", SUM(1)))
*
ERROR at line 1:
ORA-00978: nested group function without GROUP BY
The workaround to this is to simply remove the XMLAGG, in this case we do not need to aggregate any
XML, so it's simply not required :
SQL> SELECT XMLELEMENT("test", SUM(1))
2 FROM dual
3 /
XMLELEMENT("TEST",SUM(1))
------------------------------------------------
<test>1</test>
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
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>
Fortunately, there is an easy way of turning so-called "pretty-printing" on, and that's by using the
EXTRACT method of XMLELEMENT, with either the "/" or "/*" XPath expressions, i.e.
SQL> SELECT XMLELEMENT("test", XMLELEMENT("test2", NULL),
2 XMLELEMENT("test3", NULL)).EXTRACT('/*')
3 FROM dual;
XMLELEMENT("TEST",XMLELEMENT("TEST2",NULL),XMLELEMENT("TEST3",NULL)).EXTRACT('/*
--------------------------------------------------------------------------------
<test>
<test2/>
<test3/>
</test>
Note, for further information see Metalink note : 301262.1, or
this Metalink
forum entry.
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.