Perl Cd Bookshelf [Electronic resources] نسخه متنی

PL/SQL XML Processing Techniques

After you get the privileges to run the XML PL/SQL packages, you can use them to process XML data. However, unlike in Oracle9i Database, you do not have to use these packages in certain cases because Oracle Database 10g has many SQL/XML features available that often are easier to use than the XML PL/SQL packages to process XML data. For example, you will not normally use DBMS_XSLTPROCESSOR to transform XML documents because the XMLType.transform() function provides the same functionality and is easier to use. Therefore, in this section, we discuss the PL/SQL XML processing techniques only if they are distinct from the SQL/XML functions.

Parsing XML

Parsing XML is an operation that reads XML documents, processes the XML documents, and provides programmatic access to the XML data.

In Oracle Database 10g, you can use either the DBMS_XMLPARSER package or the XMLType() constructor function to parse XML. The difference is that DBMS_XMLPARSER returns the parsed XML document as a DBMS_XMLDOM.DOMDocument object, while the XMLType() function returns parsed XML as an XMLType object. In order to process the parsed XMLType objects using DBMS_XMLDOM, you can use the DBMS_XMLDOM.newDOMDocument() function to create DBMS_XMLDOM.DOMDocument objects from XMLTypes objects.

The DBMS_XMLPARSER package in Oracle Database 10g provides three interfaces that allow you to parse XML documents from a string buffer, from a CLOB, or from a file that is stored in the local file system, in the XML DB Repository, or in a web server:

parse (p Parser, url VARCHAR2) Parses XML stored in the given URL or file and returns the built DOM document

parseBuffer(p Parser, doc VARCHAR2) Parses XML stored in the given string buffer

parseClob(p Parser, doc CLOB) Parses XML stored in the given CLOB

Since parsing from a string buffer or a CLOB inside the Oracle database is very straightforward, in the following sections, we only explain the techniques of how to parse XML from files using UTL_FILE_DIR, BFILE, and database URL types.

Parsing XML from Local File Systems

You can either set up UTL_FILE_DIR or use BFILE to parse XML documents from the local file system.

Setting Up UTL_FILE_DIR to Parse XML Documents

When you set up UTL_FILE_DIR to parse XML documents, you first need to use the following command to set up the database system parameter, UTL_FILE_DIR, to include the directory in which the XML documents are stored:

SQL> ALTER SYSTEM
SET UTL_FILE_DIR='D:\xmlbook\Examples\Chapter26\src\xml' SCOPE=SPFILE;

In this example, D:\xmlbook\Examples\Chapter26\src\xml is set to UTL_FILE_DIR. After you run this command as a SYS user, you have to restart the Oracle database to make this change take effect.

After you restart the Oracle database, you can parse XML documents by using DBMS_XMLPARSER. Within your PL/SQL procedure, you first have to initialize an XML parser by using the DBMS_XMLPARSER.newParser() function:

v_parser DBMS_XMLPARSER.parser;
…
v_parser := DBMS_XMLPARSER.newParser;

You can choose whether to validate XML against a DTD and whether to preserve whitespace in the parsed XML documents by specifying the parsing parameters, as follows:

DBMS_XMLPARSER.setValidationMode(v_parser, FALSE);
DBMS_XMLPARSER.setPreserveWhiteSpace(v_parser, TRUE);

To parse an XML file from UTL_FILE_DIR, you need to set the XML file directory specified in UTL_FILE_DIR to be the base URL of the XML parser by using DBMS_XMLPASER.setBaseURL() as follows:

v_dir VARCHAR2(200) :=
'D:\xmlbook\Examples\Chapter26\src\xml';
…
DBMS_XMLPARSER.setBaseDir(v_parser, v_dir);

Then, you can use the DBMS_XMLPARSER.parse(p Parser, url VARCHAR2) procedure to parse XML files. For example, you can parse a contact.xml file stored in this directory as follows:

DBMS_XMLPARSER.parse(v_parser, 'contact.xml');

You then can get the DBMS_XMLDOM.DOMDocument from the XML parser:

v_xmldoc := DBMS_XMLPARSER.getDocument(v_parser);

You can perform DOM operations on the returned DOM document and we will discuss how you can perform such operations later in this chapter.

After you perform DOM operations, you can write the XML DOM document to a CLOB, a string buffer, or a file by using the following procedures:

DBMS_XMLDOM.writetoBuffer() writes XML into a string buffer.

DBMS_XMLDOM.writetoClob() writes XML into a CLOB.

DBMS_XMLDOM.writetoFile() writes XML into a file in the local file system.

In the following example, the document is written to a CLOB whose content is displayed on the SQL*Plus screen and to an XML file named out.xml that is created in the directory specified in UTL_FILE_DIR:

DBMS_LOB.createtemporary(v_out,FALSE,DBMS_LOB.SESSION);
DBMS_XMLDOM.writetoClob(v_xmldoc, v_out);
DBMS_XMLDOM.writetoFile(v_xmldoc,v_dir||'/out.xml');
:out := v_out;
DBMS_LOB.freetemporary(v_out);

In summary, the following is the example PL/SQL program that parses an XML document stored in a local file system by using UTL_FILE_DIR:

SET LONG 100000
SET AUTOPRINT ON
VAR out CLOB
DECLARE
v_xmldoc DBMS_XMLDOM.DOMDocument;
v_parser DBMS_XMLPARSER.parser;
v_out CLOB;
v_dir VARCHAR2(200) :=
'D:\xmlbook\Examples\Chapter26\src\xml';
BEGIN
-- New parser
v_parser := DBMS_XMLPARSER.newParser;
-- Setting up the parsing parameters
DBMS_XMLPARSER.setValidationMode(v_parser, FALSE);
DBMS_XMLPARSER.setPreserveWhiteSpace(v_parser, TRUE);
-- The v_dir has to be a valid directory in the UTL_FILE_DIR
DBMS_XMLPARSER.setBaseDir(v_parser, v_dir);
-- Parsing the XML file
DBMS_XMLPARSER.parse(v_parser, 'contact.xml');
-- Getting the XML DOMDocument
v_xmldoc := DBMS_XMLPARSER.getDocument(v_parser);
-- Print out the result
DBMS_LOB.createtemporary(v_out,FALSE,DBMS_LOB.SESSION);
DBMS_XMLDOM.writetoClob(v_xmldoc, v_out);
 DBMS_XMLDOM.writetoFile(v_xmldoc,v_dir||'/out.xml');
:out := v_out;
DBMS_LOB.freetemporary(v_out);
END;
/

Compared to DBMS_XMLPARSER, the XMLType() function doesn’t allow files to be read from UTL_FILE_DIR. Moreover, by using DBMS_XMLPARSER, you can parse DTDs and use them to validate the XML documents, which is not possible with XMLType().

Using BFILE

In the Oracle database, you can create a database directory and use BFILE to read XML files from the local file system. After loading the data into BFILEs, you then get the data into CLOBs where you can parse them by using either DBMS_XMLPARSER or the XMLType() function.

To create a database directory, you need to log in as a SYS user and run the following commands:

CREATE DIRECTORY xmldir AS 'D:\xmlbook\Examples\Chapter26\src\xml';
GRANT READ ON DIRECTORY xmldir TO demo;

These two commands create a database directory called xmldir and grant the read privilege to the user demo.

To load XML documents using BFILE, you can use the following PL/SQL procedure after you have connected as demo/demo:

CREATE OR REPLACE FUNCTION BfileToClob(fname VARCHAR2) RETURN CLOB IS
fclob CLOB;
theBFile BFILE;
num NUMBER := 0;
src_offset NUMBER := 1;
dest_offset NUMBER := 1;
lang_context NUMBER := 1;
BEGIN
DBMS_LOB.createtemporary(fclob,FALSE,DBMS_LOB.SESSION);
theBFile := BFileName('XMLDIR',fname);
DBMS_LOB.fileOpen(theBFile);
DBMS_LOB.loadClobFromFile(dest_lob=>fclob,
src_bfile=>theBFile,
amount=>dbms_lob.getLength(theBFile),
dest_offset=>dest_offset,
src_offset=>src_offset,
bfile_csid=>0,
lang_context=>lang_context,
warning=>num);
DBMS_LOB.fileClose(theBFile);
RETURN fclob;
END;
/

In this procedure, a BFILE is opened in the XMLDIR database directory by using the BFileName() function. To ensure the character set conversion is properly performed, the current language context—the lang_context parameter—is set to 1 for the DBMS_LOB.loadClobFromFile() function.

After the XML document is loaded from a BFILE into a CLOB, you can use the DBMS_XMLPARSER.ParseCLOB() or XMLType() function to parse the document in Oracle Database 10g as follows:

SET AUTOPRINT ON
VAR out CLOB
DECLARE
v_xmldoc DBMS_XMLDOM.DOMDocument;
v_xmltype XMLType;
v_out CLOB;
BEGIN
v_xmltype := XMLType(BFiletoClob('contact.xml'));
v_xmldoc :=DBMS_XMLDOM.newDOMDocument(v_xmltype);
DBMS_LOB.createtemporary(v_out,FALSE,DBMS_LOB.SESSION);
DBMS_XMLDOM.writetoClob(v_xmldoc, v_out);
:out := v_out;
DBMS_LOB.freetemporary(v_out);
END;
/
show errors;

In this example, XMLType() parses the XML in the CLOB returned by the BFiletoClob() procedure.

If an XML document contains DTD references, both UTL_FILE_DIR and BFILE approaches might not work properly. For example, assume that you have an XML document, test01.xml, that refers to a DTD document called test01.dtd, as follows:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="?>
<!DOCTYPE SIGNATURE SYSTEM "test01.dtd">
<SIGNATURE>
<NAME>&AUTHOR_NAME;</NAME>
<COPYRIGHT>&COPYRIGHT;</COPYRIGHT>
<EMAIL>&AUTHOR_EMAIL;</EMAIL>
<LAST_MODIFIED>&LAST_MODIFIED;</LAST_MODIFIED>
<PHOTO SOURCE="FUN_IMG">Lovely Kevin</PHOTO>
</SIGNATURE>

Parsing this document using BFILE or UTL_FILE_DIR will return the ORA-31001: Invalid Resource Handle or Path Name “/test01.dtd” error.

To avoid this error, you can load the DTD files to the XML DB Repository and update the DTD URLs in the XML files to be the XMLDBURIs for the DTD files. Otherwise, you have to rewrite the EntityResolvers for the XML parsers. In Chapter 27, we will discuss how to create Java stored procedures to rewrite the EntityResolvers so that XML parsers can resolve reference URLs in XML by reading data from files in the local file system, from URLs, or even from database tables.

Parsing XML from URLs

To parse an XML document from a URL, you can leverage the UriType datatypes that are provided in the Oracle XML DB, such as HTTPUriType, DBUriType, and XDBUriType.

Using HTTPUriType

The following example parses an XML document from a web server by using HTTPUriType:

SET LONG 100000
SET AUTOPRINT ON
VAR out CLOB
DECLARE
v_xmldoc DBMS_XMLDOM.DOMDocument;
v_xmltype XMLType;
v_out CLOB;
BEGIN
 v_xmltype := sys.HTTPUriType.createURI(
 'http://otn.oracle.com/syndication/rss_otn_news.xml').getXML();
v_xmldoc :=DBMS_XMLDOM.newDOMDocument(v_xmltype);
DBMS_LOB.createTemporary(v_out,FALSE,DBMS_LOB.SESSION);
DBMS_XMLDOM.writetoClob(v_xmldoc, v_out);
:out:=v_out;
DBMS_LOB.freeTemporary(v_out);
END;
/

In this example, an HTTUriType object is created for the XML document with the URL http://otn.oracle.com/syndication/rss_otn_news.xml, as follows:

sys.HTTPUriType.createURI(
'http://otn.oracle.com/syndication/rss_otn_news.xml')

To get the XML document, the HTTPUriType.getXML() function is then used. The content of the XML document is returned in an XMLType, which is then used to create a DBMS_XMLDOM.DOMDocument object for further processing.

Using XDBUriType

A similar approach to parse an XML document stored in the XML DB Repository is to use XDBUriType. The following procedure assumes that you have loaded contact.xml into the XML DB repository’s /public directory.

SET LONG 100000
SET AUTOPRINT ON
VAR out CLOB
DECLARE
v_xmldoc DBMS_XMLDOM.DOMDocument;
v_xmltype XMLType;
v_out CLOB;
BEGIN
 v_xmltype :=
 SYS.XDBUriType.createUri('/public/contact.xml').getXML();
v_xmldoc :=DBMS_XMLDOM.newDOMDocument(v_xmltype);
DBMS_LOB.createtemporary(v_out,FALSE,DBMS_LOB.SESSION);
DBMS_XMLDOM.writetoClob(v_xmldoc, v_out);
:out := v_out;
DBMS_LOB.freetemporary(v_out);
END;
/

The XDBUriType object is created using the SYS.XDBUriType.createUri() function and the XML data is returned as an XMLType by using the XDBUriType.getXML() function.

Setting Up HTTP Proxy and User Authentication

If an XML document has URL references to external files outside the intranet, you need to set up the proxy server to retrieve them. Otherwise, you will get the ORA-29273: HTTP Request Failed error. To set up a proxy server, you can use the UTL_HTTP.SET_PROXY() procedure as follows:

BEGIN
UTL_HTTP.SET_PROXY('proxy.mycompany.com:80', 'mycompany.com');
END;
/

The first parameter is the proxy URL in host:port format. The second parameter includes the domains that can be accessed without the proxy server. The proxy will stay in effect for the current database connection session.

If the URL requires user authentication, you need to create the URL with the username and password in a format as follows:

[http://][user[:password]@]host[:port][/]

For example, contact.xml is created by user demo as a private resource in the /public directory in the XML DB Repository. To access its content, you need to log in as user demo. If the demo user’s password is demo and the HTTP port number for the XML DB Repository is 8080, you need to specify the URL to be http://demo:demo@localhost:8080/public/contact.xml. Otherwise, you will get an ORA-29273: HTTP Request Failed error. The following example shows how to parse an XML document from an HTTP server that requires user authentication:

SET LONG 100000
SET AUTOPRINT ON
VAR out CLOB
DECLARE
v_xmldoc DBMS_XMLDOM.DOMDocument;
v_xmltype XMLType;
v_out CLOB;
BEGIN
v_xmltype := sys.httpuritype.createuri(
'http://demo:demo@localhost:8080/public/contact.xml').getXML();
v_xmldoc :=DBMS_XMLDOM.newDOMDocument(v_xmltype);
DBMS_LOB.createtemporary(v_out,FALSE,DBMS_LOB.SESSION);
DBMS_XMLDOM.writetoClob(v_xmldoc, v_out);
:out:=v_out;
DBMS_LOB.freetemporary(v_out);
END;
/

An alternative approach that does not have the security issue is to use UTL_HTTP to get the XML file as follows:

set long 100000
set autoprint on
var out clob
declare
v_xmldoc DBMS_XMLDOM.DOMDocument;
v_xmltype XMLType;
v_out clob;
v_req UTL_HTTP.req;
v_resp UTL_HTTP.resp;
v_text VARCHAR2(32767);
BEGIN
v_req :=
UTL_HTTP.begin_request('localhost:8080/public/contact.xml');
UTL_HTTP.set_authentication(r=>v_req,
username=>'demo',
password=>'demo');
v_resp := UTL_HTTP.get_response(v_req);
UTL_HTTP.read_text(r=>v_resp,data=>v_text);
UTL_HTTP.end_response(v_resp);v_xmltype :=XMLType(v_text);
v_xmldoc :=DBMS_XMLDOM.newDOMDocument(v_xmltype);
DBMS_LOB.createtemporary(v_out,FALSE,DBMS_LOB.SESSION);
DBMS_XMLDOM.writetoClob(v_xmldoc, v_out);
:out:=v_out;
DBMS_LOB.freetemporary(v_out);
END;
/

Both the DBMS_XMLPARSER.PARSE() and XMLType() functions use UTL_HTTP to request the data from remote web servers. Therefore, the proxy setup and the user authentication setup are the same.

Parsing with DTDs

Similar to XML schemas, DTDs can be used to define the structure and metadata of XML documents, which then can be used to validate XML documents. While they are not supported to the same extent as XML schemas, you can control the use of DTDs in Oracle Database 10g using the following command:

Alter session set events '31156 trace name context forever, level 2';

This turns off the DTD parsing and validation when parsing XML in either the XMLType() functions or the DBMS_XMLPARSER XML parsing functions.

In addition, and distinct from XML schemas, DTDs can also define entities for an XML document, which have to be resolved before XML parsers can check the well-formedness of XML documents. In this case, you cannot turn off DTD parsing; therefore, DTD files must have a URL that can be resolved by the Oracle XML DB. In Oracle Database 10g, it should be an XML DB URI like /public/author.dtd pointing to a resource file stored in the Oracle XML DB Repository.

Processing XML Using DOM

After XML documents are parsed by DBMS_XMLPARSER or XMLType() constructor functions, the XML parser builds XML documents as in-memory object trees. Therefore, you can navigate the object tree by using the DOM APIs in the DBMS_XMLDOM package to retrieve XML data or to update the object tree by inserting or deleting XML elements or their content. In this section, we do not explain every DOM API but rather focus on discussing the solutions for some common problems that are encountered when dealing with XML using DOM in PL/SQL.

Dealing with XML Namespaces

XML namespaces are collections of names identified by URI references, which are then used on XML elements and attributes to qualify XML elements or attributes for different applications. The following example, which uses the DBMS_XMLDOM package, shows how you can create an XML element with a namespace in Oracle Database 10g:

CREATE OR REPLACE FUNCTION appendChild(p_doc xmltype)
RETURN XMLType AS
v_doc DBMS_XMLDOM.DOMDocument;
v_docn DBMS_XMLDOM.DOMNode;
v_lcn DBMS_XMLDOm.DOMNode;
v_nelem DBMS_XMLDOM.DOMNode;
v_elem DBMS_XMLDOM.DOMElement;
v_attr DBMS_XMLDOM.DOMAttr;
v_nattr DBMS_XMLDOM.DOMNode;
v_tx DBMS_XMLDOM.DOMText;
v_ntx DBMS_XMLDOM.DOMNode;
BEGIN
-- Create DOM document
v_doc := DBMS_XMLDOM.newDOMDocument(p_doc);
v_docn := DBMS_XMLDOM.makeNode(
DBMS_XMLDOM.getDocumentElement(v_doc));
-- Locate the element where the new element will be inserted
v_lcn := DBMS_XMLDOM.getLastChild(v_docn);
-- Create a new XML element with namespace
v_elem :=DBMS_XMLDOM.CreateElement(v_doc,
'my:notes',
'http://www.example.com');
-- Create the attribute to include namespace declaration
v_attr :=DBMS_XMLDOM.CreateAttribute(v_doc,'xmlns:my');
v_nattr := DBMS_XMLDOM.makeNode(v_attr);
DBMS_XMLDOM.setNodeValue(v_nattr, 'http://www.example.com');
-- Set the attribute to the XML element
v_attr := DBMS_XMLDOM.setAttributeNode(v_elem,
v_attr,'http://www.example.com' );
-- Insert Text Node to XML Element
v_nelem := DBMS_XMLDOM.makeNode(v_elem);
v_tx :=DBMS_XMLDOM.createtextnode(v_doc, 'Address is not complete.');
v_ntx:=DBMS_XMLDOM.makenode(v_tx);
v_ntx:= DBMS_XMLDOM.appendchild(v_nelem, v_ntx);
-- Insert the new element
v_nelem := DBMS_XMLDOM.insertBefore(v_docn, v_nelem, v_lcn);
RETURN p_doc;
END;
/
show errors;

This example shows that you can create XML elements or attributes qualified by namespaces in Oracle Database 10g by specifying the third parameter of the DBMS_XMLDOM.CreateElement() or DBMS_XMLDOM.CreateAttribute() function. In this sample, the new XML element is created with its namespace to be http://www.example.com:

 v_elem :=DBMS_XMLDOM.CreateElement(v_doc,
'my:notes',
'http://www.example.com');

However, according to the DOM specification, the namespace declarations are not added by default. Therefore, you need to add the namespace declaration attributes explicitly as shown in the following example, which adds an xmlns:my attribute to the my:note element:

 v_attr :=DBMS_XMLDOM.CreateAttribute(v_doc,'xmlns:my');
v_nattr := DBMS_XMLDOM.makeNode(v_attr);
DBMS_XMLDOM.setNodeValue(v_nattr, 'http://www.example.com');
-- Set the attribute to the XML element
v_attr := DBMS_XMLDOM.setAttributeNode(v_elem,
v_attr,'http://www.example.com' );

After the PL/SQL function is created, you then test it as follows:

set autoprint on
var out clob
BEGIN
SELECT appendChild(XMLType('<person>
<name> John Smith </name>
<address> XYZ street, CA 12345</address>
</person>')).getClobVal() INTO :out
FROM DUAL;
END;
/

The output of the procedure is the XML with the new element inserted, as follows:

<person>
<name> John Smith </name>
<my:notes xmlns:my="http://www.example.com"/>
<address> XYZ street, CA 12345</address>
</person>

Retrieving XML Data Using XPath

The UPDATEXML() function in Oracle Database 10g can update XML elements selected by XPaths in XMLTypes. However, instead of updating the original XML document, UPDATEXML() creates a new XML document with the updates. To edit and update the original XML document, you need to use DOM APIs provided by DBMS_XMLDOM.

In Chapter 10, we showed an example that selects XML elements using DBMS_XMLDOM.GetElementsByTagName() and updates their content. In this section, we extend this example on discussing how to select XML elements using three XPath functions in the DBMS_XSLPROCESSOR package and update the selected content using DBMS_XMLDOM. The following are the three XPath interfaces provided in DBMS_XSLPROCESSOR:

SelectNodes (n DOMNode, pattern VARCHAR2, namespace VARCHAR2) Selects nodes from a DOM tree that match the given XPath pattern. It returns the result as the DBMS_XMLDOM.NodeList object.

SelectSingleNodes (n DOMNode, pattern VARCHAR2, namespace VARCHAR2) Selects the first node as a DBMS_XMLDOM.DOMNode object from the DOM tree that matches the given XPath pattern.

ValueOf (n DOMNode, pattern VARCHAR2, value VARCHAR2, namespace VARCHAR2) Retrieves the value of the first node in a string from the DOM tree that matches the given XPath pattern.

To update XML elements using XPath, you need to first select the XML elements by using DBMS_XSLPROCESSOR.SelectNodes(). Before you use this interface, you need to get the DBMS_XMLDOM.DOMDocument object either parsed by DBMS_XMLPARSER or created using the DBMS_XMLDOM.newDocument(). Next, you need to get the document element and create a DOM node using DBMS_XMLDOM.MakeNode() that you can process:

v_doc DBMS_XMLDOM.DOMDocument;
v_ndoc DBMS_XMLDOM.DOMNode;
…
v_ndoc := DBMS_XMLDOM.makeNode(dbms_xmldom.getDocumentElement(v_doc));

Then, you get the first child of the document node, which will be the root of the DOM document:

v_fnode := DBMS_XMLDOM.getFirstChild(v_node);

Next, you can call DBMS_XSLPROCESSOR.SelectNodes() function with the document root node as the first parameter and the XPath as the second parameter, as follows:

v_nodeList := DBMS_XSLPROCESSOR.selectNodes(v_fnode, '/Txn/Data/@value');

A DOM node list will be returned from the DBMS_XSLPROCESSOR.SelectNodes() function. Then, you can iterate over each node to update the DOM object using the DBMS_XMLDOM.setNodeValue() function:

v_nl := DBMS_XMLDOM.getLength(v_nodeList);
v_sum := 0;
IF v_nl > 0 THEN
FOR j in 0..v_nl-1 LOOP
v_node := DBMS_XMLDOM.item(v_nodeList,j);
DBMS_XMLDOM.setNodeValue(v_node,'<new value>');
END LOOP;

Processing XML Using XSLT

In Oracle Database 10g, you can use XMLType.transform() to apply XSL stylesheets on XML documents, which is normally a process to transform XML documents. However, in this section, we discuss how you can leverage XSLT to process XML data. We compare the XSLT approach with the DOM approach. You will see that using XSLT to process XML data sometimes is much easier than using DOM.

The example is a process, which gets an input XML document containing a collection of data from an application transaction and needs to calculate the sum of all the data values. The input document is as follows:

<Txn>
<Data value="18.75"/>
<Data value="17.32"/>
<Data value="16.45"/>
<Data value="15.82"/>
</Txn>

Using DOM, you need to iterate over all the <Data> nodes, read out the numbers in the value attributes, and add them together. The sample code is as follows:

DECLARE
v_xmlbuf VARCHAR2(512);
v_parser DBMS_XMLPARSER.Parser;
v_xmldoc DBMS_XMLDOM.DOMDocument;
v_outbuf VARCHAR2(600);
v_node DBMS_XMLDOM.DOMNode;
v_fnode DBMS_XMLDOM.DOMNode;
v_nodeList DBMS_XMLDOM.DOMNodeList;
v_nl NUMBER;
v_value NUMBER;
v_sum NUMBER :=0;
BEGIN
v_xmlbuf := '<Txn><Data value="18.75"/><Data value="17.32"/>' ||
'<Data value="16.45"/><Data value="15.82"/></Txn>';
-- Initialize the XML parser
v_parser := DBMS_XMLPARSER.newParser;
-- Parse XML document
DBMS_XMLPARSER.parseBuffer(v_parser, v_xmlbuf);
v_xmldoc := DBMS_XMLPARSER.getDocument(v_parser);
v_node := DBMS_XMLDOM.makenode(v_xmldoc);
v_fnode := DBMS_XMLDOM.getfirstchild(v_node);
-- Select <Data> elements
v_nodeList:=DBMS_XSLPROCESSOR.selectNodes(v_fnode,
'/Txn/Data/@value');
v_nl := DBMS_XMLDOM.getLength(v_nodeList);
DBMS_OUTPUT.put_line('The length of the node list:'||v_nl);
-- Iterate the <Data> elements and sum the value attributes
v_sum:=0;
IF v_nl > 0 THEN
for j in 0..v_nl-1 loop
v_node := DBMS_XMLDOM.item(v_nodeList,j);
v_value := DBMS_XMLDOM.getNodeValue(v_node);
v_sum := v_sum+v_value;
END LOOP;
DBMS_OUTPUT.put_line('Sum='||v_sum);
ELSE
DBMS_OUTPUT.put_line('No node selected');
END IF;
END;
/
show errors;

The DOM operation requires parsing the XML document, selecting the <Data> elements, and iterating all the <Data> elements to calculate the sum of all the values. Instead of doing that, you can use the following XSL stylesheet, which can do the same job:

<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:value-of select="sum(Txn//Data/@v)"/>
</xsl:template>
</xsl:stylesheet>

Thus, the operation can be done in a single SQL command as follows:

SELECT XMLType('<Txn>
<Data value="18.75"/>
<Data value="17.32"/>
<Data value="16.45"/>
<Data value="15.82"/>
</Txn>').transform(XMLType('<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:value-of select="sum(Txn//Data/@value)"/>
</xsl:template>
</xsl:stylesheet>')) AS result
FROM DUAL;

Using XSLT, you don’t have to write programs to traverse DOM objects, and it is much more efficient. Therefore, XSLT is not limited to transforming XML documents. You sometimes can leverage its functionality, especially using XPath functions, to process XML data.