Using XMLTABLE and xquery to extract data from xml

References:

https://stackoverflow.com/a/9976068/377141

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.

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.