Channels ▼


XML, SQL, and C

Source Code Accompanies This Article. Download It Now.

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.

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.