لطفا منتظر باشید ...
Retrieving Using XMLType and SQL/XML Functions
So far, we have discussed various ways of generating XML from SQL data using the SQL/XML functions and the DBMS_XMLGEN package. In Oracle Database 10g, if you already have XML documents natively stored as XMLTypes, you can use the built-in XMLType member functions and the SQL/XML functions to retrieve and create XML. Table 10-3 summarizes the XMLType member functions and their respective XML processing functionalities.
Name | Functionality |
|---|---|
XMLType() createXML() createSchemaBasedXML() createNonSchemaBasedXML() | Create XMLTypes from XML data stored in VARCHAR2, CLOB, or other XMLTypes. |
existsNode() | Checks if the XML nodes or node sets specified by XPath exist. It returns 1 if nodes or node sets are found; otherwise, it returns 0. If the XPath string is NULL or the document is empty, then a value of 0 is returned; otherwise, 1 is returned. |
extract() | Extracts nodes or node sets based on the XPath expression and returns an XMLType instance containing the resulting node(s). |
isFragment() | Checks if the XMLType is an XML document fragment or a well- formed document. It returns 1 if the XMLType is an XML document fragment or a well-formed document. Otherwise, it returns 0. Determines if the XMLType instance corresponds to a well-formed document or a fragment. Returns 1 or 0 indicating if the XMLType instance contains a fragment or a well-formed document. |
getClobVal() getNumberVal() getStringVal() | Gets the CLOB, NUMBER, or String value in VARCHAR2, respectively, from the XMLType. You can only use getNumberVal() when the content of XMLType is numeric. |
transform() | Transforms the XML content in XMLType with the XSL stylesheet specified. |
toObject() | Casts the XMLType to other object types. |
isSchemaBased() getSchemaURL() getRootElement() getNamespace() | Checks the XML schema–related information of XMLType. If the XMLType is XML schema–based, isSchemaBased() returns TRUE. Then, getSchemaURL(), getRootElement(), and getNamespace() can be used to find out the registered XML schema URL, the defined root element and the namespace of the root element for the XMLType. |
isSchemaValidate() isSchemaValid() schemaValidation() setSchemaValidate() | Checks and updates the XML Schema validation status of XMLType. If the XMLType is valid against the XML schema, then isSchemaValidate() returns 1. The isSchemaValid() function returns the validation status of the XMLType against the supplied XML schema.To update the status of the XMLType, schemaValidation() validates the XMLType against an XML schema and updates the status to validated after the validation. If you validated the XMLTypes, you can update the status through the setSchemaValidate() procedure. |
We will not discuss all details of the functions listed in Table 10-3, because they are well covered by the online manual, Oracle XML Database Developer’s Guide. Instead, in the following section, we will explore frequently used functionality to help you solve some real problems.
Both the SQL/XML extension functions and XMLType member functions can be used, along with SQL commands such as SELECT, UPDATE, and INSERT to perform XML operations. This section discusses how you can extract, transform, and update the content when XML is stored natively in XMLTypes.
extract() and existsNode()
The XMLType.extract() object member function extracts nodes or node sets based on the passed XPath expression and returns an XMLType instance containing the resulting node or nodes. You need to specify the table name alias to qualify the object. Otherwise, you will get an error message as follows:
SQL> SELECT description.extract('//KEYWORD').isFragment()
FROM product;
SELECT description.extract('//KEYWORD').isFragment() FROM product
*
ERROR at line 1:
ORA-00904: "DESCRIPTION"."EXTRACT": invalid identifierIn this example from Chapter 8, the product table contains a description XMLType column.
There are corresponding SQL/XML extension functions with the same name as the XMLType member functions, including EXTRACT()and EXISTSNODE(). They are functionally the same except that they take an XMLType as their first parameter and normally do not require the use of qualified names, as are needed by the XMLType member functions. The following example illustrates using the EXTRACT() SQL/XML function:
SQL> SELECT EXTRACT(description,'//KEYWORD') FROM product;
EXTRACT(DESCRIPTION,'//KEYWORD')
----------------------------------------------
<KEYWORD>xdk</KEYWORD>
<KEYWORD>XML</KEYWORD>
EXTRACTVALUE()
EXTRACTVALUE()does not belong to the XMLType object. It is an Oracle-provided SQL/XML function that returns scalar content, such as numbers or strings, when passed an XPath expression. This function is quite convenient to use because it automatically gets the value of the child text element of the specified XML node. However, make sure the XPath points to an XML element with only a single child text element, otherwise, you will get the following error:
<DESCRIPTION>This Oracle XML Parser supports <KEY>DOM</KEY> and
<KEY>SAX</KEY> interfaces.</DESCRIPTION>
SQL> SELECT EXTRACTVALUE
(description,'//DESCRIPTION/text()') FROM product;
SELECT EXTRACTVALUE(description,'//DESCRIPTION/text()') FROM product
*
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node
The EXTRACTVALUE() function always returns a VARCHAR2 by default for string values. Therefore, the node value cannot be greater than 4K. If larger sizes are needed, you can use XMLType.extract() to get the document and use the XMLType getClobVal() to get the content out in CLOB. When the XMLType storage is based on an XML schema, at query compile time, EXTRACTVALUE() can automatically return the appropriate data type based on the XML schema information, such as in a CLOB, DATE, or NUMBER.
