Database Reference
In-Depth Information
use
DBI
;
use
XML::
XPath
;
#
...
process
command
-
line
options
(
not
shown
)
...
#
...
connect
to
database
(
not
shown
)
...
# Open file for reading
my
$xp
=
XML::
XPath
->
new
(
filename
=>
$file_name
);
my
$row_list
=
$xp
->
find
(
"//row"
);
# find set of <row> elements
print
"Number of records: "
.
$row_list
->
size
()
.
"\n"
;
foreach
my
$row
(
$row_list
->
get_nodelist
())
# loop through rows
{
my
@name
;
# array for column names
my
@val
;
# array for column values
my
$col_list
=
$row
->
find
(
"*"
);
# child columns of row
foreach
my
$col
(
$col_list
->
get_nodelist
())
# loop through columns
{
push
(
@name
,
$col
->
getName
());
# save column name
push
(
@val
,
$col
->
string_value
());
# save column value
}
# construct INSERT statement, then execute it
my
$stmt
=
"INSERT INTO $tbl_name ("
.
join
(
","
,
@name
)
.
") VALUES ("
.
join
(
","
, (
"?"
)
x
scalar
(
@val
))
.
")"
;
$dbh
->
do
(
$stmt
,
undef
,
@val
);
}
$dbh
->
disconnect
();
The script creates an
XML::XPath
object, which opens and parses the document. This
object is queried for the set of
<row>
elements, using the path
//row
. The size of this set
indicates how many rows the document contains.
To process each row, the script uses the path
*
to ask for all the child elements of the
row object. Each child corresponds to a column within the row; using
*
as the path for
get_nodelist()
this way is convenient because you need not know in advance which
columns to expect.
xml_to_mysql.pl
obtains the name and value from each column and
saves them in the
@name
and
@value
arrays. After all the columns have been extracted,
the arrays are used to construct an
INSERT
statement that names those columns that
were found to be present in the row and that includes a placeholder for each data value.
(
Recipe 2.5
discusses placeholder list construction.) Then the script executes the state‐
ment, passing the column values to
do()
to bind them to the placeholders.
In
Recipe 11.9
, we used
mysql_to_xml.pl
to export the contents of the
expt
table as an
XML document.
xml_to_mysql.pl
performs the converse operation of importing the
document back into MySQL:
%
xml_to_mysql.pl cookbook expt expt.xml
