The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

lunedì, settembre 11, 2017

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))


        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)


        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.


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


        [10] Pro Oracle SQL, pag 481 written by Morton, Osborne, Shamsudeen [Apress]
        [14] XMLTable (PDF)

        Part07 Index Part09

        Nessun commento: