Split XML tags and transform to SQL

Question

I want to transform a large xml file to sql statements with xslt. For example I have the author tag.

<author>Evans, Jim; Henderson, Mike; Coyier, Alan</author>

I have a column for last_name and first_name, so Evans, Henderson and Coyier should go to last_name and so on.

How can I pick them out of the tag and put it into sql statements!

Thanks in advance!

Answer 1

You can do it with xslt, but it's not very pretty as you need to parse out the lastname/firstname pairs, and then the lastname - firstname yourself. This is done through recursion.

In the same xslt you can generate SQL statements from it, but again this is not painless as you have to escape any literal string delimiters, for example, O'Hanlon must become the SQL string literal 'O''Hanlon'.

Again, this is done with recursion.

This is an example that is fully functional:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;

  <xsl:output method="text"/>

  <!-- match the eelement to extract data from -->

  <xsl:template match="/author">
    <xsl:call-template name="authors">
      <xsl:with-param name="authors" select="text()"/>
    </xsl:call-template>
  </xsl:template>

  <!-- recursively extract individual authors -->
  <xsl:template name="authors">
    <xsl:param name="authors"/>
    <xsl:variable name="author" select="substring-before($authors,';')"/>
    <xsl:choose>
      <xsl:when test="string-length($author)=0">
        <xsl:call-template name="author">
          <xsl:with-param name="author" select="$authors"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="author">
          <xsl:with-param name="author" select="$author"/>
        </xsl:call-template>
        <xsl:call-template name="authors">
          <xsl:with-param name="authors" select="substring-after($authors,';')"/>
        </xsl:call-template>        
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!-- extract firstname, lastname, escape single quote, and generate SQL -->
  <xsl:template name="author">
    <xsl:param name="author"/>
      <xsl:variable name="last-name" select="normalize-space(substring-before($author, ','))"/>
      <xsl:variable name="first-name" select="normalize-space(substring-after($author, ','))"/>
      INSERT INTO author (first_name, last_name) VALUES (
        '<xsl:call-template name="replace">
           <xsl:with-param name="text" select="$first-name"/>
           <xsl:with-param name="search">&apos;</xsl:with-param>
           <xsl:with-param name="replace">&apos;&apos;</xsl:with-param>
         </xsl:call-template>'
      ,
        '<xsl:call-template name="replace">
           <xsl:with-param name="text" select="$last-name"/>
           <xsl:with-param name="search">&apos;</xsl:with-param>
           <xsl:with-param name="replace">&apos;&apos;</xsl:with-param>
         </xsl:call-template>'
      );
  </xsl:template>

  <!-- recursive search and replace -->
  <xsl:template name="replace">
    <xsl:param name="text"/>
    <xsl:param name="search"/>
    <xsl:param name="replace"/>
    <xsl:value-of select="$text"/>
    <xsl:variable name="tail">
      <xsl:if test="contains($text, $search)">
        <xsl:call-template name="replace">
          <xsl:with-param name="text" select="substring-after($text, $search)"/>
          <xsl:with-param name="search" select="$search"/>
          <xsl:with-param name="replace" select="$replace"/>
        </xsl:call-template>
      </xsl:if>
    </xsl:variable>
    <xsl:value-of select="concat(substring-before($text, $search), $tail)"/>
  </xsl:template>
</xsl:stylesheet>

with your input:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="author.xslt"?>
<author>Evans, Jim; Henderson, Mike; Coyier, Alan</author>

This gives me this output:

INSERT INTO author (first_name, last_name) VALUES ( 'Jim' , 'Evans' );
INSERT INTO author (first_name, last_name) VALUES ( 'Mike' , 'Henderson' );
INSERT INTO author (first_name, last_name) VALUES ( 'Alan' , 'Coyier' ); 

If you need to do a lot of XML munging and inserting it into databases, I can recommend using a tool like kettle, aka. pentaho data integration. It has many steps that you can use to process data, and out-of-the-box connectivity for more than 30 databases. It's free, and easy to install. get it here: http://sourceforge.net/projects/pentaho/files/Data%20Integration/