Tuesday, October 15, 2013

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.