How To : Access database tables from XSLT context

In this post we’ll see how to access a database table (or view) in the context of an XSL transformation.

The key feature allowing that is the ability to query a table using a DBUri URI.
In SQL or PL/SQL, DBUris are managed via the DBUriType object and its methods (see DBUris: Pointers to Database Data), and over HTTP protocol via the DBUriServlet.
The URI address is an XPath-based string, just like the oradb scheme available in XQuery.

In XSLT, we can pass a DBUri URI to the document() function in order to retrieve data in XML format. There are however some restrictions.

 

1. Retrieving a single value (“lookup table” mode)

Let’s say we want to transform the following input XML document, and replace DEPTNO by the department name (DEPT.DNAME) in the process :

SQL> select xmlserialize(document
  2           xmlelement("ROWSET",
  3             xmlagg(
  4               xmlelement("ROW",
  5                 xmlforest(empno, deptno)
  6               )
  7             )
  8           )
  9           as clob indent
 10         ) input_doc
 11  from scott.emp;
 
INPUT_DOC
---------------------------------------------
<ROWSET>
  <ROW>
    <EMPNO>7369</EMPNO>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7499</EMPNO>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7521</EMPNO>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7566</EMPNO>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7654</EMPNO>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7698</EMPNO>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7782</EMPNO>
    <DEPTNO>10</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7839</EMPNO>
    <DEPTNO>10</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7844</EMPNO>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7900</EMPNO>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7902</EMPNO>
    <DEPTNO>20</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>7934</EMPNO>
    <DEPTNO>10</DEPTNO>
  </ROW>
</ROWSET>
 

then this stylesheet will do it :

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml"/>
  <xsl:template match="/ROWSET">
    <Employees>
      <xsl:apply-templates/>
    </Employees>
  </xsl:template>
  <xsl:template match="ROW">
    <Employee id="{EMPNO}">
      <xsl:attribute name="dept">
        <xsl:value-of select="document(concat('/oradb/SCOTT/DEPT/ROW[DEPTNO=',DEPTNO,']/DNAME'))"/>
      </xsl:attribute>
    </Employee>
  </xsl:template>
</xsl:stylesheet>

SQL> select xmlserialize(document
  2           xmltransform(
  3             xmlelement("ROWSET",
  4               xmlagg(
  5                 xmlelement("ROW",
  6                   xmlforest(empno, deptno)
  7                 )
  8               )
  9             )
 10           , xmltype(:xsldoc)
 11           )
 12           as clob indent
 13         )
 14  from scott.emp
 15  ;
 
XMLSERIALIZE(DOCUMENTXMLTRANSF
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<Employees>
  <Employee id="7369" dept="RESEARCH"/>
  <Employee id="7499" dept="SALES"/>
  <Employee id="7521" dept="SALES"/>
  <Employee id="7566" dept="RESEARCH"/>
  <Employee id="7654" dept="SALES"/>
  <Employee id="7698" dept="SALES"/>
  <Employee id="7782" dept="ACCOUNTING"/>
  <Employee id="7839" dept="ACCOUNTING"/>
  <Employee id="7844" dept="SALES"/>
  <Employee id="7900" dept="SALES"/>
  <Employee id="7902" dept="RESEARCH"/>
  <Employee id="7934" dept="ACCOUNTING"/>
</Employees>
 

 

A further analysis using SQL Trace shows the queries run internally by Oracle to resolve DBuris :

[...]
PARSING IN CURSOR #807373884 len=105 dep=1 uid=48 oct=3 lid=48 tim=2887942133 hv=3648565905 ad='2ac86828' sqlid='f79sn7zcrjcnj'
SELECT alias000$."DNAME" AS "DNAME" FROM "SCOTT"."DEPT" alias000$ WHERE 1 = 1 AND (alias000$."DEPTNO"=20)
END OF STMT
[...]
PARSING IN CURSOR #267924496 len=105 dep=1 uid=48 oct=3 lid=48 tim=2887955465 hv=1206980471 ad='2ac84c84' sqlid='5nfvubt3z23vr'
SELECT alias000$."DNAME" AS "DNAME" FROM "SCOTT"."DEPT" alias000$ WHERE 1 = 1 AND (alias000$."DEPTNO"=30)
END OF STMT
[...]
PARSING IN CURSOR #267924496 len=105 dep=1 uid=48 oct=3 lid=48 tim=2887963722 hv=3245323572 ad='2ac84578' sqlid='g2drxn70qzd9n'
SELECT alias000$."DNAME" AS "DNAME" FROM "SCOTT"."DEPT" alias000$ WHERE 1 = 1 AND (alias000$."DEPTNO"=10)
END OF STMT

 

And here’s the interesting part : even though the same values were apparently retrieved multiple times (for instance, 6 times for DEPTNO = 30), the cursor was actually fetched only once, meaning there’s somehow a caching mechanism.

SQL> select fetches, executions, loads, parse_calls, rows_processed
  2  from v$sql
  3  where sql_id = '5nfvubt3z23vr';
 
   FETCHES EXECUTIONS      LOADS PARSE_CALLS ROWS_PROCESSED
---------- ---------- ---------- ----------- --------------
         1          1          1           1              1
 

 

2. Retrieving multiple values (node-set)

Actually we can’t retrieve multiple rows in the same way, except for the whole table (or view).
For instance, the following works, it’ll get the content of the EMP table (no filter predicate) :

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="xml"/>
 <xsl:template match="/">
   <xsl:copy-of select="document('/oradb/SCOTT/EMP')"/>
 </xsl:template>
</xsl:stylesheet>

But this doesn’t because the corresponding query returns multiple rows and the result is not an XML document (single-rooted) but an XML fragment :

SQL> select xmltransform(
  2           xmlelement("dummy")
  3         , xmltype(
  4  q'!<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  5   <xsl:output method="xml"/>
  6   <xsl:template match="/">
  7     <xsl:copy-of select="document('/oradb/SCOTT/EMP/ROW[DEPTNO=10]')"/>
  8   </xsl:template>
  9  </xsl:stylesheet>!'
 10           )
 11         )
 12  from dual
 13  ;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing

Of course, we can always do this :
document(‘/oradb/SCOTT/EMP’)/EMP/ROW[DEPTNO=10]

We’ll get the expected result, but we won’t benefit from the SQL access predicate.
With this workaround, the whole table content is first converted to XML then an XPath filter is applied. For large tables, the performance may be disastrous.

 

3. Calling an Oracle sequence from XSLT?

Since we can query views through DBUris, why not trying to query an Oracle sequence wrapped in a view?
Let’s see what we can and cannot do.

First of all we need a function to encapsulate the sequence call (because a sequence cannot appear directly in the SELECT list of a view) :

SQL> create sequence my_seq;
 
Sequence created
 
SQL> create or replace function get_my_seq return integer is
  2  begin
  3   return my_seq.nextval;
  4  end;
  5  /
 
Function created
 

Then the view is simply :

SQL> create or replace view my_seq_v as
  2  select get_my_seq() as nval from dual;
 
View created
 

And a simple usage :

SQL> select xmltransform(
  2           xmltype('<dummy/>')
  3         , xmltype(
  4  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  5   <xsl:output method="xml"/>
  6   <xsl:template match="/">
  7    <xsl:copy-of select="document(''/oradb/DEV/MY_SEQ_V/ROW/NVAL'')"/>
  8   </xsl:template>
  9  </xsl:stylesheet>'
 10           )
 11         )
 12  from dual
 13  ;
 
XMLTRANSFORM(XMLTYPE('<DUMMY/>
---------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<NVAL>1</NVAL>
 
SQL> /
 
XMLTRANSFORM(XMLTYPE('<DUMMY/>
---------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<NVAL>2</NVAL>
 

So far, so good. We’re calling it only once in the XSL transformation.

But see what happens for multiple calls (in a template for example) :

SQL> select xmltransform(
  2           xmltype('<root><item/><item/><item/></root>')
  3         , xmltype(
  4  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  5   <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
  6   <xsl:template match="//item">
  7    <ROW>
  8     <xsl:attribute name="id">
  9      <xsl:value-of select="document(''/oradb/DEV/MY_SEQ_V/ROW/NVAL'')"/>
 10     </xsl:attribute>
 11    </ROW>
 12   </xsl:template>
 13  </xsl:stylesheet>'
 14           )
 15         )
 16  from dual
 17  ;
 
XMLTRANSFORM(XMLTYPE('<ROOT><I
--------------------------------------------------------------------------------
 
<ROW id="3"></ROW>
<ROW id="3"></ROW>
<ROW id="3"></ROW>
 

The same value is assigned to all three nodes, and the sequence was incremented only once. Sounds very much like a “subquery caching”.

Let’s add some basic logging in the function to see what happens :

SQL> create or replace function get_my_seq return integer is
  2  begin
  3   dbms_output.put_line('Next value = '||to_char(my_seq.nextval));
  4   return my_seq.currval;
  5  end;
  6  /
 
Function created
 
SQL> set serveroutput on
SQL> select xmltransform(
  2           xmltype('<root><item/><item/><item/></root>')
  3         , xmltype(:xsldoc)
  4         )
  5  from dual;
 
XMLTRANSFORM(XMLTYPE('<ROOT><I
--------------------------------------------------------------------------------
 
<ROW id="4"></ROW>
<ROW id="4"></ROW>
<ROW id="4"></ROW>
 
 
Next value = 4
 

Same behaviour : three times the same value, only one increment.

Let’s try something else that will force “materializing” the view :

SQL> create or replace view my_seq_v as
  2  select get_my_seq() as nval
  3  from dual
  4  where rownum = 1;
 
View created
 
SQL> select xmltransform(
  2           xmltype('<root><item/><item/><item/></root>')
  3         , xmltype(:xsldoc)
  4         )
  5  from dual;
 
XMLTRANSFORM(XMLTYPE('<ROOT><I
--------------------------------------------------------------------------------
 
<ROW id="5"></ROW>
<ROW id="5"></ROW>
<ROW id="5"></ROW>
 
 
Next value = 5
Next value = 6
Next value = 7

That’s better. Now the sequence is incremented the expected number of times. But we’re still getting the same value in the XML output.

One possible solution to circumvent the caching mechanism is to generate a different URI for each iteration. We can do that by using a predicate (that will always evaluate to true) based on the position() function :

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
 <xsl:template match="/">
  <xsl:for-each select="//item">
  <ROW>
    <xsl:value-of select="document(concat('/oradb/DEV/MY_SEQ_V/ROW[RN&lt;=', position(), ']/NVAL'))"/>
  </ROW>
  </xsl:for-each>
 </xsl:template>
</xsl:stylesheet>

 
Associated with this modified view definition (so that RN <= position() is always true), we finally get the expected output :

create or replace view my_seq_v as
select get_my_seq() as nval
     , 1 as rn 
from dual;

SQL> set define off
SQL> select xmltransform(
  2           xmltype('<root><item/><item/><item/></root>')
  3         , xmltype(
  4  q'!<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  5   <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
  6   <xsl:template match="/">
  7    <xsl:for-each select="//item">
  8    <ROW>
  9      <xsl:value-of select="document(concat('/oradb/DEV/MY_SEQ_V/ROW[RN&lt;=', position(), ']/NVAL'))"/>
 10    </ROW>
 11    </xsl:for-each>
 12   </xsl:template>
 13  </xsl:stylesheet>!'
 14           )
 15         )
 16  from dual
 17  ;
 
XMLTRANSFORM(XMLTYPE('<ROOT><I
--------------------------------------------------------------------------------
 
<ROW>8</ROW>
<ROW>9</ROW>
<ROW>10</ROW>