6.5 Building XML Documents from Relational Data
Oracle's version 9i provides several new SQL functions and PL/SQL packages that can be used to handle XML documents. The SQL functions process XMLType objects?for example, to ask for the existence of certain nodes, to extract fragments, or to create XMLType objects by using SQL queries and table data.
6.5.1 SQL Functions existsNode and extract
Two built-in SQL functions extract and existsNode handle XMLType objects in SQL statements. They behave similar to the methods of XMLType of the same name even if the signature is slightly different; the result is again an XMLType object. Both methods are directly usable in SQL statements, as shown in Listing 6.20.
Listing 6.20 extract and existsNode Example
SELECT extract(x.txt, '/Customer//Phone') FROM XMLTypeTab x WHERE existsNode(x.txt, 'Customer//Order') = 1 AND extract(x.txt, '/Customer/CNo/text()').getNumberVal() = 10
The principle is exactly the same as described earlier except that the XMLType object is passed as a parameter. Hence, there is nothing really new. The main benefit of these functions is that both can be used in function indexes, thus speeding up performance for queries like the one shown in Listing 6.20.
6.5.2 The SQL Function SYS_XMLGen
SYS_XMLGen is a standalone function that assumes one SELECT expression, the result of which is then converted into XML. The function converts a value into an XMLType object similar to XSU?that is, the structure is defined by a canonical mapping. Again, object views can be used to affect the structure of the XML document. But in contrast to XSU, only one expression is allowed in the query.
SELECT SYS_XMLGen(Lastname) FROM Customer WHERE CNo > 1
The result of this query consists of one XMLType object for each tuple. Each object represents a document like <LASTNAME> Luke </LASTNAME> putting a database value Luke into tags. Similar to XSU, the attribute names are directly taken as tags. In case of function calls or complex SELECT expressions, the tag <ROW> will be used by default. However, <ROW> can be renamed by calling SYS_XMLGenFormatType.createFormat within SYS_XMLGen, as shown in Listing 6.21.
Listing 6.21 SYS_XMLGen Example
SELECT SYS_XMLGen(c.Address.Zip || ' ' || c.Address.City,
SYS_XMLGenFormatType.createFormat('TOWN')
).getClobVal()
FROM MyCustomer c
WHERE CNo = 1
Listing 6.21 creates the XML document shown in Listing 6.22.
Listing 6.22 Example Output Using SYS_XMLGenFormatType
<?xml version="1.0"?> <TOWN> 12345 Bull </TOWN>
The getStringValue/getClobValue methods produce a string for an XMLType object. Without calling SYS_XMLGenFormatType, the result would be the XML document shown in Listing 6.23.
Listing 6.23 Example Output without SYS_XMLGenFormatType
<?xml version="1.0"?> <ROW> 12345 Bull </ROW>
Because the SELECT expression uses a concatenation as a function call, there is no direct column name that could be taken. Using SYS_XMLGenFormatType, a style sheet can be registered, too, to transform the resulting document afterwards.
The single argument used in SYS_XMLGen can be any expression?for example, an XMLType object, a function call, or an object-valued attribute. Despite the limitation to one expression, the function possesses the same power as XSU, if complex objects or object views are used. For example:
SELECT SYS_XMLGen(VALUE(x)) FROM MyCustomer x
The expression VALUE(x) selects an object of the complex object view MyCustomer: The object is now processed. This view determines the inner structure of the query result and thus the structure of the XML document.
If you pass an XMLType object into SYS_XMLGen, the function encloses the fragment with an element whose tag name is the default "ROW". Hence, the SYS_XMLGen function is useful for converting XMLType fragments (e.g., resulting from extraction) into a well-formed document. Thanks to SYS_XMLGen, the result receives an outer tagging:
SELECT SYS_XMLGen(x.doc.extract('/Customer/Phone'))
FROM XMLTypeTab x
The extract method returns for each tuple a fragment:
<PHONE> 012/3456 </PHONE> <PHONE> 023/4567 </PHONE>
consisting of several PHONE elements: There are several occurrences of <PHONE> in the document. SYS_XMLGen then puts <ROW> around the fragment (ROW because extract() is an invocation of a method):
<ROW> <PHONE> 012/3456 </PHONE> <PHONE> 023/4567 </PHONE> </ROW>.
Owing to the surrounding ROW tag, each tuple is now well formed with regard to XML.
6.5.3 The SQL Function SYS_XMLAgg
While SYS_XMLGen processes each tuple individually?each tuple becomes an XMLType object?SYS_XMLAgg is an aggregate function that takes the whole result and aggregates all the tuples to a single XMLType, enclosing it with a tag ROWSET. The tag ROWSET can again be renamed with SYS_XMLGenFormatType. As an example, in Listing 6.24 we combine SYS_XMLAgg with extract.
Listing 6.24 SYS_XMLAgg Example
SELECT SYS_XMLAgg(x.doc.extract('//Customer/Phone'),
SYS_XMLGenFormatType.createFormat('LIST')) .getClobVal()
FROM XMLTypeTab x
The extract method returns an XMLType fragment for each tuple i first of all:
<PHONE> phone 1 of tuple i </PHONE> <PHONE> phone 2 of tuple i </PHONE> . . .
that is, a list of PHONE elements. These fragments are aggregated by SYS_XMLAgg. The result is one XMLType object, as shown in Listing 6.25.
Listing 6.25 Example Aggregated Output
<LIST> <PHONE> phone 1 of tuple 1 </PHONE> <PHONE> phone 2 of tuple 1 </PHONE> . . . <PHONE> phone 1 of tuple 2 </PHONE> . . . </LIST>
The result concatenates all the phone numbers of all the tuples. The association between a phone number and its customer tuple is, however, now lost.
The functions SYS_XMLGen and SYS_XMLAgg can be combined, too: SYS_XMLGen yields an XMLType object for each tuple so that all those objects can be aggregated by SYS_XMLAgg, as shown in Listing 6.26.
Listing 6.26 Using SYS_XMLGen and SYS_XMLAgg Together
SELECT SYS_XMLAgg(SYS_XMLGen(CNo)).getClobVal() FROM Customer GROUP BY Cname
The GROUP BY query itself computes the set of customer numbers having the same name. Using SYS_XMLAgg the numbers of each group are concatenated to one single tuple, as shown in Listing 6.27.
Listing 6.27 Example Concatenated Output
<ROWSET> <CNO> 10 </CNO> <CNO> 20 </CNO> </ROWSET> <ROWSET> <CNO> 15 </CNO> <CNO> 25 </CNO> <CNO> 35 </CNO> </ROWSET>
6.5.4 PL/SQL Package DBMS_XMLGen
The PL/SQL package DBMS_XMLGen makes it possible to generate XML documents from query results in PL/SQL. PL/SQL is Oracle's main proprietary language for writing stored procedures. The result is a CLOB or an XMLType object. The queries can be quite complex and may use object-relational features such as objects, references, nested tables, and so on. Thus, the functionality is comparable to XSU, but there are some essential differences: It is not possible to store, modify, and delete XML documents into the database on the one hand. On the other hand, the performance of XSU is worse than DBMS_XMLGen, the functionality of which is integrated in the database kernel.
Listing 6.28 shows the principle of DBMS_XMLGen.
Listing 6.28 DBMS_XMLGen Example
DECLARE
qCtx DBMS_XMLGen.ctxHandle;
result CLOB;
BEGIN
qCtx := DBMS_XMLGen.newContext('SELECT . . . ');
result := DBMS_XMLGen.getXML(qCtx);
DBMS_XMLGen.closeContext(qCtx);
END;
DBMS_XMLGen.ctxHandle is a context class representing a query and controlling all the functions. A context is created with newContext by passing an SQL query. Using getXML, an XML document can be generated as a CLOB. Analogously, getXMLType returns an XMLType object.
The package DBMS_XMLGen has again several procedures and functions to affect the naming of tags:
setRowTag(ctx ctxHandle, name VARCHAR): Renames <ROW>
setRowSetTag(ctx ctxHandle, name VARCHAR): Renames <ROWSET>
and so on. Furthermore, the query result can be processed incrementally:
setMaxRows(ctx ctxHandle, n NUMBER): Restricts the tuples to be handled to n
setSkipRows(ctx ctxHandle, n NUMBER): Leaves out the first n tuples
getNumRowsProcessed(ctx ctxHandle) RETURN NUMBER: returns the number of tuples that have been processed so far
restartQuery(ctx ctxHandle): Executes the query again
Additional procedures are available to reflect the functionality known from XSU.
| Top |
