Course Web Pages - Spring 2012 - LIBR 242-01/10 Greensheet - Sample Codes - xSQL

xSQL INSERTing XML Data/Records into Table

The direct counterpart to xsql:query for writing to the database is xsql:dml. As with xsql:query, the contents of xsql:dml are raw SQL. Here again an effective XSQL document can be extremely compact. And here to the XSQL can be parameterized:



<?xml version="1.0"?>
<xsql:dml connection="myDbConnection" 
          xmlns:xsql="urn:oracle-xsql"
          diameter="50">
  INSERT into OneOfYourTable
    VALUES (2000,{@diameter})
</xsql:dml>

XSQL also has a mechanism for inserting HTML form data that arrive via an HTTP POST (n.b., not GET) operation into a database--i.e., we want to build a page handlePost.xsql such that, if handlePost.xsql is the value of the "action" attribute of some HTML form somewhere, and if someone actually does a submit on that form, the data on the form get stuffed into the database (you can also do this with POSTed XML, but we'll focus on the HTML case here).

The HTML form is going to look something like this:


...
<form action="handlePost.xsql" method="POST">
  Name:<input type="text" name="name"><br>
  Rank:<input type="text" name="rank"><br>
  Serial number:<input type="text" name="serialnumber"><br>
  <input type="submit">
</form>
...

When the form is submitted, and an HTTP POST is done to handlePost.xsql, the form data are 'materialized' within XSQL in a specific XML format. The XSQL element that'll do the insert is xsql:insert-request. It takes two attributes, "table", which says what database table we want to insert the data into, and "transform", which lets us apply an XSLT stylesheet to the data before it's inserted. The economy of XSQL is again remarkable. The stylesheet handlePost.xsql could end up as simple as:



<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="dressUpInsertOutput.xsl"?>
<xsql:insert-request connection="someConnection"
                     xmlns:xsql="urn:oracle-xsql"
                     table="aTable"
                     transform="tweakit.xsl"/>

When we do the insertion, the data must have the same canonical XML format (ROWSET, ROW...) as they do when they're initially extracted from the database. This means we have to transform the "materialized-parameters" format (request, parameters...) into the canonical one. But this is just what XSLT is made for (and it's what the "transform" attribute on xsql:insert-request is there for). The stylesheet would look something like:



<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
                   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
    <ROWSET>
      <ROW>
          <name><xsl:value-of select="request/parameters/name"></name>
          <rank><xsl:value-of select="request/parameters/rank"></rank>
          <serialnumber><xsl:value-of select="request/paramters/serialnumber">
			</serialnumber>
      </ROW>
    </ROWSET>
  </xsl:template>
</xsl:stylesheet>

The result of executing xsql:insert-request, apart from the fact that the database is updated, is that the xsql:insert-request element is replaced, in the output of the .xsql file, by an xsql-status element, the value of whose "rows" attribute reports the number of database rows altered. Either that or, in the event of failure, the xsql:insert-request is replaced by an xsql-error element. So if handlePost.xsql is the single-element document we have above, the output of handlePost that results from a form submission is going to be:



<?xml version="1.0"?>
<xsql-status action="xsql:insert-request" rows="1"/>

In any case, the result of executing xsql:insert-request is then handled by the xml-stylesheet dressUpInsertOutput.xsl (as specified in handlePost.xsql above) and transformed into meaningful (and displayable> HTML lines. The stylesheet could have templates matching both xsql-status and xsql-error, and take appropriate action depending on which it sees.

Just to be explicit about the order of stylesheet application in this case: the stylesheet named in the "transform" attribute of xsql:insert-request (tweakit.xsl) is applied first -- applied to the POSTed data that showed up on the doorstep, applied to get it into the right form to be inserted into the database. Once the XSQL processor's massaged the input, it sends it to the database and waits for a status report. When it gets that (XMLized) response, it applies the stylesheet named in the stylesheet PI (dressUpInsertOutput.xsl) to it, and sends the result of that transformation back to the client.

Select here to return to the sample codes page.

Top of Page

Programs

Courses

xSQL INSERTing XML Data/Records into Table