How do i Convert XML to MYSQL insert using the UNIX command line?

Question

If I have a XML that looks like this

<monsterload xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<monster>
<idLore_Monsters>99</idLore_Monsters>
<strMonsterName>Young Minotaur</strMonsterName>
<strDescription>Not quite as experienced as an older Minotaur warrior, young Minotaurs are still quite dangerous and guard their treasure with equal ferocity if not strength.</strDescription>
<intLevel>20</intLevel>
<intExp>35</intExp>
<intGold>18</intGold>
<intBaseHP>301</intBaseHP>
<intBaseMP>100</intBaseMP>
<intBaseSP>30</intBaseSP>
<intSTR>35</intSTR>
<intDEX>35</intDEX>
<intINT>0</intINT>
<intCHA>0</intCHA>
<intEND>30</intEND>
<intLUK>0</intLUK>
<strExtraData>minotaur,lean:1.5</strExtraData>
<idLore_Elements>3</idLore_Elements>
<idLore_WeaponTypes>1</idLore_WeaponTypes>
<intWeaponBaseDamage>4</intWeaponBaseDamage>
<intWeaponRandDamage>3</intWeaponRandDamage>
<intWeaponBonusToHit>12</intWeaponBonusToHit>
<intArmorFIRE>115</intArmorFIRE>
<intArmorWATER>85</intArmorWATER>
<intArmorICE>85</intArmorICE>
<intArmorWIND>130</intArmorWIND>
<intArmorEARTH>70</intArmorEARTH>
<intArmorENERGY>115</intArmorENERGY>
<intArmorLIGHT>100</intArmorLIGHT>
<intArmorDARKNESS>100</intArmorDARKNESS>
<intArmorMELEE>25</intArmorMELEE>
<intArmorRANGED>15</intArmorRANGED>
<intArmorMAGIC>15</intArmorMAGIC>
<strBodyFileName>monster-minotaur5.swf</strBodyFileName>
<strHeadFileName>monster-minotaur2-head.swf</strHeadFileName>
<idLore_Backgrounds>5</idLore_Backgrounds>
<intMaxPackSize>2</intMaxPackSize>
<dateMonsterUpdated>2013-11-18T21:15:23</dateMonsterUpdated>
<strWeaponElement>Earth</strWeaponElement>
<strWeaponType>Melee</strWeaponType>
<strBackgroundTerrain>dungeon</strBackgroundTerrain>
<strBackgroundFileName>background-dungeon2.swf</strBackgroundFileName>
<intPower>100</intPower>
<numPower>1.000000</numPower>
</monster>
</monsterload>

Can I make a MYSQL insert statement out of this? (automatically). What i do right now is convert it into csv, then convert the csv to MYSQL, using online tools, which is very slow. Here is an example SQL code, first few lines:

 INSERT INTO `tablename` (`IdLore_Monsters`, `strMonsterName`, `strDescription`, `Level`))
 VALUES
(99, 'Young Minotaur', 'Not quite as experienced as an older Minotaur warrior, young Minotaurs are still quite dangerous and guard their treasure with equal ferocity if not strength.', '20');

etc.

Hi and welcome to Stack Overflow. This question, as it stands, is unlikely to get answered - Stack Overflow is not a programming service. If you have existing code please include it in your question. — l0b0, Nov 22 '14 at 16:32

Borodin · Answer 1 · 2014-11-22T17:03:24.143

l0b0 is correct. You really should make an effort to solve this problem yourself rather than simply hoping for a free solution from some anonymous expert in the cloud. However in this case you are lucky as it took my fancy to write some code to do this.

It is good to remember that Stack Overflow isn't a forum, and its primary purpose isn't to provide support for each individual's difficulties, but to build a library of generally-applicable solutions to common programming problems. That means you should be the least of my concerns, and I should focus on creating as generally-useful an answer as possible. With this in mind it is best to be a little more humble when you are asking a question, and you should start by searching the site for previous solutions that you can use in your situation.

It is always best to use a proper XML parsing module to process XML. My example below shows a solution that uses XML::Twig, which isn't a core module and is likely to need installing.

I have written a helper subroutine that guesses whether a data item should be quoted depending on whether it "looks like a number". If you are using DBI to manipulate the database then it is far better to use placeholders in the SQL statement that you pass to prepare, and supply the actual values in the call to execute.

use strict;
use warnings;

use XML::Twig;
use Scalar::Util qw/ looks_like_number /;

my $twig = XML::Twig->new;
$twig->parsefile('monsterload.xml');

my @columns = qw/ idLore_Monsters strMonsterName strDescription intLevel /;

for my $monster ($twig->get_xpath('/monsterload/monster')) {
  printf "INSERT INTO `tablename` (%s)\nVALUES (%s)\n",
      join(', ', map "`$_`", @columns),
      join(', ', map monster_field($monster, $_), @columns);
}

sub monster_field {
  my ($monster, $field_name) = @_;

  my $value = $monster->field($field_name);
  $value = "'$value'" unless looks_like_number($value);
  $value;
}

output

INSERT INTO `tablename` (`idLore_Monsters`, `strMonsterName`, `strDescription`, `intLevel`)
VALUES (99, 'Young Minotaur', 'Not quite as experienced as an older Minotaur warrior, young Minotaurs are still quite dangerous and guard their treasure with equal ferocity if not strength.', 20)

I did actually try a few things with `xmllint`, I didn't include them because they didn't work. Thank you for the response, and I understand that and I will include my own code in my future questions. However, i though that XML to SQL insert is a fairly common programming thing? And to be on topic to your answer, can i just add the other XML nodes to the `@columns` value to convert all the nodes to SQL? — MobilenJakob, Nov 22 '14 at 17:49
You may be correct about the commonality of XML to SQL conversion, but it isn't something I've had to deal with. And yes, whatever identifiers you put into `@columns` will be transferred to the `INSERT` statement that is printed. It would be very simple to try it! It will also print a new `INSERT` statement for each `` element within the root `` element. — Borodin, Nov 22 '14 at 18:06
A quick search comes up with [this question](http://stackoverflow.com/questions/13833568) — Borodin, Nov 22 '14 at 18:10

How do i Convert XML to MYSQL insert using the UNIX command line?

1 Answers1