It also might be interesting to see whether the db itself, or other external tools, can do part of the job for you. For example, MS SQL Server's XML Bulk Load Component.

Joe

Hmmm... that term sounds vaguely familiar.   What does “MS” stand for, again?

;-)

From the look of things, it seems like XML::XPath might do such a good job of ripping into the XML structure that a few short scripts might just do it for us.

<?xml version="1.0"?>
<!DOCTYPE rdf:RDF [
    <!ENTITY rdf 'http://www.w3.org/1999/02/22-rdf-syntax-ns#'>
    <!ENTITY rss 'http://purl.org/rss/1.0/'>
]>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:rdf="&rdf;"
                xmlns:rss="&rss;">
  <xsl:output method="text"/>

  <xsl:template match="/rdf:RDF">
      <xsl:apply-templates select="rss:item" />
  </xsl:template>

  <xsl:template match="rss:item">
    insert into links (url, title)
    values (
    '<xsl:call-template name="sql-escape"><xsl:with-param name="text" 
+select="rss:title"/></xsl:call-template>',
    '<xsl:call-template name="sql-escape"><xsl:with-param name="text" 
+select="rss:link"/></xsl:call-template>'
    );
  </xsl:template>

  <xsl:template name="sql-escape">
    <xsl:param name="text"/>
    <xsl:variable name="tmp">
      <xsl:call-template name="replace-substring">
        <xsl:with-param name="from">'</xsl:with-param>
        <xsl:with-param name="to">''</xsl:with-param>
        <xsl:with-param name="value" select="$text"/>
      </xsl:call-template>
    </xsl:variable>
    <xsl:value-of select="$tmp"/>
  </xsl:template>

  <xsl:template name="replace-substring">
    <xsl:param name="value" />
    <xsl:param name="from" />
    <xsl:param name="to" />
    <xsl:choose>
      <xsl:when test="contains($value,$from)">
        <xsl:value-of select="substring-before($value,$from)" />
        <xsl:value-of select="$to" />
        <xsl:call-template name="replace-substring">
          <xsl:with-param name="value" select="substring-after($value,
+$from)" />
          <xsl:with-param name="from" select="$from" />
          <xsl:with-param name="to" select="$to" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$value" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  
</xsl:stylesheet>
[download]

wget -O - http://feeds.delicious.com/rss/aufflick | xsltproc del2sql.xsl - | psql links_db Pretty neat huh! Unfortunately xslt is even less decipherable than golf perl, but thems the breaks.

You might also be better using xslt to convert to CSV and then using some simple perl script to slurp in the csv - you could then handle insert/update errors gracefully in your perl.

The utility sql-escape and replace-substring templates can be placed in a common file and included into your xsl stylesheets via an xsl:import statement.

Update: In my example I used a commandline xslt engine wrapper, but if you're going the route of a perl script to manage the db connection you can manage the xslt engine there also. CPAN has plenty of xslt related modules. XML::XSLT::Wrapper seems a good idea and would let you benchmark the different libraries it supports.

Hello,

Assuming that the XML file takes a "each child of the root node is a record" layout, the first thing I would do is turn each record in to a data structure. An easy way to do this while keeping efficiency in mind is with Jenda's XML::Rules (see also node #582348).

Then personally I would use Data::FormValidator to validate and manipulate each record.

And then probably DBI or maybe an ORM to insert the data in to the db.

trwww

I'm afraid that if such a module was to be general enough it would be either just as low level or way too complex.

For the time being try to forget about the modules that might be available and take a few of those tasks and try to express their differences in a way that would be detailed enough for a computer (using a hypothetic module). Try to express as a config file or as function/method calls of a module the slices and the filtering and the mapping ... you may easily find out that what you end up is too complex and that there really isn't a way around using your preferred XML parser and DBIx module directly. Or maybe you find out that what YOU need can be built on top of that selected parser and DBIx easily with the exact bits that can be controlled that you need.

In either case I don't think you can get away with something like a shared script and a bunch of config files. I'd expect more luck with a library that implements the common bits used by a new script for each import.

Maybe someone can adapt/use this one. It is a generic clientside XML to SQL converter.