The Mystique of XSLT: Changing XML to Other Usable Formats

With the use of XML as prevalent as finding an X-gene at Xavier’s School for Gifted Youngsters, it is vital to know how to change XML to a different data format. With XSLT, this can be done easily.

For an easy to replicate example, let’s take a WordPress blog, dump the wp_posts table into XML, and then use XSLT to generate properly formed SQL INSERT statements. To dump our SQL data, we use the –xml flag on mysql and/or mysqldump:

mysql -u hank_mccoy -p -e "SELECT * FROM wp_posts" --xml wordpress_blog

<?xml version="1.0"?>


<resultset statement="SELECT * FROM wp_posts"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <row>

	<field name="ID">1</field>

	<field name="post_author">35</field>

	<field name="post_date">2007-05-16 12:00:00</field>

	<field name="post_date_gmt">2007-05-16 04:00:00</field>

	<field name="post_content">
        Registration for Flying 101 is open today!
 Be sure that you qualify for this class.
 I look forward to seeing you all this semester! More information below:
        ...</field>

	<field name="post_title">Registration for Flying 101</field>

	<field name="post_excerpt"></field>

	<field name="post_status">publish</field>

	<field name="comment_status">open</field>

	<field name="ping_status">open</field>

	<field name="post_password"></field>

	<field name="post_name">summer_2007_registration_flying_101

        </field>

	<field name="to_ping"></field>

	<field name="pinged"></field>

	<field name="post_modified">0000-00-00 00:00:00</field>

	<field name="post_modified_gmt">0000-00-00 00:00:00</field>

	<field name="post_content_filtered"></field>

	<field name="post_parent">0</field>

	<field name="guid">

http://blog.xavierschoolforgifted.edu/summer_2007_registration_flying_101/

        </field>

	<field name="menu_order">0</field>

	<field name="post_type">post</field>

	<field name="post_mime_type"></field>

	<field name="comment_count">0</field>

  </row>

  ...
</resultset>

Building an XSLT file to navigate each row in the result and generate a matching INSERT statement requires a tweak to the XSLT file. The default output for a XSLT transformed file is XML, so let’s change that to text:

<xsl:output method="text" omit-xml-declaration="yes" media="text/plain" />

Next, we’ll generate the start of the INSERT statement by declaring what columns we’ll be inserting into:

INSERT INTO wp_posts (

<xsl:for-each select="row[1]/field">

    <xsl:value-of select="@name" />

    <xsl:if test="position() != last()">, </xsl:if>

</xsl:for-each>

) VALUES

We are selecting only one row (the first) to go through each field and grab the name attribute. This attribute is storing the column name of the table, and so we’ll use it accordingly. Then, we use the xsl:for-each and the current function to build the fields for our INSERT statements:

<xsl:for-each select="row">
(
   <xsl:for-each select="field">
      '<xsl:value-of select="current()" />'
      <xsl:if test="position() != last()">,</xsl:if>
   </xsl>
)
</xsl>

This code is grabbing each row of data, then each field in each row. The current() function is pulling the value of the currently selected node, which is the field it is currently traversing. We need a comma to separate each field of data from the next (but not on the last field), and we accomplish this with a nice if statement.

Now apply the XSLT to your XML file, and celebrate in jubilee that you have transformed XML data to different and usable output.

Michael Marr
About Michael Marr
Michael Marr is a staff writer for WebProNews

Leave a Reply