xml to sql transformation issues

ismyhc · January 20th, 2011, 03:08 PM

Im trying to transform an xml file into in sql so that I can insert the info into a database. Ive been fairly successful, but am running to a couple of issues.

using libxml2, xsltproc

Below is my xsl stylsheet and xml sample.

xsl stylesheet

Code:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text" encoding="UTF-8"/>
  <xsl:template match="/">
    <xsl:for-each select="log/logentry">
      <xsl:variable name="logentryRevision"><xsl:value-of select="@revision"/></xsl:variable>
      <xsl:variable name="author"><xsl:value-of select="author"/></xsl:variable>
      <xsl:variable name="date"><xsl:value-of select="date"/></xsl:variable>
      <xsl:variable name="msg"><xsl:value-of select="msg"/></xsl:variable>
    <xsl:for-each select="paths/path">
      <xsl:variable name="pathKind"><xsl:value-of select="@kind"/></xsl:variable>
      <xsl:variable name="pathCopyFromPath"><xsl:value-of select="@copyfrom-path"/></xsl:variable>
      <xsl:variable name="pathCopyFromRev"><xsl:value-of select="@copyfrom-rev"/></xsl:variable>
      <xsl:variable name="pathAction"><xsl:value-of select="@action"/></xsl:variable>
INSERT INTO svn(revision, author, date, kind, copyfrom_path, copyfrom_rev, action, msg)      
VALUES ('<xsl:value-of select="$logentryRevision"/>', '<xsl:value-of select="$author"/>', '<xsl:value-of select="$date"/>', '<xsl:value-of select="$pathKind"/>', '<xsl:value-of select="$pathCopyFromPath"/>', '<xsl:value-of select="$pathCopyFromRev"/>', '<xsl:value-of select="$pathAction"/>', "<xsl:value-of select="$msg"/>");
    </xsl:for-each>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

xml sample

Code:

<?xml version="1.0"?>
<log>
<logentry
   revision="1540">
<author>george</author>
<date>2011-01-17T19:29:44.338063Z</date>
<paths>
<path
   kind=""
   action="M">/trunk/pathto/file.cpp</path>
<path
   kind=""
   action="M">/trunk/pathto/another/file.cpp</path>
</paths>
<msg>some text about the above changes, etc.
</msg>
</logentry>
</log>

When I do the transform. I get multiple sql statments for the single "logentry". It looks like since there are more than one "path" it creates the sql twice.

Any ideas?

Thanks!

samjudson · January 20th, 2011, 04:24 PM

Well what do you want it to do if there are two <path> elements?

Tell us what the output currently is, and what you want it to look like and we might be able to help you.

ismyhc · January 20th, 2011, 05:12 PM

The output looks like the following.

Code:

INSERT INTO svn(revision, author, date, kind, copyfrom_path, copyfrom_rev, action, msg)
VALUES ('1540', 'george', '2011-01-17T19:29:44.338063Z', '', '', '', 'M', "some text about the above changes, etc.
");

INSERT INTO svn(revision, author, date, kind, copyfrom_path, copyfrom_rev, action, msg)
VALUES ('1540', 'george', '2011-01-17T19:29:44.338063Z', '', '', '', 'M', "some text about the above changes, etc.
");

I would like to have one single sql statement for each logentry whether it contains 2 or 10 <path> elements. Im not sure if this is possible.

So I would have a table with the following rows in a database called:
svn:
-revision
-author
-date
-path
-kind
-copyfrom-path
-copyfrom-rev
-action
-msg

Im guessing that since there can be more than one path tied to a revision that I may have to structure my database differently anyways.

I was also trying to get the value of each<path> not just the attribute values of <path>.

samjudson · January 21st, 2011, 07:37 AM

Could you actually show us what you want the SQL to look like when there are multiple <path> elements. What do you want to go into the 'path', 'kind' and 'action' fields.

Your original XSLT references attributes called @copyfrom-path and @copyfrom-rev which don't exist in your input XML as well.

It does look like you need to think a bit more about the structure of your tables, perhaps have a 'svn' table and then a 'svn_paths' table containing the path information.

ismyhc · January 21st, 2011, 11:33 AM

Thanks for bearing with me on this. I'm going to talk with my dba and figure out what the tables are going to look like first. Once I know that then I can have a better idea of what I need my sql statements to be. I apologize for jumping in here without having as much info as I need. Once I gather more info Ill be back.

Thanks!