XML and MySQL
Open xslt/mods2sql.xsl in your Web browser or text editor. Notice how it outputs plain text. Notice how it outputs SQL commands to create a database table called items. Notice how it loops through each MODS record selectively extracting Dublin Core-like elements and putting them into SQL in- sert statements.
Open a command prompt and change to the root of the workshop's distribution.
Use xsltproc to transform the MODS data into SQL and save it to a file: xsltproc xslt/mod2sql.xsl xml-data/mods/single/catalog/catalog.xml > sql/catalog/catalog.sql .
When xsltproc is finished, open sql/catalog/catalog.sql to see the fruits of your labors. You should see oodles of SQL insert statements.
Getting XML output from MySQL
Given some input you will be able to extract data in a rudimentary XML flavor. Begin by creating a new database called catalog using the mysqladmin command: mysqladmin -p root create catalog .
Using data created from the previous exercise you should now be able to fill this new database with con- tent using the mysql client. First, change directories to the root level of this workbook's distribution, and then run this command: mysql -u root catalog < sql/catalog/catalog.sql .
You should now be able to see the fruits of your labors by running the mysql client again and exploring your new database. First, connect to the newly populated database: mysql -u root catalog . Once con- nected run the following SQL commands at the prompt to see what happened and what is in the data- base:
select title from items;
select title from items order by sort_title;
select count(id) as 'number of records' from items;
select title, creator from items limit 1, 10;
select concat(title, ' ', responsibility) as 'title/author list' from items limit 1, 10;
select title from items where title like '%blues%';
When finished, quit the mysql client by entering \q at the mysql prompt.
MySQL provides the ability to dump the content of a database in an XML form. To do this you use the mysqldump command with the --xml option. Something like this should work: mysqldump --xml -u root catalog . The output will look something like this: