The simple way to publish an XML stored in the database with the Oracle
XSQL servlet is to use the xsql:include-xml tag. An example is shown in Figure
1.
<?xml version="1.0"?>
<xsql:include-xml bind-params="id"
connection="demo"
xmlns:xsql="urn:oracle-xsql">
SELECT
x.xml_text doc FROM txml x
WHERE x.id =
?
</xsql:include-xml>
Figure 1 – Presenting an XML store in the DB using
XSQL
The table TXML contains the XML string in the column XML_TEXT (with varchar2 or CLOB type). The appropriate record is selected using the parameter ID. This works well, but there is one caveat.The xsql:query tag provides the no-rows-query fallback select, which is performed in case that the original query doesn’t return any rows. But the include-xml tag doesn’t have an analogue functionality. In case a wrong parameter is provided and no data is selected an error is returned (something like <xsql-error action="xsql:include-xml"/>). All in all, an empty string is no valid XML.One should be able to expect that a NVL function would solve the problem and provide the missing XML.But of course using NVL doesn’t help, as there is no record returned from the query. So NVL is not performed at all. See Figure 2.
<?xml version="1.0"?><xsql:include-xml bind-params="id" connection="demo" xmlns:xsql="urn:oracle-xsql"> SELECT NVL(x.xml_text,'<x>please provide valid id</x>') doc FROM txml x WHERE x.id = ? </xsql:include-xml>
Figure 2 – NVL doesn’t provide any help A possible workaround (a little ugly, though) is to add second select to the original query using UNION ALL. The extra query will return one row in case that the original query fails to return a row. The fallback query must of course handle the case that the parameter is NULL as well. A possible solution is depicted in Figure 3.
<?xml version="1.0"?><xsql:include-xml bind-params="id id id" connection="demo" xmlns:xsql="urn:oracle-xsql"> SELECT x.xml_text doc FROM txml x WHERE x.id = ? UNION ALL SELECT '<x>please provide valid id</x>' FROM dual where ? is null or not exists (select null from txml x where id = ?) </xsql:include-xml>
Figure 3 – Do it yourself “no-rows-query” for
include-xml
Finally, it should be noted that in order to present XML stored in a
database column the xsql:query function can be used as
well. This enables the use of the no-rows-query to handle missing data. This
approach, which is probably better if we need a fallback query, is shown in
Figure 4.
<?xml version="1.0"?><xsql:query bind-params="id" connection="demo" xmlns:xsql="urn:oracle-xsql"> SELECT xmltype(x.xml_text) doc FROM txml x WHERE x.id = ? <xsql:no-rows-query> SELECT XMLElement("x",'please provide valid id') FROM dual </xsql:no-rows-query> </xsql:query>Figure 4 – Using xsql:query to publish XML data
It should be noted that there are some differences in these two
approaches xsql:include-xml and xsql:query – see http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14252/adx_j_xsqladv.htm#BABHHJID
for
details.
Last revision 7.7.2009
Jaromir D.B. Nemec is a freelancer specializing in data warehouse and integration solutions. He can be reached at http://www.db-nemec.com