OraSal: Using XML 10: Comparing functions

In this post, I want just compare the EXTRACT and EXTRACTVALUE functions with the new XMLQuery and XMLTable functions, respectively.

What I want to show is summarized in this table

Old Syntax	New Syntax
EXTRACT	XMLQuery
EXTRACTVALUE	XMLTabel

But wait a moment. Really, EXTRACTVALUE extracted the value while in the previous example the XMLTable was the same as XMLQuery, they is EXTRACT function.

The example for XMLTable operator not used all parameters. I not used the "COLUMN" option.

It's the time.

SQL> SELECT rownum, t.*, tx.* from mytab t,

2> XMLTable('/alertlog/msg[@pid="65370"]'

3> PASSING t.XML_COLUMN COLUMNS message path 'msg', text path 'txt') tx

Click to enlarge

Now you can say that XMLTable is like the EXTRACTVALUE function. So the table I wrote above is more correctly like this one:

Old Syntax	New Syntax
EXTRACT(<xmltype>, <row-pattern>)	XMLQuery(<row-pattern> PASSING <col-name> RETURNING CONTENT)
EXTRACTVALUE(<col-name>, <row-pattern>)	XMLTabel(<row-pattern> PASSING <expr> COLUMNS <column> PATH <string>)

At this point I'm (almost) ready to modify a statement wrote by Connor McDonald. Yes. My goal when I decided to write these posts was to understand a particular statement that McDonald wrote in him video [1]

The statement I used is a little different: I just modified some aliases and the way to quote the string (in green the modifications)

with xml
as (
select
table_name
, dbms_xmlgen.getxmltype(
q'[select
table_name
, partition_name
, high_value
from
dba_tab_partitions
where
table_name=']'||table_name||q'[']') as xml_output
from dba_tables t
where table_name in ('COMPOSITE_RNG_LIST')
and owner='FRTD_CORP'
)
select
extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition_name,
extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
from xml x,
table(xmlsequence(extract(x.xml_output, 'ROWSET/ROW'))) rws
order by 1,2,3;

Click to enlarge

What I don't discussed yet is the DBMS_XMLGEN.GETXMLTYPE. I'm going to investigate this function in the 3th part ("From rows/columns To XML" [3]) of this collection on XML.

For now, from [2], we know that "the PL/SQL package DBMS_XMLGEN creates XML documents from SQL query results". It should be enough in order to understand the meaning of the above statement.

Before to go ahead, I need to know the format of my XML source: it comes from CTE (Common Table Expression). So I run just this statement

Click to enlarge

Comparing this output with the one I used in my examples, you can build following map

McDonald case	My example
ROWSET	alertlog
ROW	msg
TABLE_NAME	txt
PARTITION_NAME	txt
HIGH_VALUE	txt

Based on all you saw untill now, I can rewrite the above statement like this one (in a red square the two core pieces)

with xml
as (
select
table_name
, dbms_xmlgen.getxmltype(
q'[select
table_name
, partition_name
, high_value
from
dba_tab_partitions
where
table_name= ']'||table_name||q'[']') as xml_output
from dba_tables t
where table_name in ('COMPOSITE_RNG_LIST')
and owner='FRTD_CORP'
)
select
rws.table_name
, rws.partition_name
, rws.high_value
from
xml x
, xmltable('/ROWSET/ROW'
PASSING
x.xml_output
COLUMNS
table_name PATH 'TABLE_NAME'
, partition_name PATH 'PARTITION_NAME'
, high_value PATH 'HIGH_VALUE') rws
order by 1,2,3;

Click to enlarge

Pay attention on the case of you are searching. If you remember, XML is case sensitive, so you must specify ROWSET, ROW and others in upper case. Infact if you run the previous statement using the search in lower case you found noting: