markwild has asked for the wisdom of the Perl Monks concerning the following question:

This site is terrific and I'm enjoying responding to the puzzles that others post. My turn to ask a question. I am a 2-year Perl user, but new to XML and trying to make my way through XML::Parser and XML::Simple. I've searched through the vague XML module names on CPAN, even read a few readme files, and can't find what I'm looking for. My goal is to map XML data to SQL data, both for reading and writing. In other words, I'd like to execute a SELECT query on my data and get the data into some nice XML that I can send to some external application. Also, I'd like to read in some XML and insert that into one or more tables. I've no problem reading and writing to my databases, and no problem reading and writing XML using XML::Simple. What I need is a mapping tool. Before I go writing one, I'm wondering if anyone knows of such a method or module to get the job done. Anyone? Hello? Bueller? Bueller? Thanks. --Mark

Replies are listed 'Best First'.
Re: XML to DBI
by btrott (Parson) on Oct 03, 2000 at 04:15 UTC
    Check out DBIx-XML_RDB. Specifically designed to do what you're asking, I believe.

    From the synopsis: 

        use DBIx::XML_RDB;
    my $xmlout = DBIx::XML_RDB->new($datasource,
        "ODBC", $userid, $password, $dbname) ||
        die "Failed to make new xmlout";
    $xmlout->DoSql("select * from MyTable");
    print $xmlout->GetData;

    [download]
[reply]
[d/l]
Re: XML to DBI
by mirod (Canon) on Oct 03, 2000 at 06:43 UTC

    I don't think you will find any automated tool to do the mapping for you. After it's a design decision whether to build just one huge document with all of your data or several smaller ones each holding a separate table.

    I found a couple of papers on methodologies you can use though:

[reply]
      Thanks for the references (and other posts, too). I'll read them and post an update. --Mark
[reply]
Re: XML to DBI
by extremely (Priest) on Oct 03, 2000 at 04:38 UTC

    I'm not sure there is a good enough general case there to warrant writing it. Most of the time once you get to SQL you are going to use more than one table. Factor in dependencies, one to many lookups and such and I don't know if there is a way to do that without it being specific to the data and application.

    Now for individual tables and views on select you might be able to make some fancy inroads. You get back a table like: 

    keyfield  field1   field2
=========+========+==============
Data1     f1data   f2data
Data2     x1data   NULL

    and you want out 

    <dbxml>
 <row>
  <col>
   <field>keyfield</field>
   <value>Data1</value>
  </col>
...

    or would you expect 

    <dbxml>
 <row>
  <keyfield>Data1</keyfield>
  <field1>f1data</field1>
  <field2>f2data</field2>
 </row>
...

    The first is pretty flexible since you don't have to deal with anything but a single DTD that describes the general layout. The second way you have to DTD every table so that your parser knows what to expect. And we haven't even dealt with CDATA yet or other madness. Basically, XML is super pimped up CSV in these cases. You use the power of XML to express the relationships between multiple tables and I can't even try to think about how it would be automatable so that you could pass it straight to SQL.

    Thanks, now my brain hurts again. =)

    --
    $you = new YOU;
    honk() if $you->love(perl)

[reply]

Back to Seekers of Perl Wisdom