hatop.de hatop.de

XML Database Connection with SXQL

About SXQL
SXQL is a set of java classes which can:
  • put XML documents into a relational database.
  • get data from a relational database into XML documents.
If you ever wanted to build an index list over a set of structured xml documents, or you just wants to use SQL to query your documents, there is a chance that SXQL can help you.

News
  • June 2002: SXQL comes with servlet filter support. An XSLTFilter, a SQLFilter and some more filters comes with sxql.
  • November 2002: SXQL updated to xalan 2.4.1 and java 1.4
  • June 2004: some minor bug fixes
  • Dezember 2005: SXQL updated to xalan 2.7 and java 1.5
  • February 2006: sxql comes with new class prefix "de" instead of "com". Users must update their xslt styles

Mapping XML Data to relational Databases
XML is a much more powerful data modeling language than the relational data model underlying SQL-Databases is. Throwing XML data into a SQL database is therefore slightly more complicated than getting XML back from a database: you somehow have to find a mapping from nested data structures to flat relational tuple format.
If you can express such a mapping in XSLT, you can use the SXQL extension to the apache xalan xslt processor to put xml data into the database.

The example xml file: 
<collection>
        <comic id="6" number="62" title="Sandman" >
          <writer>Neil Gaiman</writer>
          <penciller pages="1-9,18-24">Glyn Dillon</penciller>
          <penciller pages="10-17">Charles Vess</penciller>
        </comic>
</collection>
can be put into a database with this XSLT database transporter: 
<!-- shows how to put xml data into a relational database
     using the apache xslt extension mechanism with sxql
     -->

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:lxslt="http://xml.apache.org/xslt"
                xmlns:sql="de.hatop.sxql.SQLXtension"
                extension-element-prefixes="sql"
                version="1.0">

<xsl:output method="text" encoding="UTF-8"/>

   <!-- you may need to change this: -->
   <xsl:template match="/">
      <sql:init dburl="jdbc:mysql://127.0.0.1/test"
                driver="org.gjt.mm.mysql.Driver" user="" passwd="" />
      <sql:setLog level="0" />
        <xsl:apply-templates />
      <sql:close />
   </xsl:template>

   <xsl:template match="comic">
      <!-- insert values into database: -->
      <sql:insert table="comic" id="{@id}" title="{@title}"
                   number="{@number}" writer="{writer}" />
      <xsl:apply-templates select="penciller" />
   </xsl:template>

   <xsl:template match="penciller">
      <sql:insert table="penciller" id="{parent::node()/@id}"
                   pages="{./@pages}" penciller="{.}" />
   </xsl:template>
</xsl:stylesheet>

Constructing XML Documents from Database records
Data from relational databases is structured as table rows. If you need more structure, that is, the values should be nested within an XML document, then you may want to use a tool like SXQL. While using this program to query a XML document against a database the full power of SQL is with you.
You have to write a template XML query file like this: 
<collection>
<sql query="select id, title, number, writer from comic where id='6'">
   <comic id="" title="" number="">
      <writer/>
      <sql query="select pages, penciller from penciller where id='$id'">
          <penciller pages=""/>
      </sql>
    </comic>
</sql>
</collection>
After running this query file, SXQL generates an output document identical to the original xml data above.
The SXQL query file uses a pattern Element which is filled up with the corresponding data records from the database source.
A more sohpisticated example showing how to query index lists out of a XML repository is contained in the distribution of SXQL.

Installation and Usage
Please read and understand this license agreement.
You will receive a download link after payment.
(Source code included).
After downloading unpack and install the sxql.jar file somewhere in your CLASSPATH. You have to install apache xalan (version 2), available from xml.apache.org before SXQL does make any sense. Your database must have a JDBC driver.

Disclaimer
All trademarks and copyrights mentioned on this page are properties of their respective owners.
You have NO WARRANTY for using software, ideas or anything else from this page.

Comments

If you have any problems with SXQL or comments about it, you may want to use the mail form from this site.