The XML FAQ

Almost all database management systems now provide XML import and export modules to connect XML applications with databases.

In some trivial cases there will be a 1:1 match between field names in the database table and element type names in the XML Schema or DTD, but in most cases some programming will be required to establish the desired match. This can usually be stored as a procedure so that subsequent uses are simply commands or calls with the relevant parameters.

Alternatively, most database systems now provide an XML dump format that lets you export a table as-is, for example by surrounding the field values with tags called after the fieldnames. For example, the -X option to the mysql command will do this, eg

$ echo 'select * from news;' | mysql -X -u username -p password dbname

<?xml version="1.0"?>
<resultset statement="select * from news" 
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
	<field name="id">1</field>
	<field name="stamp">0</field>
	<field name="title"></field>
	<field name="date">0000-00-00</field>
	<field name="time">test</field>
	<field name="description">News Engine test item 1</field>
  </row>
</resultset>
$
	

In less trivial, but still simple, cases, you could export by writing a report routine that formats the output as an XML document by adding the relevant tags as literals before and after each data value; and you could import by writing an XSLT or similar transformation that formatted the XML data as a load file in your database's preferred format. For example, with the following data:

<news>
  <entry xml:id="N1" stamp="0" date="0000-00-00" time="test">
    <title></title>
    <description>News Engine test item 1</description>
  </entry>
</news>
	

you could turn it into a MySQL statement with lxprintf:

$ lxprintf -e entry \
  'INSERT INTO `news` VALUES (%s,%s,"%s","%s","%s","%s");\n' \
  'substring(@xml:id,2)' @stamp title @date @time description \
	mynews.xml | mysql -u username -p password dbname
	

This feeds the following command into MySQL

INSERT INTO `news` VALUES (1,0,"","0000-00-00","test","News Engine test item 1")
	

Users from a database or computer science background should be aware that XML is not a database management system: it is a text markup system. While there are many similarities, some of the concepts of one are simply non-existent in the other: XML does not possess some database-like features in the same way that databases do not possess markup-like ones. It is a common error to believe that XML is a DBMS like Oracle or Access and therefore possesses the same facilities. It doesn't.

Database users should read the article (Salminen and Tompa, 2001) [thanks to Bart Lateur for identifying this.] Ronald Bourret also maintains a good resource on XML and Databases discussing native XML databases at http://www.rpbourret.com/xml/XMLAndDatabases.htm.

There is some information about the XQuery (XQL) Language in the note on Searching.