Using XML 08: SELECT, Part 3a/3

Continune from previous post

XMLTable [4]

XMLTable decompose the result of an XQuery evaluation into relational rows and columns. Or: the XMLTABLE operator allows us to split the XML data into rows and project columns on to it [13]. You can query the result returned by the function as a virtual relational table using SQL.

XMLTABLE (<XQuery_string> <XMLTABLE_options>)
  |- <XMLTABLE_options>   => <XML_passing_clause> [COLUMNS <XML_table_colums>]
  |- <XML_passing_clause> => PASSING <expr> [AS <identifier>]
  |- <XML_table_colums>   => <column> [<datatype>|XMLTYPE] [PATH <string>] [DEFAULT <expr>]

where [4][5]

• <XQuery_string> is a literal string. XQuery_string is sometimes called the row pattern of the XMLTable call. It is a complete XQuery expression. The value of XQuery_string serves as input to the XMLTable function; it is this XQuery result that is decomposed and stored as relational data.
• The <expr> in the XML_passing_clause is an expression returning an XMLType or an instance of a SQL scalar data type (that is, not an object or collection data type) that is used as the context for evaluating the XQuery expression. You can specify only one expr in the PASSING clause without an identifier. Each expression expr can be a table or view column value, a PL/SQL variable, or a bind variables with proper casting. The result of evaluating each expr is bound to the corresponding identifier in the XQuery_string. If any expr that is not followed by an AS clause, then the result of evaluating that expression is used as the context item for evaluating the XQuery_string
• The optional COLUMNS clause defines the columns of the virtual table to be created by XMLTable. If you omit the COLUMNS clause, then XMLTable returns a row with a single XMLType pseudocolumn named COLUMN_VALUE. For each resulting column you must specify the column data type, which can be XMLType or any other data type. If the column data type is XMLType, then specify the XMLTYPE clause.
• If you omit PATH, then the XQuery expression column is assumed. For example:

          XMLTable(... 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.

In a more simple way

XMLTABLE (<row-pattern> 
          PASSING <XMLType>
          COLUMNS <column_name> <datatype> PATH <string>) 


Ok. Let's start

The row-pattern is '/alertlog'; the PASSING clause is......well, I try "SELECT * FROM MYTAB" (because Each expression expr can be a table or view column value)

SQL> SELECT * FROM XMLTable('/alertlog' PASSING (select * from mytab))

SQL> SELECT * FROM XMLTable('/alertlog' PASSING (select KEY_COLUMN, XML_COLUMN from mytab))

De

Ok. <exp> can be a table or view column name, but it must return an XML or an instance of a SQL scalar data type. So I have to try something simple. Because MYTAB table is

I specify just the MYTAB.XML_COLUMN column

SQL> SELECT * FROM XMLTable('/alertlog' PASSING (select XML_COLUMN from mytab))

Remember that if you omit the COLUMNS clause, then XMLTable returns a row with a single XMLType pseudocolumn named COLUMN_VALUE.

I want to know how many rows are returned, so I use thr ROWNUM pseudo column

SQL> col COLUMN_VALUE for a60
SQL> SELECT rownum, t.*
SQL> FROM XMLTable('/alertlog' PASSING (select XML_COLUMN from mytab)) t

Perfect. Now because I want all columns of MYTAB, the only way I have in my mind is to join the XMLTable with MYTAB, but I'not  not sure: I try

SQL> SELECT tk.key_column, tx.*
SQL> FROM XMLTable('/alertlog' PASSING (select XML_COULMN from mytab)) tx, mytab t;

I tried to use the the new syntax in Oracle: the "JOIN" clause. See the Appendix section at the end of this post.

In the previous SELECT, I specified "tk.key_column" and "tx.xml_column". I would like not specify the columns, so I change previous statement just aa little

SQL> SELECT * FROM XMLTable('/alertlog' PASSING t.XML_COLUMN) tx, mytab t

I want to try a stupid thing: I want to change the order between XMLTable and MYTAB. Why? Because in the exception ORA-00904, Oracle told me that the "T.XML_COLUMN" is an invalid identifier. It's strange, because I specified the "T" table just after the comma in the FROM clause.

 SQL> SELECT * FROM mytab t, XMLTable('/alertlog' PASSING t.XML_COLUMN) tx 

Great, but I want to show also the row numebr

 SQL> SELECT rownum, t.*, tx.*
 SQL> FROM mytab t, XMLTable('/alertlog' PASSING t.XML_COLUMN) tx 

As usual, one row is returned.

What if I change the XQuery_string/row-pattern, from /alertlog to /alertlog/msg?

(to be continued)




Appendix

If I use the JOIN syntax I had following error

It's new for me. The problem is the absence of ON clause. In fact if I use it with the condition "1=1", then it works

It non depend on the join that include the XMLTable: the ON condition is mandatory for the syntax. Well, as I told before, it's the first time I incurred in the errore. This is because I always used the ON.

Update

2017/Sep/11 - Update the Reference section adding [13] and [14]

References

[1] https://docs.oracle.com/database/122/SQLRF/EXTRACT-XML.htm#SQLRF00640
[2] https://docs.oracle.com/database/122/SQLRF/EXTRACTVALUE.htm#SQLRF06173
[3] https://docs.oracle.com/database/122/SQLRF/XMLQUERY.htm#SQLRF06209
[4] https://docs.oracle.com/database/122/SQLRF/XMLTABLE.htm#SQLRF06232
[5] https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB5121
[6] http://allthingsoracle.com/xquery-for-absolute-beginners-part-1-xpath/
[7] http://allthingsoracle.com/xquery-for-absolute-beginners-part-2-xquery-and-the-oracle-database/
[8] https://www.youtube.com/watch?v=n_dEb6myFH4
[9] https://www.youtube.com/watch?v=5d4fZVxFx20
[10] Pro Oracle SQL, pag 481 written by Morton, Osborne, Shamsudeen [Apress]
[11] http://www.ateam-oracle.com/using-xmltable-and-xmltype-to-extract-html-clob-data/
[12] http://www.oradev.com/xml_functions.html
[13] https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql
[14] XMLTable (PDF)

Part07 Index Part09