oracle - Using XMLTABLE and xquery to extract data from xml -

i have 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 xml want extract data in pl/sql. i'd use xmltable, since extract , extractvalue functions deprecated.

i able extract data using query:

select pers.initials,        pers.firstname     lsinitials,          lsfirstname         xmltable ('*:person' passing pxrequest     columns initials           path '*:initials',             firstname          path '*:firstname'     )  pers; 

i'm using wildcards namespaces since don't care abbreviations sending party using namespace, know exact path data anyway.

with code have 2 things puzzle me:

• according documentation on http://docs.oracle.com/database/121/sqlrf/functions268.htm#sqlrf06232 path should optional, however, remove path columns section, don't results anymore.

edit:

i found out when remove namespaces elements, , made them uppercase, works. seems column names need match xml elements names make work. didn't yet figure out how make work namespaced xml.

• the documentation notes "for each resulting column except ordinality column, must specify column data type", however, seems work fine without it. seems bit redundant specify columns , variables i'm fetching data into. idea if not specifying data types me 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         lsinitials,              lsfirstname                 xmltable ('*:person' passing pxrequest         columns initials           path '*:initials',                 firstname          path '*:firstname'         )  pers;       dbms_output.put_line(lsinitials);     dbms_output.put_line(lsfirstname);  end;                             

as per first question, the documentation linked has day omitting path:

the optional path clause specifies portion of xquery result addressed xquery expression string used column content.

if omit path, xquery expression column assumed. example:

(... columns xyz)

is equivalent to

xmltable(... columns xyz path 'xyz')

you can use different path clauses split xquery result different virtual-table columns.

the reason column xyz assumed 'xyz' because oracle, default, case insensitive (defaults all-caps). if had defined column "abcd" path value assumed 'abcd'

---

as second question specifying data types: if data you're extracting going text data, might able away not specifying data type.

however, if start dealing things dates, timestamps, floating point numbers, etc, may run issues. you'll either need manually convert them using to_* functions or can specify data types in column definitions. if don't, oracle free implicitly cast feels fit, may have unexpected consequences.