Enriching XMLType data using relational data – XQuery and fn:collection in action

One of my colleagues had an interesting challenge. In an Oracle Database he has a table that contains an XMLType column. The column contains XML documents with people collections. This data is retrieved and transformed inside the database using an XSLT stylesheet, to a format – for example XHTML – that can be served directly to an end user in a browser. However, the XMLType contains a country code element where the displayed data should show the name of the country. There is a lookup table that contains the country data; this table can be used to enrich the data in the XMLType. The question now was: what is the best moment in the processing pipeline (query => transform => output) to perform this enrichment. The definition of ‘best’ should include performance, scalability, programming effort and (infrastructural) complexity.

One of the options we discussed was enriching in the first stage of the pipeline, as part of the retrieval step. This would be done using an XQuery operation against the XMLType, producing another [enriched] XMLType that would be fed into the XSLT transformation. This article shows how that could be done. Using the experienced insights of my colleague Marco Gralike, I can even mention some performance considerations. We will be using the XQuery operation and the fn:collection function that was introduced in Oracle Database 11gR2. Other options – completely out of scope for this article – could include using a Service Bus or other middleware infrastructure.

To keep the setup simple, I have not created a table with XMLType column but instead an in line view that creates the XMLType records on the fly. This should make no difference for the final results:

with people as
( select xmltype('MARCEL

<address>EDISONBAAN</address>6030
LUCAS

<address>VOORWEG</address>6040
') as xml
from dual
)
select extract(xml,'*')
from people

This query creates that selects and returns the XMLType with people details:

More interesting things can be done with this XMLType of people, such as counting the number of PERSON elements:

and returning all COUNTRY elements:

With this last query, we touch upon the challenge: we do not want to have the Country Code values but instead the Country Names. The enrichment should result in an extended XMLType that includes the Country Name, using the values in the lookup table MY_COUNTRIES. To the rescue comes the fn:collection XQuery function. This function allows us to access data in some table or view in our database and bring that data as additional source into the XQuery performed by the XMLQuery function.

The MY_COUNTRIES table is a very simple table with just three columns – code, abbreviation and name – and rows for all the countries that are of relevance in our environment. The XQuery that will produce enriched XMLType instances can now be written as follows, using fn:collection to bring in the look up data:

with people as
( select xmltype('MARCEL

<address>EDISONBAAN</address>6030
LUCAS

<address>VOORWEG</address>6040
') as xml
from dual
)
select xmlquery( 'for $i in $p/PEOPLE
return {for $person in $i/PERSON
return (
{$person/NAME}
{$person/ADDRESS}

{for $j in fn:collection("oradb:/HR/MY_COUNTRIES")/ROW
where $j/CODE eq $person/COUNTRY
return ($j/NAME/text())}

)}
'
passing people.xml as "p"
returning content
)
from people

The results from running the query:

Performance Considerations

We have a solution that does what we need. But does it do so in a performant, scalable fashion? Marco’s explains:

XMLType is a datatype, not a storage definition. As of Oracle Database 11gR2, there are three storage options for XMLType in the database. The characteristics of the above query strongly depend on which storage option is used for recording the XMLTypes.

• XMLType Binary XML (Securefile)
• XMLType Object Relational
• XMLType CLOB (Basicfile)

See for more details on these options: http://www.liberidu.com/blog/2007/06/24/oracle-11g-xmltype-storage-options/

The performance of the proposed solution using fn:collection will not be good (Marco put it much stronger than that) when the storage option of the XMLType is CLOB and fine when XMLType Binary XML Securefile was used (as of 11.2.0.3). This distinction is the result of the way of parsing and handling intermediate results via pickler fetches in PGA with XMLType CLOB (very resource intensive), compared to the much more elegant and lean approach with XMLType Binary XML that uses query rewrite and bitwise access to the required data, optimized via the C-Kernel XQuery/SQL engine.

The storage option used for the XMLType column can be inspected with for example DBMS_METADATA.GET_DDL:

dbms_metadata.get_ddl(‘TABLE’,{table name between quotation marks and capitalized}, {username, optional for a user’s own objects})

for example:
select dbms_metadata.get_ddl(‘TABLE’,’EMP’, ‘SCOTT’) from dual;

Resources

Oracle Documentation on fn:collection.

Essential white papers:

http://www.oracle.com/technetwork/database-features/xmldb/overview/oracle-xmldb-11gr2-1974916.html):

Oracle XML DB : Choosing the Best XMLType Storage Option for Your Use Case (PDF) Jan 2010
Oracle XML DB : Best Practices to Get Optimal Performance out of XML Queries (PDF) Jan 2013