Using XML 07: SELECT, Part 2/3

Because the EXTRACT [1] and EXTRACTVALUE [2] functions are deprecated in 11gR2 [10], you have to use the XMLQuery [3] and XMLTable [4] (respectively).

XMLQuery [1]

XMLQUERY lets you query XML data in SQL statements. It takes an XQuery expression as a string literal, an optional context item, and other bind variables and returns the result of evaluating the XQuery expression.

The more simple syntax is

XMLQuery(XQuery_string PASSING expr RETURNING CONTENT)

where [1]

• XQuery_string is a complete XQuery expression, including prolog
• The expr is an expression returning an XMLType or an instance of a SQL scalar data type that is used as the context for evaluating the XQuery expression. 
• RETURNING CONTENT indicates that the result from the XQuery evaluation is either an XML 1.0 document or a document fragment conforming to the XML 1.0 semantics.

or in simple words

• XMLQueryis the search path
• "expr" is the XML column of the table 

Here there are 3 examples, so you can compare the output, with the one of EXTRACT function provided in the previous post

SQL> SELECT rownum, t.*, 
  2> XMLQuery('/alertlog' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t

SQL> SELECT rownum, t.*, 
  2> XMLQuery('/alertlog/msg' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t

SQL> SELECT rownum, t.*,
  2> XMLQuery('/alertlog/msg/txt' PASSING t.XML_COLUMN  RETURNING CONTENT) extract FROM mytab t

Here other two examples

As you can see, also if there is a little difference in the syntax between XMLQuery and EXTRACT function, the outptu is the same. The XMLQuery function returns the TAGs. The same thing of the EXTRACT function.

In the next post XMLTable


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] http://allthingsoracle.com/xquery-for-absolute-beginners-part-1-xpath/
[6] http://allthingsoracle.com/xquery-for-absolute-beginners-part-2-xquery-and-the-oracle-database/
[7] https://www.youtube.com/watch?v=n_dEb6myFH4
[8] https://www.youtube.com/watch?v=5d4fZVxFx20
[9] Pro Oracle SQL, pag 481 written by Morton, Osborne, Shamsudeen [Apress]
[10] http://www.oracle.com/technetwork/database/features/xmldb/xmlqueryoptimize11gr2-168036.pdf (PDF)

Part06 Index Part08