Home > HowTo, XSLT > How To : Access database tables from XSLT context

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>
 

About these ads
Categories: HowTo, XSLT Tags: , , ,
  1. Paul Duncan
    April 10, 2013 at 13:30

    Pollock01 here – from the Oracle Forums – I am AMAZED! I didn’t think it was possible to access the db from XSLT. I thought we had to use XQuery for that (the deprecated ora:view and current oradb:doc/collection). This is a major game changer for my XML Loader framework!! I’m gonna try to play with this a bit – thanks SO MUCH!

  2. October 10, 2013 at 01:58

    Do you know if it is possible to use DBUris to access the metadata for a column. If so, would I be correct to assume that the DBUri would be structured as schema/table/column ?

    • October 10, 2013 at 16:50

      Sure. All you have to do is build a path expression targetting the proper dictionary view, for example :

      ‘oradb/PUBLIC/ALL_TAB_COLUMNS/ROW[OWNER="SCOTT" and TABLE_NAME="EMP" and COLUMN_NAME="EMPNO"]‘

  3. October 10, 2013 at 20:20

    Thanks for the quick reply! I don’t quite understand, though. Please forgive me if I’m missing something obvious. The expression you’ve constructed is filtering for a specific row. I know how to retrieve row data this way. What I’m looking to do is retrieve the metadata for a column. It’s my educated guess that you would need a path expression in the form of schema/table/column and that row would not be part of it, but I couldn’t find any Oracle documentation about it.
    Thanks again for your responsiveness.

    • October 11, 2013 at 13:53

      Sorry David, I don’t understand what you’re looking for. What do you mean by “metadata for a column” ? Could you give an example ?

  4. Anonymous
    October 13, 2013 at 21:11

    Sorry for the delay in responding. The metadata is the definition of the column, and contains information such as the data type of the column. For example, the metadata for a ‘lastname’ column could tell us that the column is a string data type of a certain maximum length.

    • October 14, 2013 at 13:16

      I know what metadata is :)
      What I don’t understand is what result you’re expecting. The path I gave earlier will retrieve that kind of info.
      I’ll send you an email if you don’t mind.

  5. Anonymous
    October 14, 2013 at 20:46

    Sure, email is fine, thanks: david@dfrankelconsulting.com, or feel free to phone me: +1 530-519-4135 or Skype: DavidSFrankel0107. Just to explain a little more, I want to use DBUris as arguments for DBMS_METADATA.Get_xxx() procedures, to retrieve metadata from the DDL.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: