Implicit Uppercase in XMLTable

If you review the documentation for XMLTable, you may notice that within the COLUMNS clause, the PATH clause is optional.  The documentation states

The optional PATH clause specifies that the portion of the XQuery result that is addressed by XQuery expression string is to be used as the column content. If you omit PATH, then the XQuery expression column is assumed.

The example to show what the assumed PATH looks like after being added is:

COLUMNS xyz PATH 'XYZ'

Does that mean that the PATH is always the upper case version of the column name?  Let's see with a simple example.  I used the WITH clause simply to simulate a table for this example.

WITH INPUT_XML AS(
 SELECT XMLTYPE('<Document>
    <node1>1</node1>
    <Node1>2</Node1>
    <NODE1>3</NODE1></Document>') as XML_COL from dual
)
-- Care about the below
SELECT xt.*
 from INPUT_XML ix,
       XMLTABLE('Document'
          PASSING ix.XML_COL
          COLUMNS
          node1           VARCHAR(1)
      ) xt;

NODE1
-----
3

So it looks like it, but what about if we enclose the name in double quotes to make it case sensitive?

WITH INPUT_XML AS(
 SELECT XMLTYPE('<Document>
    <node1>1</node1>
    <Node1>2</Node1>
    <NODE1>3</NODE1></Document>') as XML_COL from dual
)
-- Care about the below
SELECT xt.*
 from INPUT_XML ix,
       XMLTABLE('Document'
          PASSING ix.XML_COL
          COLUMNS
          "node1"         VARCHAR(1)
      ) xt;
node1
-----
1

So the answer is, Oracle will uppercase the column name, unless you make the name case sensitive by enclosing it in double quotes.  I believe the best solution is to always include the PATH clause so you can be explicit on which node from the XML is needed to ensure a correct mapping/extraction. Don't leave it up to the people after you to guess what the code is doing and why it is not retrieving a value from the XML.

Passing XMLType By Reference

This post was inspired by the Daily PL/SQL Quiz for 22 August 2013 over on the PL/SQL Challenge.  That quiz was testing concepts related to setting OUT parameters in a called procedure, but one of the quizzes was performing some *evil*.  The procedure was changing a global VARCHAR2 variable that had also been passed to the procedure as an IN parameter.  When the procedure looked at the local IN variable, it saw the change.  This got me to wondering whether this would apply to an XMLType variables as well.
Per Subprogram Parameter Modes and Subprogram Parameter Aliasing with Parameters Passed by Reference it should, but why not test it to be sure.  So I setup a simple test on 11.1 to verify it

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
SQL> set serverout on
SQL> DECLARE
  2    g_xmltype   XMLTYPE;
  3  
  4    FUNCTION return_col_value(i_xml  IN XMLTYPE)
  5    RETURN VARCHAR2
  6    IS
  7       l_value   VARCHAR2(10);
  8    BEGIN
  9       g_xmltype := XMLTYPE('<root><c1>b</c1></root>');
 10       SELECT c1
 11         INTO l_value
 12         FROM XMLTable('/root'
 13                       PASSING i_xml
 14                       COLUMNS
 15                       c1  VARCHAR2(10) PATH 'c1');
 16  
 17       RETURN l_value;
 18    END return_col_value;
 19  BEGIN
 20    g_xmltype := XMLTYPE('<root><c1>a</c1></root>');
 21    dbms_output.put_line(return_col_value(g_xmltype));
 22  END;
 23  /
 
b
 
PL/SQL procedure successfully completed

Note that line 9 sets the global variable and the SELECT on line 10 reads from the local IN variable instead.  The function returns the value that was set on line 9 instead of what was set on line 20.  Had the function not been able to see the assignment on line 9, then the function would have returned "a" as that is what the XML looked like on line 20 when the function was called.  This confirms that the XMLType variable was passed by reference, as a by value passing would not have seen the change made on line 9.

You may be thinking, well all that was in the same anonymous block of code so it was easy for Oracle to figure that out and treat it by reference.  Following is another example, that would be more common to this situation, where the XMLType variable resides outside the block of code that is the function or block of code calling the function.

SQL> CREATE OR REPLACE PACKAGE hold_value
  2  IS
  3     g_xmltype   XMLTYPE;
  4  END;
  5  /
 
Package created
SQL> CREATE OR REPLACE FUNCTION return_col_value(i_xml  IN XMLTYPE)
  2  RETURN VARCHAR2
  3  IS
  4     l_value   VARCHAR2(10);
  5  BEGIN
  6     hold_value.g_xmltype := XMLTYPE('<root><c1>b</c1></root>');
  7     SELECT c1
  8       INTO l_value
  9       FROM XMLTable('/root'
 10                     PASSING i_xml
 11                     COLUMNS
 12                     c1  VARCHAR2(10) PATH 'c1');
 13  
 14     RETURN l_value;
 15  END return_col_value;
 16  /
 
Function created
SQL> BEGIN
  2    hold_value.g_xmltype := XMLTYPE('<root><c1>a</c1></root>');
  3    dbms_output.put_line(return_col_value(hold_value.g_xmltype));
  4  END;
  5  /
 
b
 
PL/SQL procedure successfully completed

Same result as before so the change made on line 6 was seen in the SELECT statement and returned by the function, even though the package variable was modified and not the local variable the SELECT statement was using.  So as above, Oracle is passing XMLType variables defined as IN by reference instead of by value.  This is in agreement with Oracle documention.

One final note.  Don't do something like this in production code.  This would be called a nasty side-effect and one not obvious unless you look closely at the code.

XMLType DOMDocument and pointers

Ran across this post on the OTN Forums last week.
Dynamically adding attribute based on PL/SQL function
The part that interested me was Odie's second post. If you look closely at his second post (first example) he basically does
=======================
xmltype := XML_text
DOMDocument := New Document (xmltype)
... modify DOMDocument
Free DomDocument
Output xmltype.getClobVal
 =======================
So with that example, he shows that even though the DOMDocument variable is being modified, the changes still show up in the XMLtype variable.  The only way that can be done is through the use of a pointer the two variables are using to reference the same memory.  Very interesting to me as it was something new.

XMLTYPE and DBMS_REDEFINITION

This post is based on the following OTN XML DB thread: Migrating XMLType from STORE AS CLOB to STORE AS BINARY XML.

When Oracle first introduced the XMLType datatype (9.2.0.3), the default storage mechanism for it was a CLOB (aka BasicFile LOB). Note: I'm ignoring Object Relational storage since it is a table based format instead of a single column. With the release of 11.1.0.6, Oracle added a new storage type of SECUREFILE BINARY XML but left the default storage as CLOB. With the release of 11.2.0.2, Oracle changed the default storage to be SECUREFILE BINARY XML (What's New in Oracle XML DB?).

So what are the options for going from CLOB to BINARY XML storage? One is via DBMS_REDEFINITION. This may be the best option as it allows for the redefinition of the table without any system downtime. The downside to this option is that you need to have free space equal to or greater than the size of the new table as both the old and new will exist at the same time in the system. Assuming you can, here is what a simple example looks like (borrowed from myself from the OTN thread)

SQL> CREATE TABLE HOLDS_XML  -- old table structure
  2    (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3     xml_col XMLTYPE)
  4  XMLTYPE xml_col STORE AS BASICFILE CLOB;
 
Table created
SQL> INSERT INTO holds_xml VALUES
  2  ('1',XMLTYPE('val1'));
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> CREATE TABLE HOLDS_XML_TMP  -- new table structure
  2    (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3     xml_col XMLTYPE)
  4  XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
 
Table created
SQL> exec DBMS_REDEFINITION.start_redef_table('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
 
SQL>    exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
SQL>    exec DBMS_REDEFINITION.finish_redef_table('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','HOLDS_XML')
  2  FROM   dual;
 
DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
snipped...
 XMLTYPE COLUMN "XML_COL" STORE AS SECUREFILE BINARY XML (
...snipped

As you can see from the above DDL, the column is now a SECUREFILE BINARY XML. This also means that all the XML in that column has been converted to the more efficient (and smaller storage requirements) BINARY XML.

While researching this post, I came across someone mentioning the use of an ALTER TABLE statement to switch the storage formats. Here is what happens on 11.1.0.6 if we use the above table with a row in it.

SQL> ALTER TABLE JASON.HOLDS_XML
  2     MODIFY (XML_COL)
  3       XMLTYPE COLUMN XML_COL STORE AS SECUREFILE BINARY XML;
 
Table altered
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','HOLDS_XML')
  2    FROM   dual;

DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
snipped...
 XMLTYPE COLUMN "XML_COL" STORE AS BASICFILE CLOB (
...snipped

So the command completed successfully but did nothing still the column still uses CLOB storage. It was worth a try at least.

There are also the standard methods of

• Exporting data, dropping table, creating new table, importing data
• Exporting data, creating new table, importing data, dropping old table, renaming new table
• Create new table, Insert Select From old table, dropping old table, renaming table

I'm sure there are plenty others, but all those method involve re-doing grants and privileges for the new table. Each has a place though, depending upon the situation and environment.

The downside to this is that once the table contains a SECUREFILE BINARY XML based XMLType column, you can no longer use DBMS_REDEFINITION as shown by

SQL> CREATE TABLE HOLDS_XML  -- new table structure
  2     (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3      xml_col XMLTYPE)
  4   XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
 
Table created
 
SQL> exec dbms_redefinition.can_redef_table( 'JASON', 'HOLDS_XML' );
 
begin dbms_redefinition.can_redef_table( 'JASON', 'HOLDS_XML' ); end;
 
ORA-12090: cannot online redefine table "JASON"."HOLDS_XML"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1766
ORA-06512: at line 2

The ORA-12090 means

An attempt was made to online redefine a table that is either a clustered table, AQ table, temporary table, IOT overflow table or table with FGA/RLS enabled.

I searched on My Oracle Support but did not turn up any reason why a BINARY XML storage would cause this issue. Hopefully it is a limitation Oracle removes in the future.

I think this post xml query hungs up with large xml response from utl_http request on the OTN Forums and the response nicely sums up what I mentioned in my Options for Slow Performance Parsing Large XML series. If you want good performance for parsing large XML that is generated external to Oracle and you are on 11g then you need to INSERT the data into an XMLTYPE column stored as securefile binary xml and then query against that data.