Using XML 06: SELECT, Part 1/3

In the last two posts [3][4], I was shown how to insert a record (an XML document) in an XML column. Here I want to show how to select data inside an XML column

In order to SELECT data, you have to use some functions. Two of these are EXTRACT [1] and EXTRACTVALUE [2]

EXTRACT [1]

It applies to a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.

EXTRACT (<XMLType_instance>, <XPATH_string>)

I start using just EXTRACT(XML_COLUMN, '/alertlog')

The result is the entire XML content. This is because I chosen the root node. So, what I expect if I use EXTRACT(XML_COLUMN, '/alertlog/msg') is the EXTRACT column without the root node. And in fact:

A similar things I expect if I use EXTRACT(XML_COLUMN, '/alertlog/msg/txt'). I mean, the output should be only the "txt" element.

In all cases, only one row is returned (yes there is one row in the table).

EXTRACTVALUE [2]

The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node. The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.

EXTRACTVALUE (<XMLType_instance>, <XPATH_string>)

I have to follow the manual above: The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. This is why I had previous exception. If I specify just an element, then...

Also this, is a valid statement

As you see in this case, only onw row is returned. Anyway, the documentation says:

The EXTRACT (XML) function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLQUERY function instead. See XMLQUERY for more information [1]

The EXTRACTVALUE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function, or the XMLCAST and XMLQUERY functions instead. See XMLTABLE, XMLCAST, and XMLQUERY for more information [2]

In the next post. we see how to use the XMLQuery function in order to obtain the same result

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://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table.html
[4] https://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table-part-22.html

Part05 Index Part07