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.

giovedì, agosto 31, 2017

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


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

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

Nessun commento: