We have covered the basic techniques of generating XML from SQL data in object-relational tables and XMLTypes. In this section, we discuss how you can process the XML documents before passing them to applications. Specifically, we discuss how to convert XML fragments into XML documents and how to use DBMS_XMLDOM to create a DOM during XML document generation. PL/SQL transformations are discussed in Chapter 26.
XMLType operations may result in creating XMLTypes containing XML fragments instead of XML documents. Since you cannot insert an XML fragment into XMLType columns or tables, you need to use the following function to check whether or not the current XML type is an XML fragment:
SQL> INSERT INTO temp_emp
SELECT XMLCONCAT(XMLELEMENT("Email",email),
XMLELEMENT("Name", first_name||' '||last_name))
FROM employees
WHERE ROWNUM<3;
insert into temp_emp
*
ERROR at line 1:
ORA-19010: Cannot insert XML fragments
The following query checks if the result of the SQL/XML function is an XML document fragment:
SQL> SELECT XMLCONCAT(XMLELEMENT("Email",email),
2 XMLELEMENT("Name", first_name||' '||last_name)).isFragment()
3 FROM employees
4 WHERE ROWNUM<2;
XMLCONCAT(XMLELEMENT("EMAIL",EMAIL),
XMLELEMENT("NAME",FIRST_NAME||''||LAST_NAME)
-----------------------------------------------------------------------
1
The result of the preceding query shows that the XMLType is an XML fragment. Normally, such XML fragments do not meet the single root element requirement for XML documents. Therefore, you need to create a well-formed XML document using XMLELEMENT() before instering it into XMLTypes as follows:
INSERT INTO temp_emp
SELECT XMLELEMENT"RESULT",
XMLCONCAT(XMLELEMENT("Email",email),
XMLELEMENT("Name", first_name||' '||last_name)))
FROM employees
WHERE ROWNUM<2;
As discussed in Chapter 2, the Document Object Model (DOM) is a set of standard APIs dealing with XML as a tree-type memory object. You can use the DOM APIs to delete, insert, and update XML elements, nodes, and attributes. In Oracle Database 10g, you can use the DBMS_XMLDOM package to perform these functions. From Chapter 2, you should be already familiar with the DOM operations, so the following examples show how these are performed from PL/SQL.
In Oracle Database 10g, you do not need to reparse an XML document if it is stored as an XMLType. There are two functions in the DBMS_XMLDOM package that directly support XMLType DOM operations:
FUNCTION NEWDOMDOCUMENT(xmldoc IN SYS.XMLType) RETURN DOMDOCUMENT; FUNCTION GETXMLTYPE(doc IN DOMDOCUMENT) RETURN SYS.XMLType;
As a result, you can call DOM functions by creating an XMLType, using its construction function, and then passing it to DBMS_XMLDOM.NEWDOMDOCUMENT() as an input. After DOM processing, you can get the result in an XMLType by using the DBMS_XMLDOM.GETXMLTYPE() function. Here is an example of an update operation:
CREATE OR REPLACE PROCEDURE updateXMLElement(p_doc IN OUT XMLTYPE,
p_ename IN VARCHAR2,
p_content IN VARCHAR2) AS
v_item NUMBER;
v_doc DBMS_XMLDOM.DOMDOCUMENT;
v_ndoc DBMS_XMLDOM.DOMNODE;
v_nlist DBMS_XMLDOM.DOMNODELIST;
v_node DBMS_XMLDOM.DOMNODE;
v_elem DBMS_XMLDOM.DOMELEMENT;
v_nelem DBMS_XMLDOM.DOMNODE;
v_text DBMS_XMLDOM.DOMTEXT;
v_ntext DBMS_XMLDOM.DOMNODE;
BEGIN
-- Create DOM Object
v_doc := DBMS_XMLDOM.NEWDOMDOCUMENT(p_doc);
v_ndoc :=DBMS_XMLDOM.MAKENODE(DBMS_XMLDOM.GETDOCUMENTELEMENT(v_doc));
-- Select the DOM Nodes
v_nlist:= DBMS_XMLDOM.GETELEMENTSBYTAGNAME(v_doc,p_ename);
IF DBMS_XMLDOM.GETLENGTH(v_nlist) > 0 THEN
DBMS_OUTPUT.PUT_LINE('Update the: '||p_ename||' elements.');
FOR v_item IN 0..DBMS_XMLDOM.GETLENGTH(v_nlist) LOOP
v_node := DBMS_XMLDOM.ITEM(v_nlist, v_item);
v_ntext := DBMS_XMLDOM.GETFIRSTCHILD(v_node);
DBMS_OUTPUT.PUT_LINE(DBMS_XMLDOM.GETNODEVALUE(v_ntext));
DBMS_XMLDOM.SETNODEVALUE(v_ntext, p_content);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('No '||p_ename||' element in the current
document.');
END IF;
-- Free resources
DBMS_XMLDOM.FREEDOCUMENT(v_doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exceptions during the process');
END;
In the example, the DBMS_XMLDOM.DOMDOCUMENT() creates the DOMDOCUMENT from the XMLType input p_doc. Then, a set of DOM operations is performed, which include using the DBMS_XMLDOM.GETELEMENTBYTAGNAME() function to get the XML elements with name p_ename for the updates and iterating each XML element item to update the content using the DBMS_XMLDOM.SETNODEVALUE().
Next, the created procedure can be used to update the telephone number for an employee with EMPLOYEE_ID of 199. The original document contains the following:
<ROWSET>
<ROW>
<EMPLOYEE_ID>199</EMPLOYEE_ID>
…
<PHONE_NUMBER>650.507.9844</PHONE_NUMBER>
…
</ROW>
Then, run the updateXMLElement() function:
DECLARE
v_doc XMLType;
BEGIN
-- Create Sample XML Document
SELECT XMLTYPE(DBMS_XMLGEN.GETXML('SELECT * FROM employees
WHERE employee_id=199'))
INTO v_doc FROM dual;
-- Update the Element
updateXMLElement(v_doc,'PHONE_NUMBER','650.506.9181');
-- Print out the content
SELECT v_doc.getClobVal() INTO :result FROM dual;
END;
The result is the XML document containing the updated phone number:
<ROWSET> <ROW> <EMPLOYEE_ID>199</EMPLOYEE_ID> … <PHONE_NUMBER>650.506.9181</PHONE_NUMBER> … </ROW> </ROWSET>
If multiple employees were selected, all of them would be updated. You can try the sample yourself by updating the WHERE clause in the SQL statement.
If you have two XML documents and want to merge them together, you can use the following functions, which use DBMS_XMLDOM:
FUNCTION importNode(doc DOMDOCUMENT, importednode DOMNODE, deep BOOLEAN) RETURN DOMNODE; FUNCTION appendChild(n DOMNODE, newChild IN DOMNODE) RETURN DOMNODE;
For example, if you create one XML document containing the department information and want to merge this document with one containing the employees of this department, you could use the following code:
SQL> SET AUTOPRINT ON
SQL> VAR RESULT CLOB
SQL> DECLARE
v_tdoc XMLType;
v_tsubdoc XMLType;
v_result CLOB;
v_doc DBMS_XMLDOM.DOMDOCUMENT;
v_subdoc DBMS_XMLDOM.DOMDOCUMENT;
v_doc_elem DBMS_XMLDOM.DOMELEMENT;
v_subdoc_elem DBMS_XMLDOM.DOMELEMENT;
v_node DBMS_XMLDOM.DOMNODE;
v_impnode DBMS_XMLDOM.DOMNODE;
BEGIN
-- Create the Main XML document
SELECT XMLType(DBMS_XMLGEN.GETXML('SELECT * FROM departments
WHERE department_id =20'))
INTO v_tdoc FROM dual;
-- Create the Sub XML document
SELECT XMLType(DBMS_XMLGEN.GETXML('SELECT * FROM employees
WHERE department_id = 20'))
INTO v_tsubdoc from dual;
-- Merge the documents
v_doc := DBMS_XMLDOM.NEWDOMDOCUMENT(v_tdoc);
v_subdoc :=DBMS_XMLDOM.NEWDOMDOCUMENT(v_tsubdoc);
v_doc_elem := DBMS_XMLDOM.GETDOCUMENTELEMENT(v_doc);
v_subdoc_elem := DBMS_XMLDOM.GETDOCUMENTELEMENT(v_subdoc);
v_impnode := DBMS_XMLDOM.IMPORTNODE(v_doc,
DBMS_XMLDOM.MAKENODE(v_subdoc_elem), true) ;
v_node := DBMS_XMLDOM.APPENDCHILD(
DBMS_XMLDOM.MAKENODE(v_doc_elem), v_impnode);
--v_node := DBMS_XMLDOM.APPENDCHILD(
-- DBMS_XMLDOM.MAKENODE(v_doc_elem),
-- DBMS_XMLDOM.MAKENODE(v_subdoc_elem));
DBMS_LOB.CREATETEMPORARY(v_result,true,DBMS_LOB.SESSION);
DBMS_XMLDOM.WRITETOCLOB(v_doc,v_result);
SELECT v_result INTO :result FROM dual;
DBMS_LOB.FREETEMPORARY(v_result);
END;
This example uses the DBMS_XMLDOM.IMPORTNODE() and DBMS_XMLDOM.APPENDCHILD() functions to create the merged document. This is the suggested and standards-compliant way to add content from one document to another via the DOM. We explore the DBMS_XMLDOM package further in Chapter 26.