Transforming XML to SQL-Based Databases

Transforming XML to SQL-Based Databases

During any discussion of advanced XSLT topics, its worthwhile to mention Saxons SQL extension elements. Using Java Database Connectivity (JDBC), you can work with SQL-based databases. Youve already seen XML to XML transformations, to HTML, to XHTML, to plain text, to RTF, to JavaScript, and so on in this book. Now youll see an XML transformation to a SQL-based database.

Using Instant Saxon

Note that you cannot use the prebuilt Windows-only saxon.exe to connect to JDBC databases. You should run the Saxon Java class com.icl.saxon.StyleSheet instead, as I do at the end of this example.

In this example, I add the data in planets.xml to a Microsoft Access-style database, planets.mdb. If you want to follow along in this example, create this database file, creating four new text fields, named Name, Mass, Radius, and Day in a table named planets, and leaving the rest of the database file empty. In Windows, Ill register this database file as an ODBC source using the Data Sources (ODBC) icon in the control panel (if youre using Windows 2000, youll find it in the Administrative Tools folder in the Control Panel), giving it the data source name planets. When you run this example, itll read the planetary data in planets.xml and add it to the database file, planets.mdb.

I use the Saxon <sql:connect> element to connect to this database using JDBC. The namespace prefix sql is defined this way in Saxon:

 <xsl:stylesheet      xmlns:sql="http://icl.com/saxon/extensions/com.icl.saxon.sql.SQLElementFactory"      xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.1">          .          .          . 

To actually connect to the planets data source, you use the < sql:connect> extension element. That element has database , user , password , and driver attributes. For JDBC, you set the driver attribute to sun.jdbc.odbc. JdbcOdbcDriver, the database attribute to the ODBC data source, jdbc:odbc:planets, and the user and password attributes to a username and password needed to log in to the database. I dont need a username or password here, but I add placeholders for those parameters, because many database applications do require them:

 <xsl:stylesheet      xmlns:sql="http://icl.com/saxon/extensions/com.icl.saxon.sql.SQLElementFactory"      xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.1">      <xsl:param name="database" select="'jdbc:odbc:planets'"/>      <xsl:param name="user"/>      <xsl:param name="password"/>      <xsl:template match="PLANETS">          <sql:connect database="{$database}" user="{$user}" password="{$password}"          driver="sun.jdbc.odbc.JdbcOdbcDriver" xsl::extension-element-prefixes="sql"/>          <xsl:apply-templates select="PLANET"/>      </xsl:template>          .          .          . 

At this point, then, Im connected to the planets data source. I want to insert the data from each <PLANET> element into the database, so I construct a new template that matches <PLANET> elements and uses the Saxon <sql:insert> element to insert data into the planets table of the database:

 <xsl:stylesheet      xmlns:sql="http://icl.com/saxon/extensions/com.icl.saxon.sql.SQLElementFactory"      xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.1">          .          .          .      <xsl:template match="PLANETS">          <sql:connect database="{$database}" user="{$user}" password="{$password}"          driver="sun.jdbc.odbc.JdbcOdbcDriver" xsl::extension-element-prefixes="sql"/>          <xsl:apply-templates select="PLANET"/>      </xsl:template>      <xsl:template match="PLANET">          <sql:insert table="planets" xsl::extension-element-prefixes="sql">          .          .          .          </sql:insert>      </xsl:template>  </xsl:stylesheet> 

The <sql:insert> element inserts a new record into the database. To specify the new data for the fields of this record, you use the <sql:column> element, setting the name attribute of this element to the name of the column in which the data is to be stored, and setting the select attribute to the data you want to store, as follows :

 <xsl:stylesheet      xmlns:sql="http://icl.com/saxon/extensions/com.icl.saxon.sql.SQLElementFactory"      xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.1">          .          .          .      <xsl:template match="PLANET">          <sql:insert table="planets" xsl::extension-element-prefixes="sql">              <sql:column name="Name" select="NAME"/>              <sql:column name="Mass" select="MASS"/>              <sql:column name="Radius" select="RADIUS"/>              <sql:column name="Day" select="DAY"/>          </sql:insert>      </xsl:template>  </xsl:stylesheet> 

Ideally, this would be all youd need, but with the most recent Access driver, Saxon doesnt appear to flush its data buffers at the end of the operation. This means that the data for the last planet in planets.xml, Earth, isnt sent to the database. To flush the data buffers, I call the <PLANET> template explicitly as a named template, this time using <sql:insert> with a dummy set of data:

Listing 10.11 Working with a SQL-Based Database
 <xsl:stylesheet      xmlns:sql="http://icl.com/saxon/extensions/com.icl.saxon.sql.SQLElementFactory"      xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.1">      <xsl:param name="database" select="'jdbc:odbc:planets'"/>      <xsl:param name="user"/>      <xsl:param name="password"/>      <xsl:template match="PLANETS">          <sql:connect database="{$database}" user="{$user}" password="{$password}"          driver="sun.jdbc.odbc.JdbcOdbcDriver" xsl::extension-element-prefixes="sql"/>          <xsl:apply-templates select="PLANET"/>          <xsl:call-template name="writer"/>      </xsl:template>      <xsl:template match="PLANET" name="writer">          <xsl:choose>              <xsl:when test="NAME">                  <sql:insert table="planets" xsl::extension-element-prefixes="sql">                      <sql:column name="Name" select="NAME"/>                      <sql:column name="Mass" select="MASS"/>                      <sql:column name="Radius" select="RADIUS"/>                      <sql:column name="Day" select="DAY"/>                  </sql:insert>              </xsl:when>              <xsl:otherwise>                  <sql:insert table="planets" xsl::extension-element-prefixes="sql">                      <sql:column name="Name" select="' '"/>                      <sql:column name="Mass" select="' '"/>                      <sql:column name="Radius" select="' '"/>                      <sql:column name="Day" select="' '"/>                  </sql:insert>              </xsl:otherwise>          </xsl:choose>      </xsl:template>  </xsl:stylesheet> 

This stylesheet correctly adds three records to the planets.mdb database: one new record for each planet. As I mentioned, you cant use the pre-built saxon.exe executable file here; instead, you can use the Saxon Java class com.icl.saxon.StyleSheet . First, I set the classpath to include saxon.jar:

 C:\>set classpath=.;c:\saxon\saxon.jar 

Then I can pass com.icl.saxon.StyleSheet planets.xml and the XSL stylesheet you saw in Listing 10.11:

 C:\>java com.icl.saxon.StyleSheet planets.xml saxonsql.xsl 

And thats all it takesthe planetary data is inserted into planets.mdb. You can see the results of this stylesheet in Figure 10.4, where Ive opened planets.mdb in Microsoft Access. Now weve seen an XML-to-SQL database transformation.

Figure 10.4. Using Saxons SQL extensions.
graphics/10fig04.gif

Another aspect of XSLT for which you use programming is supporting XSLT on servers. To give you a taste of whats possible, I next transform planets.xml with planets.xsl using Microsofts Active Server Pages (ASP), Suns Java Server Pages (JSP), and Java servlets, all of which run on Web servers. They return the result document back to the users browser.

Theres no space here to cover how these technologies work in depth, but if youre unfamiliar with them and want more information, you can find the details online (as always, these URLs are subject to change):

  • ASP. http://msdn.microsoft.com/workshop/c-frame.htm#/workshop/server/Default.asp (Microsofts ASP tutorial and documentation)

  • JSP. http://java.sun.com/products/jsp/ (Suns main JSP page)

  • Servlets. http://java.sun.com/products/servlet/ (Suns main Servlet page)



Inside XSLT
Inside Xslt
ISBN: B0031W8M4K
EAN: N/A
Year: 2005
Pages: 196

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net