Methods to parse XML per Oracle version

One of the items I've noted while hanging out in the XML DB forum or the general XML forum is that many people are still using the old extract and extractValue methods to parse XML via SQL statements.

Starting with 11.2, Oracle has deprecated extract and extractValue. As you can see from the Oracle documentation, Oracle suggest you use XMLQuery in place of extract and either XMLTable or XMLCast/XMLQeury in place of extractValue.

So what method should you use in SQL to parse XML? It depends upon your version of Oracle of course.

Oracle version: 8i - 9.0.x.x
There was no option that I can recall or could find. All the parsing of XML that I've done in 8i was via the xmldom package.

Oracle version: 9.2.x.x - 10.1.x.x
This is were Oracle introduced extract, extractValue and TABLE(XMLSequence(extract())) for dealing with repeating nodes.

Oracle version: 10.2.x.x
Oracle introduced XMLTable as a replacement for the previous methods since it could handle all three methods for extracting data from XML. At that point, Oracle stopped enhancing extract/extractValue in terms of performance and focused on XMLTable. In 10.2.0.1 and .2, XMLTable was implemented via Java and in .3 it was moved into the kernel so performance from .3 onwards should be better than the older 9.2 / 10.1 methods. If not, feel free to open a ticket with Oracle support. Apparently Oracle also introduced XMLQuery as well but I've never heard of many using that in 10.2

Oracle version: 11.1.x.x - 11.2.x.x
Oracle still has XMLTable and XMLQuery as I pointed out above, but also added in XMLCast as a way to cast the output of XMLQuery into a desired datatype.

So when coding, please try to pick the parsing approach that works with the version of Oracle being used. It's good for your job skills and Oracle provides better support for current functionality than deprecated functionality.

Text Literal Structure

In a previous post on XMLType/XMLTransform and parameters, I'd discovered the q'{}' structure from the OTN forum post, but I didn't know much more about it. I wasn't sure what to go searching for so I left it as an interesting tidbit to figure out later.

Just about two months after that post (on Nov 4, 2010 to be exact), I encountered this syntax structure in a PL/SQL Challenge quiz. To see the actual quiz, you will need to register (free) on the site. If you are into learning more about the database from the SQL and PL/SQL side of things in a simple quiz that takes a few minutes of time each day, I strongly suggest signing up and participating. You can find his blog discussion of this particular quiz at The quote character (q) and the 4 November quiz.

Using his wording as a starting point to search the Oracle documentation, I soon came across Text Literals. Somewhere along the way I saw that this was a 10g (10.1 I believe) addition. I went from 8.1 to 10.2, so it explains why I missed it.

It's pretty simple to use. Ignoring the national character version, you start with
q''
Within that, you put your delimiters. As the documentation says,
If the opening quote_delimiter is one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character.
so we can setup structures such as

• q'[]'
• q'<>'
• q'##'

and within our quote delimiter, we simply put the desired text. Some examples:

BEGIN
  dbms_output.put_line(q'{Here be the text with a ' in it}');
  dbms_output.put_line(q'*Here be the text with a ' in it*');
  dbms_output.put_line(q'#Random words put here#');
  dbms_output.put_line(q'^Here be the text with a ' in it^');
END;

which produces

Here be the text with a ' in it
Here be the text with a ' in it
Random words put here
Here be the text with a ' in it

Makes it a lot easier to put a single quote into a string than using double single quotes.

* Caveat *
Apparently the Text Literal process has issues with strings that contain 'n', where n' is at the end of the string.  By that I mean the following will all run successfully

select q'[help'n ']'
from dual;
select q'['na']'
from dual;
select q'['a']'
from dual;

but the following all return an ORA-01756: quoted string not properly terminated

select q'[help'n']'
from dual;
select q'['an']'
from dual;
select q'['n']'
from dual;

I've seen this error on both 10.2.0.4 and 11.1.0.6. I can't find anything in MOS yet on it.

XMLTable with a Parameter

I found this question on the OTN forums, Problem with XMLTABLE with parameters, interesting because
a) I knew there was a way to accomplish that
b) I couldn't quickly turn it up via Google till I got the right keywords.

After seeing the solution that I ended up borrowing, I first checked the Oracle documentation on XMLTable for 10.2 to verify this was valid. As you can see by the documentation, the XML_passing_clause does allow for multiple expressions.

Curious as to see how this worked for different data types and on 10g and 11g, I created the following setup.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL>
SQL> CREATE TABLE XMLT
 2  (xmlcol  XMLType)
 3   XMLTYPE column xmlcol store AS BINARY XML;

Table created

SQL>
SQL> INSERT INTO XMLT
 2  VALUES
 3  ('<Root>
 4    <Object>
 5      <ObjectID>ID_1</ObjectID>
 6      <ObjectName>Name 1</ObjectName>
 7      <ObjectValue>1</ObjectValue>
 8      <ObjectDate>1980-01-01</ObjectDate>
 9    </Object>
10    <Object>
11      <ObjectID>ID_2</ObjectID>
12      <ObjectName>Name 2</ObjectName>
13      <ObjectValue>2</ObjectValue>
14    </Object>
15  </Root>');

1 row inserted

SQL> commit;

Commit complete

sys_xmlgen works on both 10g and 11g for these scenarios. With this, everything is treated as a simple string so you just need to ensure the string you pass in matches up to the string in the XML.

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectID=$objID]'
 4                  PASSING XMLT.xmlcol,
 5                          sys_xmlgen('ID_1') as "objID"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectValue=$objVal]'
 4                  PASSING XMLT.xmlcol,
 5                          sys_xmlgen('2') as "objVal"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_2   Name 2

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          sys_xmlgen('1980-01-01') as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

Another way to do it in 11g is to use CAST to convert a string to the appropriate data type. I go overboard with the Date related queries. I will explain afterwards.

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectID=$objID]'
 4                  PASSING XMLT.xmlcol,
 5                          cast('ID_1' AS VARCHAR2(4)) as "objID"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectValue=$objVal]'
 4                  PASSING XMLT.xmlcol,
 5                          CAST('2' AS NUMBER) as "objVal"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_2   Name 2

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          CAST('01-JAN-1980' AS DATE) as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          CAST(DATE '1980-01-01' AS DATE) as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          DATE '1980-01-01' as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          TO_DATE('01-01-1980', 'MM-DD-YYYY') as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          '1980-01-01' as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          TO_DATE('01-01-1980', 'MM-DD-YYYY') as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

With my setup, Oracle knows nothing about the XML in terms of data types so 2 and 1980-01-01 are simply strings to it. So, even though I was CASTing a string to a DATE, Oracle is converting the value back to a string during the XPath evaluation. (Note: This is not confirmed but highly suspected).

So why is using CAST good? It comes in useful when you have a schema registered within Oracle and the XMLType is based off of the schema. This provides Oracle with data type information and is especially true for Object Relational Storage where it is used to create data types for columns. Oracle knows the data type of the node so you want to pass in the same data type using an explicit conversion. This is the basic reasoning why you don't want to compare a number to a string. Oracle will implicitly convert one to the other and can cause issues it if encounters a string with "a1" in it that fails to convert to a number.. That is the purpose of CAST, so you can tell Oracle that a given parameter is a number or a date to avoid the implicit conversion.

Gotchas

• Running the above XMLTable with CAST SQL Statements will result in an ORA000932: inconsistent datatypes: expected - got
  

Creating XML via the DB

Just some ways to produce XML from an Oracle DB using only SQL and PL/SQL. I'm sure this list is incomplete so I will expand it as I remember others. This is just what was coming to mind before writing this entry

• SQL/XML (XMLElement, XMLForest, XMLAgg)
• DBMS_XMLDOM
• XQUERY
• DBMS_XMLGEN
• DBMS_XMLQuery
• TRANSFORM/XMLTransform
• CreateXML/XMLType

Basically this serves as a reminder of what to cover in the future.

XMLType/XMLTransform and parameters

While spending time in the OTN XML DB forums, I ran across yet another thread where I learned something new. The thread was Add Namespaces via XQuery to an XML Instance . Besides relearning I still have a lot to learn about XQuery, I learned that the XMLTransform SQL operator accepts a third parm. This makes sense given the two operations, the PL/SQL .transform and SQL XMLTransform, perform the same task. If you look at the 11.2 documentation for XMLTransformation, it still shows it accepts only two parms. The XMLType.Transform function shows a third parm, parammap, which is a VARCHAR2.

Given my lack of understanding of the third parm in general, I decided to research it. From what little I was able to turn up, it is described in more detail in Metalink. I am currently without Metalink access so I'm left to wonder as well what is said. I was able to piece together the following examples of a few ways to send in multiple parms.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
 
SQL> set serveroutput on;
SQL> 
SQL> DECLARE
  2    l_xml      XMLTYPE := XMLTYPE('<root/>');
  3    l_xsd      XMLTYPE;
  4    l_new_xml  XMLTYPE;
  5    l_val1     VARCHAR2(5) := 'val1';
  6    l_val2     VARCHAR2(5) := 'val 2';
  7  BEGIN
  8    l_xsd := XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                                            xmlns:fo="http://www.w3.org/1999/XSL/Format" 
                                            exclude-result-prefixes="fo">
  9      <xsl:output method="xml"/>
 10  
 11      <xsl:param name="par1"/>
 12      <xsl:param name="par2"/>
 13  
 14      <xsl:template match="/">
 15          <output>
 16              <param1><xsl:value-of select="$par1"/></param1>
 17              <param2><xsl:value-of select="$par2"/></param2>
 18          </output>
 19      </xsl:template>
 20  </xsl:stylesheet>');
 21  
 22    dbms_output.put_line('#1');  -- hard coded strings
 23    l_new_xml := l_xml.transform(l_xsd, 'par1="''val1''"  par2="''val 2''"');
 24    dbms_output.put_line(l_new_xml.getStringVal());
 25  
 26    dbms_output.put_line('#2');  -- hard coded strings
 27    l_new_xml := l_xml.transform(l_xsd, q'{par1="'val1'" par2="'val 2'"}');
 28    dbms_output.put_line(l_new_xml.getStringVal());
 29  
 30    dbms_output.put_line('#3');  -- passing in variables
 31    l_new_xml := l_xml.transform(l_xsd, 'par1="''' || l_val1 || '''" par2="''' || l_val2 || '''"');
 32    dbms_output.put_line(l_new_xml.getStringVal());
 33  
 34    dbms_output.put_line('#4');  -- hard coded string/number
 35    l_new_xml := l_xml.transform(l_xsd, 'par1="''val1''"  par2="2"');
 36    dbms_output.put_line(l_new_xml.getStringVal());
 37  
 38    dbms_output.put_line('#5');  -- hard coded string/number
 39    l_new_xml := l_xml.transform(l_xsd, q'{par1="'val1'" par2="2"}');
 40    dbms_output.put_line(l_new_xml.getStringVal());
 41  END;
 42  /
 
#1
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#2
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#3
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#4
<output>
<param1>val1</param1>
<param2>2</param2>
</output>

#5
<output>
<param1>val1</param1>
<param2>2</param2>
</output>
 
PL/SQL procedure successfully completed

As the Oracle documentation says, the parameters are a "string of name=value pairs". What the online documentation leaves out are examples regarding usage.

So what can we learn from the working examples above?

• If the value is a string, the right side formatted is as "'value'". Note it is wrapped with a leading/trailing '. Depending upon how you setup your parameter, this may mean the use of two single quotes to insert one single quote in the resolved string.
  
• If the value is a number, the right side formatted is as "value". No leading/trailing ' is needed. You can treat the number as a string but then the above rule applies.
  
• Spaces are allowed within a value.
  
• Everything can be treated as a string or number that is being passed into the .xsl
  
• A space, one or more, is used to separate pairs.
  
• The common error reported when not following the above noticed rules was an "ORA-31020: The operation is not allowed, Reason: Invalid XSL Parameter or its Value". I did see a few others, including killing my session as the OTN forum thread noted when randomly throwing data in the third parm.
  

I'm not sure where the q'{}' structure comes from but it means no need for putting double single quotes around the string values as shown in #2 and #5.

As that forum post shows, and the below example as well, you can pass the third parameter into XMLTransform and it will work appropriately.

SQL> SELECT XMLSerialize(DOCUMENT
  2           XMLTransform(XMLTYPE('<root/>'),
  3                        XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  4                                              xmlns:fo="http://www.w3.org/1999/XSL/Format"
  5                                              exclude-result-prefixes="fo">
  6      <xsl:output method="xml"/>
  7  
  8     <xsl:param name="par1"/>
  9     <xsl:param name="par2"/>
 10  
 11     <xsl:template match="/">
 12        <output>
 13           <param1><xsl:value-of select="$par1"/></param1>
 14           <param2><xsl:value-of select="$par2"/></param2>
 15          </output>
 16      </xsl:template>
 17  </xsl:stylesheet>'),
 18         'par1="''val1''"  par2="2"')
 19           AS CLOB INDENT) xsl_output
 20    FROM dual;
 
XSL_OUTPUT
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<output>
  <param1>val1</param1>
  <param2>2</param2>
</output>

You can replace the third parameter with any of the examples shown above, assuming you setup variable for #3, and the results will be identical to that shown above. Note: I simply used XMLSerialize to format the output similar to how the PL/SQL code formatted it.

So now we know the basics for how to pass parameters to stylesheet and that you can do it with XMLTransform as well, even though the documentation doesn't specify it.

Memory Leaks

The thought for this posting has been in my head for a while and then this week this post came up on the OTN XML DB Forums ... XMLTYPE.createxml - how to free memory?

I knew when working with DOMDocuments that there was a .FreeDocument() but I didn't know of a similar concept for XMLType. From the testing I did given what the poster was seeing, it looks to be a bug only when using a sys_refcursor as input when doing an XMLTYPE conversion. It could impact BFILENAME as well but I need to get around to testing that at some point.

This brought back memories from a few years back of when I stumbled across a memory leak in the code one of our clients was using on 10.2.0.3. We knew we had memory leaks somewhere in the app code because we would need to restart our drivers (sessions that ran 24x7) every few days else the higher processing volume threads would eat up memory and kill the node. This was in a RAC so we got to watch the fail-over work as intended. Not something you want to do on a regular basis in a production system. We put in a process to stop/start those threads every so often to release memory while we tried to figure out where the leak was.

At that time a task came up where I had to do something as a one time job, so I hacked together a script that used a lot of production code, tested it and finally let it loose in Production. I was using DBMS_APPLICATION_INFO to log job progress and I noticed that over time, the job would start slowing down. I could stop and restart the job and it would run fast and then start slowing down again. After a lot of research and time stripping out pieces of code and rerunning and not seeing any progress, I finally figured out the code was missing some dbms_xmldom.FreeDocument statements that the documentation tells you to use.

Ah, the cause of the memory leak. Reviewed the code, plugged those in where missing, go back to testing my script and notice no difference. Huh? I stripped the script down even further till I had something like the following.

DECLARE 
   l_dom_doc   dbms_xmldom.DOMDocument;

   PROCEDURE p_local_open_close IS
   BEGIN
      l_dom_doc := dbms_xmldom.newDOMDocument;
      dbms_xmldom.FreeDocument(l_dom_doc);
   END p_local_open_close;

BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'user_domDocument_mem_leak_test',
                                    action_name => 'SLEEPING');
   dbms_lock.sleep(20);
   /* Sleeps so have time to find OS PID via
   select s.sid, s.process, s.serial#, s.module, s.action, p.spid os_pid
   from gv$session s,
        gv$process p
   where s.module = 'user_domDocument_mem_leak_test'
     and s.paddr = p.addr;
   */
   dbms_application_info.set_action('RUNNING');
   FOR i IN 1..700000  -- adjust as needed for your system
   LOOP
     p_local_open_close;
   END LOOP;
   dbms_application_info.set_action('COMPLETE');
  
END;

Running that on both 10.2.0.1 (our Development at the time) and 10.2.0.3 (Test/Production) would result in memory usage growing consistently while the script was running. We had our client open a SR with Oracle and they did confirm it was a bug. At some point later, Oracle provided the patch to fix this bug and all was good. I think this fix was included in the .4 release but I've lost the bug number for reference purposes. I think it stemmed from some of the JAVA used under the covers by dbms_xmldom based on what I learned from Oracle's responses regarding .freeDocument on this SR and another I had open with them regarding side effects.

One other interesting thing we saw is that the Development ran on RedHat Linux and Test/Production on SunOS. When the script finished running in Dev, the amount of memory that was used would be freed up even though the session/thread was still alive. On SunOS, we had to kill the session/thread before the memory would free up. I was always interested in whether that was caused by the .1 and .3 difference or the OS difference but we never had the opportunity to sync the version up until both went to .4

XML Parsing with Default Namespaces via XMLTable

In my previous post XML Parsing with Namespaces via XMLTable I covered parsing of XML where a namespace prefix existed for each namespace. What happens when you have to parse XML where some of the XML is in a default namespace? With XMLTable, it is simple as the XMLNamespace clause has a DEFAULT option to deal with this situation. Here is another round of simple example based off the ongoing example.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL> CREATE TABLE your_table (xml_col   XMLTYPE);

Table created

SQL>
SQL> INSERT INTO your_table
2  VALUES
3   ('<employees xmlns="abc.com/123" b="xyz.net/456">
4   <emp>
5    <name>Scott</name>
6    <b:favorites>
7     <b:color>red</b:color>
8     <b:color>orange</b:color>
9    </b:favorites>
10   </emp>
11   <emp>
12    <name>John</name>
13    <b:favorites>
14     <b:color>blue</b:color>
15     <b:color>green</b:color>
16    </b:favorites>
17   </emp>
18  </employees>');

1 row inserted

SQL> commit;

Commit complete

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL>
SQL> -- Method 3
SQL> SELECT xt.nam
2    FROM your_table yt,
3         XMLTable(XMLNamespaces(DEFAULT 'abc.com/123'),
4                  'employees/emp'
5                  PASSING yt.xml_col  -- defines source of XMLType, can define a join
6                  COLUMNS
7                  nam  VARCHAR2(20) PATH 'name') xt;

NAM
--------------------
Scott
John
SQL> --
SQL> -- Method 4
SQL> -- declares a prefix for the default
SQL> SELECT xt.nam
2    FROM your_table yt,
3         XMLTable(XMLNamespaces('abc.com/123' AS "a"),
4                  'a:employees/a:emp[2]'
5                  PASSING yt.xml_col
6                  COLUMNS
7                  nam  VARCHAR2(20) PATH 'a:name') xt;

NAM
--------------------
John
SQL> --
SQL> -- Method 5.a
SQL> SELECT xt.color
2    FROM your_table yt,
3         XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
4                                'xyz.net/456' AS "b"),
5                  'employees/emp/b:favorites/b:color'
6                  PASSING yt.xml_col
7                  COLUMNS
8                  color  VARCHAR2(10) PATH '.') xt;

COLOR
----------
red
orange
blue
green
SQL> --
SQL> -- Method 5.b
SQL> SELECT xt.nam, xt2.color
2    FROM your_table yt,
3         XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
4                                'xyz.net/456' AS "b"),
5                  'employees/emp'
6                  PASSING yt.xml_col
7                  COLUMNS
8                  nam      VARCHAR2(20) PATH 'name',
9                  color_t  XMLTYPE      PATH 'b:favorites') xt,  -- path to the node that repeats
10         XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
11                  'b:favorites/b:color'
12                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
13                  COLUMNS
14                  color  VARCHAR2(10) PATH '.') xt2;

SELECT xt.nam, xt2.color
FROM your_table yt,
    XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
                           'xyz.net/456' AS "b"),
             'employees/emp'
             PASSING yt.xml_col
             COLUMNS
             nam      VARCHAR2(20) PATH 'name',
             color_t  XMLTYPE      PATH 'b:favorites') xt,  -- path to the node that repeats
    XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
             'b:favorites/b:color'
             PASSING xt.color_t  -- define input XMLType as output of above, aka a join
             COLUMNS
             color  VARCHAR2(10) PATH '.') xt2

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '/oraxq_defpfx:emp/b:favorites'
SQL> --
SQL> -- Method 5.c
SQL> SELECT xt.nam, xt2.color
2    FROM your_table yt,
3         XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
4                                'xyz.net/456' AS "b"),
5                  'employees/emp'
6                  PASSING yt.xml_col
7                  COLUMNS
8                  nam      VARCHAR2(20) PATH 'name',
9                  color_t  XMLTYPE      PATH 'b:favorites/b:color') xt,  -- path to the node that repeats
10         XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
11                  '/b:color'
12                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
13                  COLUMNS
14                  color  VARCHAR2(10) PATH '.') xt2;

SELECT xt.nam, xt2.color
FROM your_table yt,
    XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
                           'xyz.net/456' AS "b"),
             'employees/emp'
             PASSING yt.xml_col
             COLUMNS
             nam      VARCHAR2(20) PATH 'name',
             color_t  XMLTYPE      PATH 'b:favorites/b:color') xt,  -- path to the node that repeats
    XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
             '/b:color'
             PASSING xt.color_t  -- define input XMLType as output of above, aka a join
             COLUMNS
             color  VARCHAR2(10) PATH '.') xt2

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '/oraxq_defpfx:emp/b:favorites/b:color'

SQL> 

Wait, what? Errors? I'll get back to that.
In Method 3, we declared the default namespace for the XML via
DEFAULT 'abc.com/123'
On the XML elements that reside in the default namespace, we do not need to include a prefix as the default namespace is assumed. XMLTable makes it that simple. You can still declare the namespace and assign it a prefix as Method 4 shows if you prefer or would need in situations where the XML has many different default namespaces.

Now about Method 5.b and 5.c, where did that error come from? That would be a bug in the version of Oracle I am using. The SQL runs correctly on 10.2 and 11.2 at least. I can't speak for other versions of 11.1
What can we learn from this bug? We learn that when the DEFAULT namespace is declared within an XMLNamespaces clause that Oracle behind the scenes creates a prefix called oraxq_defpfx and applies it to the appropriate elements in the XQuery statement.

You can work around this bug by declaring your own prefix for the namespace as shown in Method 4 and using that prefix in the XPath accordingly. I would avoid declaring any namespace prefixes that start with "ora" just to be safe. Final note, since "oraxq_defpfx" is an internal definition for the default namespace prefix, that name is subject to be changed at any time by Oracle for any reason.

XML Parsing with Namespaces via XMLTable

In Basic XML Parsing via XMLTable I covered the basics for parsing simple XML stored in the database via SQL, but most XML has one or more namespaces associated with it. So how do you deal with that? With XMLTable it is simple. As the XMLTable documentation shows, there is an XMLnamespaces_clause where this information can be provided. This translates into the XMLNamespaces(), parameter within the XMLTable syntax. The documentation doesn't show how to use it, but the XML DB FAQ Thread on the OTN XML DB forum does (see the "How do I declare namespace prefix mapping with XMLTable()?" thread). As before in the PL/SQL example showing namespaces, I've taken the previous XML and added two namespaces to it and updated the appropriate calls to make this example.

The updated XML I used.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL>
SQL> CREATE TABLE your_table (xml_col   XMLTYPE);

Table created
SQL> INSERT INTO your_table
2  VALUES
3  ('<a:employees xmlns:a="abc.com/123" xmlns:b="xyz.net/456">
4   <a:emp>
5    <a:name>Scott</a:name>
6    <b:favorites>
7     <b:color>red</b:color>
8     <b:color>orange</b:color>
9    </b:favorites>
10   </a:emp>
11   <a:emp>
12    <a:name>John</a:name>
13    <b:favorites>
14     <b:color>blue</b:color>
15     <b:color>green</b:color>
16    </b:favorites>
17   </a:emp>
18  </a:employees>');

1 row inserted
SQL> commit;

Commit complete

SQL> -- Method 3
SQL> SELECT xt.nam
 2    FROM your_table yt,
 3         XMLTable(XMLNamespaces('abc.com/123' AS "a"),
 4                  'a:employees/a:emp'
 5                  PASSING yt.xml_col  -- defines source of XMLType, can define a join
 6                  COLUMNS
 7                  nam  VARCHAR2(20) PATH 'a:name') xt;

NAM
--------------------
Scott
John
SQL> --
SQL> -- Method 4
SQL> SELECT xt.nam
 2    FROM your_table yt,
 3         XMLTable(XMLNamespaces('abc.com/123' AS "a"),
 4    'a:employees/a:emp[2]'
 5                  PASSING yt.xml_col
 6                  COLUMNS
 7                  nam  VARCHAR2(20) PATH 'a:name') xt;

NAM
--------------------
John
SQL> --
SQL> -- Method 5.a
SQL> SELECT xt.color
 2    FROM your_table yt,
 3         XMLTable(XMLNamespaces('abc.com/123' AS "a",
 4                                'xyz.net/456' AS "b"),
 5           'a:employees/a:emp/b:favorites/b:color'
 6                  PASSING yt.xml_col
 7                  COLUMNS
 8                  color  VARCHAR2(10) PATH '.') xt;

COLOR
----------
red
orange
blue
green
SQL> --
SQL> -- Method 5.b
SQL> SELECT xt.nam, xt2.color
 2    FROM your_table yt,
 3         XMLTable(XMLNamespaces('abc.com/123' AS "a",
 4                                'xyz.net/456' AS "b"),
 5                  'a:employees/a:emp'
 6                  PASSING yt.xml_col
 7                  COLUMNS
 8                  nam      VARCHAR2(20) PATH 'a:name',
 9                  color_t  XMLTYPE      PATH 'b:favorites') xt,  -- path to the node that repeats
10         XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
11                  'b:favorites/b:color'
12                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
13                  COLUMNS
14                  color  VARCHAR2(10) PATH '.') xt2;

NAM                  COLOR
-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green
SQL> --
SQL> -- Method 5.c
SQL> SELECT xt.nam, xt2.color
 2    FROM your_table yt,
 3         XMLTable(XMLNamespaces('abc.com/123' AS "a",
 4                                'xyz.net/456' AS "b"),
 5                  'a:employees/a:emp'
 6                  PASSING yt.xml_col
 7                  COLUMNS
 8                  nam      VARCHAR2(20) PATH 'a:name',
 9                  color_t  XMLTYPE      PATH 'b:favorites/b:color') xt,  -- path to the node that repeats
10         XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
11                  '/b:color'
12                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
13                  COLUMNS
14                  color  VARCHAR2(10) PATH '.') xt2;

NAM                  COLOR
-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green

which is exactly the same output as the previous example. This is what we expected. As always, you only need to pass along namespaces that are referenced directly in the XMLTable and not all the namespaces associated with the XML.

Trouble shooting tips:

• Don't forgot to include the comma after the closing parenthesis on the XMLNamespaces(), structure otherwise you'll get ORA-02000: missing , keyword
  

Basic XML Parsing via XMLTable

In Basic XML Parsing via PL/SQL, I looked at "How do I parse this XML?" when the XML resides in PL/SQL. Oftentimes, the XML resides in the database and so Oracle provides another option for parsing the XML, the XMLTable command. This command became available with the release of 10.2. On versions <= 10.1, you have to use TABLE(xmlsequence(extract(...))). I might cover that at some point in the future but will see. While reading that documentation may make XMLTable seem complex, which it can be, it is very easy to use. Let us start with putting our XML into the DB via:

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL>
SQL> CREATE TABLE your_table
 2  (xml_col   XMLTYPE);

Table created

SQL>
SQL> INSERT INTO your_table
 2  VALUES
 3  ('<employees>
 4   <emp>
 5    <name>Scott</name>
 6    <favorites>
 7     <color>red</color>
 8     <color>orange</color>
 9    </favorites>
10   </emp>
11   <emp>
12    <name>John</name>
13    <favorites>
14     <color>blue</color>
15     <color>green</color>
16    </favorites>
17   </emp>
18  </employees>');

1 row inserted

SQL> commit;

Commit complete

Now let's get out the same data we did in the original PL/SQL example. For this we will only be doing Methods 3 - 5 since they cover Methods 1 and 2 as well.

SQL> -- Method 3
SQL> SELECT xt.nam
 2    FROM your_table yt,
 3         XMLTable('employees/emp'
 4                  PASSING yt.xml_col  -- defines source of XMLType, can define a join
 5                  COLUMNS
 6                  nam  VARCHAR2(20) PATH 'name') xt;

NAM
--------------------
Scott
John

SQL> --
SQL> -- Method 4
SQL> SELECT xt.nam
 2    FROM your_table yt,
 3         XMLTable('employees/emp[2]'
 4                  PASSING yt.xml_col
 5                  COLUMNS
 6                  nam  VARCHAR2(20) PATH 'name') xt;

NAM
--------------------
John

SQL> --
SQL> -- Method 5.a
SQL> SELECT xt.color
 2    FROM your_table yt,
 3         XMLTable('employees/emp/favorites/color'
 4                  PASSING yt.xml_col
 5                  COLUMNS
 6                  color  VARCHAR2(10) PATH '.') xt;

COLOR
----------
red
orange
blue
green

SQL> --
SQL> -- Method 5.b
SQL> SELECT xt.nam, xt2.color
 2    FROM your_table yt,
 3         XMLTable('employees/emp'
 4                  PASSING yt.xml_col
 5                  COLUMNS
 6                  nam      VARCHAR2(20) PATH 'name',
 7                  color_t  XMLTYPE      PATH 'favorites') xt,  -- path to the node that repeats
 8         XMLTable('favorites/color'
 9                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
10                  COLUMNS
11                  color  VARCHAR2(10) PATH '.') xt2;

NAM                  COLOR
-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green

SQL> --
SQL> -- Method 5.c
SQL> SELECT xt.nam, xt2.color
 2    FROM your_table yt,
 3         XMLTable('employees/emp'
 4                  PASSING yt.xml_col
 5                  COLUMNS
 6                  nam      VARCHAR2(20) PATH 'name',
 7                  color_t  XMLTYPE      PATH 'favorites/color') xt,  -- path to the node that repeats
 8         XMLTable('/color'
 9                  PASSING xt.color_t  -- define input XMLType as output of above, aka a join
10                  COLUMNS
11                  color  VARCHAR2(10) PATH '.') xt2;

NAM                  COLOR
-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green

Methods 3 - 5.a here extract the same information as we did via PL/SQL.

Breaking down the Oracle documentation syntax, I will cover XMLnamespaces_clause in another post. The XQuery_string can be a simple XPath as shown above or more complex XQuery statements to be demonstrated later. The XML_passing_clause is just the PASSING line where the input XML is defined. This can join the XMLTable to another table/XMLTable, a variable, or be XML generated right there. The XML_table_column section following COLUMNS simply lists how to find the information referenced by the XQuery_string. The information is relative to the results of the XQuery_string.

Trouble shooting tips:

• If you are unsure of what the XMLType looks like at some point within an XMLTable, simply create a column of XMLType and include it in the SELECT list. In example 5.c, adding xt.color_t to the select list allows us to see what Oracle was passing into the second XMLTable.

Oracle Documentation

One thing I have noticed while being on the OTN forums is that not everyone knows about the online documentation for the version of Oracle they are using. Thankfully, Oracle has the handy Oracle Documentation to start from.

XML Parsing with Default Namespaces via PL/SQL

In my previous post I covered parsing of XML where a namespace prefix existed for each namespace. What happens when you have to parse XML where some of the XML is in a default namespace? Make up a namespace prefix. No W3C complaint parser will care about what the prefix is in the XML and the prefix in the XPath is as long as the URI (the stuff within the " quotes) is the same for the node in question. Here is a simple example based off the ongoing example

DECLARE
  l_doc       VARCHAR2(2000);
  l_domdoc    dbms_xmldom.DOMDocument;
  l_nodelist  dbms_xmldom.DOMNodeList;
  l_node      dbms_xmldom.DOMNode;
  l_value     VARCHAR2(30);
  l_a_ns      VARCHAR2(30);
  l_both_ns   VARCHAR2(60);

  l_xmltype   XMLTYPE;
  l_empx      XMLTYPE;
  l_index     PLS_INTEGER;
  l_col_ind   PLS_INTEGER;
BEGIN
  l_doc := '<employees xmlns="abc.com/123" xmlns:b="xyz.net/456">
 <emp>
  <name>Scott</name>
  <b:favorites>
   <b:color>red</b:color>
   <b:color>orange</b:color>
  </b:favorites>
 </emp>
 <emp>
  <name>John</name>
  <b:favorites>
   <b:color>blue</b:color>
   <b:color>green</b:color>
  </b:favorites>
 </emp>
</employees>';

  l_a_ns := 'xmlns:a="abc.com/123"';  -- assign random prefix to namespace
  -- assigning a different prefix to this namespace
  l_both_ns := l_a_ns || ' xmlns:c="xyz.net/456"'; -- separated by a space

  l_xmltype := XMLTYPE(l_doc);

  -- Method 4
  dbms_output.new_line;
  dbms_output.put_line('Method 4');
  l_value := l_xmltype.extract('/employees/emp[2]/name/text()', 'xmlns="abc.com/123"').getStringVal();
  dbms_output.put_line('Emp Name: '||l_value);

  -- Method 5
  dbms_output.new_line;
  dbms_output.put_line('Method 5');
  l_index := 1;
  WHILE l_xmltype.Existsnode('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns) > 0
  LOOP
   l_empx := l_xmltype.extract('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns);
   l_col_ind := 1;
   WHILE l_empx.Existsnode('/a:emp/c:favorites/c:color[' || To_Char(l_col_ind) || ']', l_both_ns) > 0
   LOOP
      l_value := l_empx.extract('/a:emp/c:favorites/c:color[' || To_Char(l_col_ind) || ']/text()', l_both_ns).getStringVal();
      dbms_output.put_line('Color: '||l_value);
      l_col_ind := l_col_ind + 1;
   END LOOP;
   l_index := l_index + 1;
  END LOOP;
END;

In the XML, we have a default namespace of "abc.com/123" for the elements and the previous "xyz.net/456" namespace. In the code, I randomly picked a prefix of "a" for the default namespace and so anytime an element in the default namespace is referenced, the corresponding prefix is used.

As Method 4 shows, you could just provide the default namespace as is for the third parm and then there is no need to include a prefix in the XPath itself. I prefer to go the route of method 5 as I have encountered XML where the "default" namespace has changed several nodes deep in the XML. Since the default changes, you need to specify a namespace prefix to keep things in order with the namespace each node resides in.

XML Parsing with Namespaces via PL/SQL

My previous post covered the basics for parsing simple XML in PL/SQL, but most XML has one or more namespaces associated with it. So how do you deal with that? Simple pretty much. Oracle provides a third parm (usually it is the third) to pass namespace URI information along to the underlying parser. I've taken the previous XML and added two namespaces to it and updated the appropriate calls to make this example.

DECLARE
l_doc       VARCHAR2(2000);
l_domdoc    dbms_xmldom.DOMDocument;
l_nodelist  dbms_xmldom.DOMNodeList;
l_node      dbms_xmldom.DOMNode;
l_value     VARCHAR2(30);
l_a_ns      VARCHAR2(30);
l_both_ns   VARCHAR2(60);

l_xmltype   XMLTYPE;
l_empx      XMLTYPE;
l_index     PLS_INTEGER;
l_col_ind   PLS_INTEGER;
BEGIN
l_doc := '<a:employees xmlns:a="abc.com/123" xmlns:b="xyz.net/456">
 <a:emp>
  <a:name>Scott</a:name>
  <b:favorites>
   <b:color>red</b:color>
   <b:color>orange</b:color>
  </b:favorites>
 </a:emp>
 <a:emp>
  <a:name>John</a:name>
  <b:favorites>
   <b:color>blue</b:color>
   <b:color>green</b:color>
  </b:favorites>
 </a:emp>
</a:employees>';

l_domdoc   := dbms_xmldom.newDomDocument(l_doc);
l_a_ns := 'xmlns:a="abc.com/123"';
l_both_ns := l_a_ns || ' xmlns:b="xyz.net/456"'; -- separated by a space

-- Method 1
dbms_output.put_line('Method 1');
-- third parm for namespace info
l_nodelist := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_domdoc),'/a:employees/a:emp/a:name', l_a_ns);
FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nodelist) - 1 LOOP
 l_node := dbms_xmldom.item(l_nodelist, cur_emp);
 l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
 dbms_output.put_line('Emp Name: '||l_value);
END LOOP;

-- Method 2
dbms_output.new_line;
dbms_output.put_line('Method 2');
l_nodelist  := dbms_xmldom.getelementsbytagname(l_domdoc, 'name');  -- This doesn't care about namespaces
-- get first item from list, could loop as shown above
l_node      := dbms_xmldom.item(l_nodelist, 0);
l_value    := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
dbms_output.put_line('Emp Name: '||l_value);

-- Done with DOMDocument examples, setup for XMLType based examples
dbms_xmldom.freeDocument(l_domdoc);
l_xmltype := XMLTYPE(l_doc);

-- Method 3
dbms_output.new_line;
dbms_output.put_line('Method 3');
l_index := 1;
WHILE l_xmltype.Existsnode('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns) > 0
LOOP
 l_value := l_xmltype.extract('/a:employees/a:emp[' || To_Char(l_index) || ']/a:name/text()', l_a_ns).getStringVal();
 dbms_output.put_line('Emp Name: '||l_value);
 l_index := l_index + 1;
END LOOP;

-- Method 4
dbms_output.new_line;
dbms_output.put_line('Method 4');
l_value := l_xmltype.extract('/a:employees/a:emp[2]/a:name/text()', l_a_ns).getStringVal();
dbms_output.put_line('Emp Name: '||l_value);

-- Method 5
dbms_output.new_line;
dbms_output.put_line('Method 5');
l_index := 1;
WHILE l_xmltype.Existsnode('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns) > 0
LOOP
 l_empx := l_xmltype.extract('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns);
 l_col_ind := 1;
 WHILE l_empx.Existsnode('/a:emp/b:favorites/b:color[' || To_Char(l_col_ind) || ']', l_both_ns) > 0
 LOOP
    l_value := l_empx.extract('/a:emp/b:favorites/b:color[' || To_Char(l_col_ind) || ']/text()', l_both_ns).getStringVal();
    dbms_output.put_line('Color: '||l_value);
    l_col_ind := l_col_ind + 1;
 END LOOP;
 l_index := l_index + 1;
END LOOP;
END;

Running this script produces

Method 1
Emp Name: Scott
Emp Name: John
 
Method 2
Emp Name: Scott
 
Method 3
Emp Name: Scott
Emp Name: John
 
Method 4
Emp Name: John
 
Method 5
Color: red
Color: orange
Color: blue
Color: green

which is exactly the same output as the previous example. This is what we expected. The only call that did not have to change was the one for Method 2. DBMS_XMLDOM is pretty much ignores namespaces when performing operations so this does have useful situations. As Methods 1, 3, and 4 show, the namespace parm only needs to contain the URI information for namespaces referenced in the Xpath, not in the XML. All the namespaces information can be passed along, but it is not needed. Pretty simple, excluding the whole "wait, what namespace is that node in again?" issue.

Basic XML Parsing via PL/SQL

One question that comes up with some frequency on various OTN forums is "How do I parse this XML?" Depending upon where the XML is (PL/SQL or DB) and what version of Oracle, there are different options that can be used. In starting simple, I show some examples for parsing some basic XML via PL/SQL. This example shows some basic ways to parse XML.

DECLARE
l_doc       VARCHAR2(2000);
l_domdoc    dbms_xmldom.DOMDocument;
l_nodelist  dbms_xmldom.DOMNodeList;
l_node      dbms_xmldom.DOMNode;
l_value     VARCHAR2(30);

l_xmltype   XMLTYPE;
l_empx      XMLTYPE;
l_index     PLS_INTEGER;
l_col_ind   PLS_INTEGER;
BEGIN
l_doc := '<employees>
 <emp>
  <name>Scott</name>
  <favorites>
   <color>red</color>
   <color>orange</color>
  </favorites>
 </emp>
 <emp>
  <name>John</name>
  <favorites>
   <color>blue</color>
   <color>green</color>
  </favorites>
 </emp>
</employees>';

l_domdoc   := dbms_xmldom.newDomDocument(l_doc);

-- Method 1
dbms_output.put_line('Method 1');
l_nodelist := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_domdoc),'/employees/emp/name');
FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nodelist) - 1 LOOP
 l_node := dbms_xmldom.item(l_nodelist, cur_emp);
 l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
 dbms_output.put_line('Emp Name: '||l_value);
END LOOP;

-- Method 2
dbms_output.new_line;
dbms_output.put_line('Method 2');
l_nodelist  := dbms_xmldom.getelementsbytagname(l_domdoc, 'name');
-- get first item from list, could loop as shown above
l_node      := dbms_xmldom.item(l_nodelist, 0);
l_value    := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
dbms_output.put_line('Emp Name: '||l_value);

-- Done with DOMDocument examples, setup for XMLType based examples
dbms_xmldom.freeDocument(l_domdoc);
l_xmltype := XMLTYPE(l_doc);

-- Method 3
dbms_output.new_line;
dbms_output.put_line('Method 3');
l_index := 1;
WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0
LOOP
 l_value := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']/name/text()').getStringVal();
 dbms_output.put_line('Emp Name: '||l_value);
 l_index := l_index + 1;
END LOOP;

-- Method 4
dbms_output.new_line;
dbms_output.put_line('Method 4');
l_value := l_xmltype.extract('/employees/emp[2]/name/text()').getStringVal();
dbms_output.put_line('Emp Name: '||l_value);

-- Method 5
dbms_output.new_line;
dbms_output.put_line('Method 5');
l_index := 1;
WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0
LOOP
 l_empx := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']');
 l_col_ind := 1;
 WHILE l_empx.Existsnode('/emp/favorites/color[' || To_Char(l_col_ind) || ']') > 0
 LOOP
    l_value := l_empx.extract('/emp/favorites/color[' || To_Char(l_col_ind) || ']/text()').getStringVal();
    dbms_output.put_line('Color: '||l_value);
    l_col_ind := l_col_ind + 1;
 END LOOP;
 l_index := l_index + 1;
END LOOP;
END;

Running this script produces

Method 1
Emp Name: Scott
Emp Name: John
 
Method 2
Emp Name: Scott
 
Method 3
Emp Name: Scott
Emp Name: John
 
Method 4
Emp Name: John
 
Method 5
Color: red
Color: orange
Color: blue
Color: green

In PL/SQL, you basically have two methods. The first, and older method, is via dbms_xmldom/dbms_xslprocessor and the second is via XMLType. The first method uses DOMDocuments, DOMNodes, DOMNodelist, etc to slowly break down the XML and parse/traverse it. This is based on the W3C definition of a DOM. The second method uses XPath 1.0 syntax to quickly parse an XMLType and return either another XMLType fragment or a basic data type. My preference for parsing XML is via an XMLType as the code seems cleaner to write, understand, and maintain.

Methods 1 and 2 use the old style to parse the XML.
Method 1 used dbms_xmlprocessor to select all the nodes that match the XPath /employees/emp/name and then iterates over the list that is returned to print out all the names.
Method 2 uses dbms_xmldom to get all the nodes called "name" and simply prints the first one. It too could easily loop through all the name nodes since both operations return a DOMNodeList.

Methods 3 through 5 use XMLType to parse the XML.
Method 3 looks for and returns the same information as Method 1. This difference is that instead of returning a list of nodes that match the criteria, it goes out and gets each occurrence one at a time.
Method 4 is like Method 2 in that it returns the contents of a specific node directly, in this case, the name of the second employee "emp[2]/name"
Method 5 shows the name for each employee and their favorite colors. As the code shows, it extract each emp as an XMLType and then parses that XMLType to extract all the colors.

Gotchas:

• /text() refers to the text contents of the given node. Leaving this off returns the node itself (an XMLType).

• .getStringVal() and others, along with many examples can be found in the XMLType Operations section within the online Oracle Documentation.
  

• "ORA-30625: method dispatch on NULL SELF argument is disallowed" means your XPath returned returned nothing aka NULL and further operations failed. This is common when doing a .get*Val() on a .extract() that returned NULL. One way is to do a .ExistsNode before the .extract().get*Val()
  

XMLType - What is it?

While pondering my first meaningful post regarding what it should include and be limited to, I began pondering what an XMLType really is. A CLOB or VARCHAR2 is stored as characters. Numbers are stored as bits and bytes. Dates are an internal Oracle representation. An XMLType though is simply a system-defined opaque type per 11.2 documentation. Depending upon where the XMLType is used, it could be stored in various formats. In the database, it could be represented as a CLOB, binary XML, or object-relational storage. How PL/SQL represents an XMLType is unclear.

Oracle provides many functions to access the data in an XMLType, whether a member function such as .extract or via XMLTable which uses an XMLType. Since the introduction of XMLType in 9.2, the definition in the Oracle documentation has changed slightly over time. Whether this was just documentation clarification or a associated with an underlying structure change is a guess. I suspect the latter given Oracle continues to introduce new DB storage options in 11g.

So for now, I will accept an XMLType as any other basic data type within in Oracle, but I will still continue to wonder how it is represented in PL/SQL and how I can figure it out besides a memory dump. Future post will touch on how to access the data in a XMLType, whether in PL/SQL or the DB along with ways to build XML. For how XML can be stored in the DB, I defer to Marco's blog on that subject where possible.

Information on Data Cartridges (10.2 documentation), as mentioned by Marco in the comment.

Round 1

This is going to start out as a place to post common examples regarding SQL/XML, such as XMLTable and XQuery, along with any other XML DB information that I post about on the Oracle OTN forums. A scratchpad with common examples to build from. What this morphs into over time is a good question.

Why did I start this? I was rightfully prodded into starting this, but that was a good thing. Trying to find some of my previous posts to re-use as an example has become difficult.