Building Oracle XML Applications

Creating Insert Transformations Automatically

In Chapter 12, we learned how to use the <xsql:insert-request> element to get posted XML or HTML form data into our database. Recall that the <xsql:insert-request> action element has the following syntax:

<xsql:insert-request table="targettable" transform="style.xsl"/>

where it is the job of the style.xsl stylesheet to transform the inbound XML document into the canonical ROWSET/ROW format that reflects the structure of the targettable table or view. If you’re like me, you tire quickly of manually creating these “insert transform” stylesheets and you start thinking of a way to automate their creation. You need look no further than the effective combination of XSQL pages and XSLT to get the data we need and XSLT to produce the “insert transform” for a given table.

Recall that the canonical XML structure required for insert is the same structure that is produced by doing a SELECT * query over the table in question. So let’s build a simple XSQL page to do that SELECT * query over a table whose name is a parameter supplied in the request:

<?xml version="1.0"?>
<page connname="xmlbook" connection="{@connname}">
  <xsql:query null-indicator="yes" xmlns:xsql="urn:oracle-xsql">
  <![CDATA[
    SELECT *
      FROM {@table}
     WHERE rownum < 2
  ]]>
  </xsql:query>
</page>

The FROM clause uses the {@table} syntax to refer to an XSQL page parameter and is wrapped in by a CDATA section so that none of the characters that occur between the opening <![CDATA[ and the closing ...