PL/SQL EXTRACTVALUE

One of the key features of PL/SQL is the ability to query and manipulate XML data using the EXTRACTVALUE function.

The EXTRACTVALUE function is used to extract a value from an XML document. It takes two arguments: the first argument is the XML document, and the second argument is an XPath expression that specifies the location of the value to be extracted.

Syntax

The syntax for the EXTRACTVALUE function is as follows:

EXTRACTVALUE(xml_document, xpath_expression)

Here, xml_document is the XML document from which the value is to be extracted, and xpath_expression is the XPath expression that specifies the location of the value to be extracted.

Example

For example, let’s say we have an XML document that looks like this:

<employees>
  <employee>
    <name>John Smith</name>
    <department>Marketing</department>
  </employee>
  <employee>
    <name>Jane Doe</name>
    <department>Human Resources</department>
  </employee>
</employees>

We can use the EXTRACTVALUE function to extract the name of the first employee in the document like this:

SELECT EXTRACTVALUE(xmltype('
<employees>
<employee>
<name>John Smith</name>
<department>Marketing</department>
</employee>
<employee>
<name>Jane Doe</name>
<department>Human Resources</department>
</employee>
</employees>'), '/employees/employee[1]/name') AS name FROM dual;

In this example, the XPath expression /employees/employee[1]/name specifies that we want to extract the name of the first employee in the XML document. The result of the query will be:

NAME
----------
John Smith

So, in summary, the PL/SQL EXTRACTVALUE function is used to extract values from an XML document using an XPath expression. It takes two arguments: the XML document and the XPath expression, and returns the value at the specified location in the document.