XML to SQL - the easy way?

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

Replies are listed 'Best First'.
Re: XML to SQL - the easy way? by DrHyde (Prior) on Jun 09, 2011 at 10:03 UTC
You have forgotten to do three important things, without which we can't help you: show us the data that you've got; show us the data that you want; show us the code that you've tried You should take the time to reduce all three to the minimum required to show the problem.	[reply]
Re: XML to SQL - the easy way? by bart (Canon) on Jun 09, 2011 at 08:53 UTC
Personally I'd try to parse the XML record by record (for example using XML::Twig, allegedly — I never could wrap my head around that module, so I wrote my own), put it into a perl hash, and convert the hash into SQL using SQL::Abstract. With DBIx::Simple you can use the SQL::Abstract syntax to put the data straight into the database. `while(my $row = read_row_from_xml($input)) { $db->insert(mytable => $row); }` [download] `$row` is suppoed to look something like `{ 'foo' => 123, 'bar' => 456, 'comment' => 'This is plain text', # NOT escaped 'undefined' => undef, # becomes NULL }` [download] Writing the sub read_row_from_xml is left as an exercise for the user.	[reply] [d/l] [select]
Re: XML to SQL - the easy way? by dHarry (Abbot) on Jun 09, 2011 at 09:22 UTC
Many databases come with there own tools for importing XML. It's sort of standard nowadays. As you doen't give any details, neither on the db you use, nor on the structure of the XML file... Before writing any code I would take a look around.	[reply]
Re: XML to SQL - the easy way? by Anonymous Monk on Jun 09, 2011 at 08:22 UTC
Do you know of any alternative? You are the alternative :) See XML::Twig, setup a few handlers, make a few inserts	[reply]
Re: XML to SQL - the easy way? by happy.barney (Friar) on Jun 09, 2011 at 08:35 UTC
It depends on XML structure and required behaviour, but proper tool to transform XML to anything is XSLT.	[reply]
Re^2: XML to SQL - the easy way? by Jenda (Abbot) on Jun 10, 2011 at 09:20 UTC
Yeah, proper tool. XML in XML that XML XML xml to xml in XML and xmls xml xml. Now how do you propose to use XSLT in this case? Write a complicated and fragile template that transforms the XML file into a bunch of INSERT statements (most likely failing to decode and escape the values properly) that you then force the database to parse and execute? Sounds very xmlish to me. XSLT also known as "I was lazy to design a sensible syntax and write a parser so let's force the developers to give us the syntax tree serialized in a format we already have a parser for" is seldom the right tool, but in this case it's simply ... not a good option. Jenda Enoch was right! Enjoy the last years of Rome.	[reply]
Re^2: XML to SQL - the easy way? by DrHyde (Prior) on Jun 10, 2011 at 10:12 UTC
Ahhh, XSLT. The only language with more brackets than LISP.	[reply]