Is there a way to convert XML to SQL INSERT using the command line, here is examples:
<something>
<somthingelse>lol</somethingelse>
</something>
would be
INSERT INTO `database` (`something`)
VALUES
(lol)
Or something like that.
-
1There is no reason why a program that can do that conversion (or even connects to the database to execute the SQL) cannot be run from the commandline. It just needs to be handed the name of the XML file (unless hardcoded) as a parameter. What programming language and library do you use for XML parsing?Anthon– Anthon2014-10-25 06:04:42 +00:00Commented Oct 25, 2014 at 6:04
-
Nothing. What i am is requesting a program that will do what i asked.DisplayName– DisplayName2014-10-25 06:12:07 +00:00Commented Oct 25, 2014 at 6:12
-
Will you never have more levels than that? Is your entire XML file always one level deep with the outer label being the table title?terdon– terdon ♦2014-10-25 10:02:03 +00:00Commented Oct 25, 2014 at 10:02
Add a comment
|
1 Answer
With xmllint using libxml version 20708:
Name of root node "something": xmllint --xpath "name()" file.xml
Text "lol": xmllint --xpath "//*/*/text()" file.xml
Script sql.sh:
#!/bin/bash
file="$1"
table=$(xmllint --xpath "name()" "$file")
value=$(xmllint --xpath "//*/*/text()" "$file")
cat << EOF
INSERT INTO \`${table}\`
VALUES
(${value})
EOF
$ ./sql.sh file.xml
Output:
INSERT INTO `something`
VALUES
(lol)
-
Could you explain what how this works? Especially the first xmllint command which produces not output on my system. Also, even if this does work, it will only work for one table and value, presumably, if the OP needed to ask this, their real data will be much larger.2014-10-25 10:07:10 +00:00Commented Oct 25, 2014 at 10:07
-
What if there are multiple root nodes?DisplayName– DisplayName2014-10-25 11:05:31 +00:00Commented Oct 25, 2014 at 11:05
-
Only with one root it's a valid XML file.Cyrus– Cyrus2014-10-25 11:08:30 +00:00Commented Oct 25, 2014 at 11:08
-
But there can be nodes inside the root node.DisplayName– DisplayName2014-10-25 15:52:20 +00:00Commented Oct 25, 2014 at 15:52