Xmltable with Oracle 11G

Using XMLTABLE and xquery to extract data from xml

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.

How to include a list of objects in Oracle's XMLTABLE?

You can use chained XMLTable calls:

select t1.name, t1.age, t2.some_number
from my_with_clause
cross join xmltable (
  '/root/person'
  passing xmltype(my_with_clause.my_xml)
  columns name varchar2(20) path 'name',
    age number path 'age',
    list_of_numbers xmltype path 'list_of_numbers/number'
) t1
cross join xmltable (
  '/number'
  passing t1.list_of_numbers
  columns some_number number path '.'
) t2;

NAME                        AGE SOME_NUMBER
-------------------- ---------- -----------
Miguel Martins               32           1
Miguel Martins               32           2
Another Person               19           3
Another Person               19           4

SQL Fiddle doesn't like that, but db<>fiddle does, and it works locally against 11gR2. (Actually SQL Fiddle is OK with a real table instead of a CTE...)

or

select t1.name, t1.age, t2.some_number
from my_with_clause
cross join xmltable (
  '/root/person'
  passing xmltype(my_with_clause.my_xml)
  columns name varchar2(20) path 'name',
    age number path 'age',
    list_of_numbers xmltype path 'list_of_numbers'
) t1
cross join xmltable (
  '/list_of_numbers/number'
  passing t1.list_of_numbers
  columns some_number number path '.'
) t2;

db<>fiddle

With this XML you could also do it with one XMLTable by starting from the numbers and then looking back up the nodes for the other data:

select t1.name, t1.age, t1.some_number
from my_with_clause
cross join xmltable (
  '/root/person/list_of_numbers/number'
  passing xmltype(my_with_clause.my_xml)
  columns name varchar2(20) path './../../name',
    age number path './../../age',
    some_number number path '.'
) t1;

NAME                        AGE SOME_NUMBER
-------------------- ---------- -----------
Miguel Martins               32           1
Miguel Martins               32           2
Another Person               19           3
Another Person               19           4

SQL Fiddle and db<>fiddle.

but your real (not-minimal) XML might not make that practical.

---

This works with tables with multiple rows too, not just a CTE or table with a single XML value to unpack.

If you can have a scenario where the list_of_names is missing or empty, and you still want to show the name/age, you can use an outer join instead of a cross join, but it needs an ugly on 1=1 clause. SQL Fiddle showing the cross join and left join for that kind of data, but I'd avoid that left-join approach if you can.

If you're on 12c or higher you can use outer apply instead of left join ... on 1=1, which is rather less offensive. (And if you don't have to worry about missing numbers, you can use cross apply instead of cross join as @Lukasz showed - doesn't seem to make a difference here.)

---

The ORA-19025 is interesting. SQL Fiddle is running Oracle Database 11g Express Edition Release 11.2.0.2.0. In Enterprise Edition 11.2.0.4 your code gets

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

instead, but it's the same issue; there are multiple number nodes under each person, and it doesn't know what to do with them, as the default data type - since you haven't specified datatypes everything is returned as strings. In my first version I'm using the same path but declaring that column as XMLType, so you get the numbers as an XML fragment like this in the first version:

<number>1</number><number>2</number>

or this in the second:

<list_of_numbers><number>1</number><number>2</number></list_of_numbers>

Those can then be consumed by the chained XMLTable call.

Using xmlnamesspaces clause in XMLTABLE (Oracle)

Your XML declares a namespace but none of the nodes explicitly use it, so your XPath doesn't need to either. So, you can declare it in your statement as the default namespace, not with an identifier:

select ackd.bicorbie
from doctest
cross join xmltable (
  xmlnamespaces (default 'urn:iso:std:iso:20022:tech:xsd:pain.002.001.03'),
  '/Document' 
  passing payload
  columns
    bicorbie varchar2(20) path 'CstmrPmtStsRpt/GrpHdr/InitgPty/Id/OrgId/BICOrBEI'
) ackd;

BICORBIE            
--------------------
BARCGB22

You can still declare it as "b" if you want, but then you have to include it as a prefix for every node:

select ackd.bicorbie
from doctest
cross join xmltable(
  xmlnamespaces ('urn:iso:std:iso:20022:tech:xsd:pain.002.001.03' as "b"),
  '/b:Document' 
  passing payload
  columns
    bicorbie varchar2(20) path 'b:CstmrPmtStsRpt/b:GrpHdr/b:InitgPty/b:Id/b:OrgId/b:BICOrBEI'
) ackd;

BICORBIE            
--------------------
BARCGB22

Using a default here is simpler.

Oracle XMLTABLE Function. How to merge nodes that have the same name?

You could make use of Oracle's support for xquery expressions in the PATH string, e.g.

SELECT xt.*
FROM   xml_t x,
       XMLTABLE (
           'breakfast_menu/food'
           PASSING x.xml_file_data
           COLUMNS
              names VARCHAR2 (4000) PATH 'string-join(name," ")',
              price VARCHAR2 (50) PATH 'price',
              calories VARCHAR2 (50) PATH 'calories') xt;  

Oracle XMLTable- fetching column from parent node

You are looking for ./parent_node, which is a <parent_node> under the current <child> node. And that doesn't exist.

You just need to go up a level:

parent_value NUMBER (10) PATH './../parent_value'

Demo with your CTE and just that added ../:

WITH xtbl AS (SELECT xmltype ('<root>
                    <parent>
                         <parent_id>1</parent_id>
                         <parent_value>10000</parent_value>
                         <child>
                              <child_id>11</child_id>
                              <other_value>1000</other_value>
                         </child>
                         <child>
                              <child_id>12</child_id>
                              <other_value>1000</other_value>
                         </child>
                    </parent>
                </root>') AS xcol FROM dual)
      SELECT myXmlTable.*
        FROM xtbl
             CROSS JOIN
             xmltable ('/root/parent/child'
                       PASSING xcol
                       COLUMNS child_id NUMBER (5) PATH 'child_id',
                               parent_value NUMBER (10) PATH './../parent_value') myXmlTable;

  CHILD_ID PARENT_VALUE
---------- ------------
        11        10000
        12        10000

---

Related Topics