Using XMLTABLE and xquery to extract data from xml

Question

I have the following piece of XML:

<per:Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.something.com/2014/11/bla/webservice.xsd"
    xmlns:per="http://www.something.com/2014/11/bla/person">
    <per:Initials>E.C.</per:Initials>
    <per:FirstName>Erik</per:FirstName>
    <per:LastName>Flipsen</per:LastName>
    <per:BirthDate>1980-07-01</per:BirthDate>
    <per:Gender>Male</per:Gender>
</per:Person>

From this xml I want to extract some data in PL/SQL. I'd like to use XMLTABLE, since the EXTRACT and EXTRACTVALUE functions are deprecated.

I am able to extract the data using this query:

select pers.Initials,
       pers.Firstname
    into lsInitials,
         lsFirstname
    from
    XMLTABLE ('*:Person' passing pxRequest
    columns Initials           PATH '*:Initials',
            Firstname          PATH '*:FirstName'
    )  pers;

I'm using wildcards for the namespaces since I don't really care what abbreviations the sending party is using for the namespace, I know the exact path where to get my data anyway.

With this code I have two things that puzzle me:

• According to the documentation on http://docs.oracle.com/database/121/SQLRF/functions268.htm#SQLRF06232 PATH should be optional, however, as soon as I remove the PATH from the COLUMNS section, I don't get any results anymore.

Edit:

I found out that when I remove the namespaces for the elements, and made them uppercase, it works. So it seems like the column names need to match the xml elements names to make it work. I didn't yet figure out how to make it work with namespaced XML.

• The documentation also notes "For each resulting column except the FOR ORDINALITY column, you must specify the column data type", however, it seems to work fine without it. It also seems a bit redundant to specify it for the columns and for the variables I'm fetching the data into. Any idea if not specifying the data types could get me into trouble?

Runnable code sample:

SET SERVEROUTPUT ON;
DECLARE

    pxRequest xmltype := xmltype('<per:Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                    xsi:schemaLocation="http://www.something.com/2014/11/bla/webservice.xsd"
                                    xmlns:per="http://www.something.com/2014/11/bla/person">
                                    <per:Initials>E.C.</per:Initials>
                                    <per:FirstName>Erik</per:FirstName>
                                    <per:LastName>Flipsen</per:LastName>
                                    <per:BirthDate>1980-01-01</per:BirthDate>
                                    <per:Gender>Male</per:Gender>
                                </per:Person>');
    lsInitials  varchar2(100);
    lsFirstname varchar2(100);                                


begin

    select pers.Initials,
           pers.Firstname
        into lsInitials,
             lsFirstname
        from
        XMLTABLE ('*:Person' passing pxRequest
        columns Initials           PATH '*:Initials',
                Firstname          PATH '*:FirstName'
        )  pers;


    dbms_output.put_line(lsInitials);
    dbms_output.put_line(lsFirstname);

end;                            

Calvincalvina

Can anyone please answer my question related to this #74346123 – Barnstorm

Answer 1

As per your first question, the documentation you linked has this to day about omitting PATH:

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. For example:

(... COLUMNS xyz)

is equivalent to

XMLTable(... COLUMNS xyz PATH 'XYZ')

You can use different PATH clauses to split the XQuery result into different virtual-table columns.

The reason the column xyz is assumed to be 'XYZ' is because Oracle, by default, is case insensitive (defaults to all-caps). If you had defined your column as "aBcD" then the PATH value will be assumed to be 'aBcD'

---

As for your second question about specifying data types: if the data you're extracting is always going to be text data, you might be able to get away with not specifying a data type.

However, if you start dealing with things like dates, timestamps, floating point numbers, etc, then you may run into issues. You'll either need to manually convert them using the TO_* functions or you can specify their data types in the column definitions. If you don't, Oracle is free to implicitly cast it however it feels fit, which may have unexpected consequences.

Brooklime

Answer 2

References:

https://mcmap.net/q/1320207/-select-xml-element-value-in-oracle

How to parse xml by xmltable when using namespace in xml(Oracle)

It should work as expected if you load in the namespace elements in your xmltable:

select results    
from xmltable( 
  xmlnamespaces(
    default 'http://tempuri.org/',    
    'http://schemas.xmlsoap.org/soap/envelope/' as "soap" 
  ),    
  'soap:Envelope/soap:Body/addResponse' passing xmltype(v_xml) 
   columns results varchar(100) path './addResult')

From your example (you may also need to register your schema/namespace ahead of time, but that should be once):

select pers.Initials,
       pers.Firstname
    into lsInitials,
         lsFirstname
    from
    XMLTABLE (
      xmlnamespaces(
        default 'http://tempuri.org/',   
        'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
        'http://www.something.com/2014/11/bla/person' as "per" 
      ),
    passing pxRequest
    columns Initials           PATH '*:Initials',
            Firstname          PATH '*:FirstName'
    )  pers;

Things that used to work in previous versions of Oracle do not work in 11g+ with respect to XML, as from what I have seen, Oracle strongly verifies/types the input/output of XML operations where in previous versions you could run normal proper XQuery operations without namespace info.

Battles