Using sqlToXml
The sqlToXml program is a convenient way to output all or part of a relational database as XML. I describe how to use the program here. For a more detailed description, see the sqlToXml.doc file in the program sources. The inputs to sqlToXml are a MySQL database, an "object dump" (.OD) file, and optionally a SQL statement to limit which parts of the database are dumped. The output is a subset of XML, which is particularly easy to parse.
Listing Seven (available electronically) shows an example of a sqlToXml dump for a simple relational database containing polygon and point tables. Each row in a table is output as a single tag in the XML. Each column in the table is represented as an attribute. Each tag is output in a single line, with child tags indented relative to parents. When there are no children, the closing tag is incorporated into the start tag.
The outermost tag in the XML file does not correspond to a table. By default, sqlToXml fills it in using the name of the database. The next tag, "polygon," corresponds to the "master table" being dumped. The dump has one record for each row in the master table. Optionally, a SQL statement can limit this to just a subset of rows.
Listing Eight (available electronically) is a .OD file that might produce a dump like this. The master table appears on the first line. In subsequent lines, fields are indented relative to the table they belong to. By default, sqlToXml outputs all fields. Fields only need to be mentioned if they link to a child table, or if you want to hide them. In this .OD file, I hide a bounding-box field and link to the point table via the id field. The format of a linking field is first the field name (in the parent table) followed by table.field in the child table. If you want the XML output to use a tag name different from table name, you can add an "as" clause. The children can themselves have children, though in this case, the hierarchy is only two deep.
