How Relational Data is Translated to XML

Consider the following illustration of an excerpt from the authors table in the Microsoft SQL Server pubs database. This illustration shows only the first six columns of the table (and only the first few records); the state, zip, and contract columns have been omitted for clarity:


au_id	au_lname	au_fname	phone	address	city
213-46-8915	Green	Marjorie	415 986-7020	309 63rd St. #411	Oakland
238-95-7766	Carson	Cheryl	415 548-7723	589 Darwin Ln.	Berkeley
267-41-2394	O'Leary	Michael	408 286-2428	22 Cleveland Av. #14	San Jose

As you can see, the authors table has columns for author ID ( au_id), author's last name ( au_lname), author's first name ( au_fname), and so on.

If we write a SELECT statement ( SELECT * FROM dbo.authors), Stylus Studio returns the following XML document:

<?xml version="1.0" encoding="UTF-8" ?>
               
<root>
               
    <row>
               
        <au_id>213-46-8915</au_id>
               
        <au_lname>Green</au_lname>
               
        <au_fname>Marjorie</au_fname>
               
        <phone>415 986-7020</phone>
               
        <address>309 63rd St. #411</address>
               
        <city>Oakland</city>
               
        <state>CA</state>
               
        <zip>94618</zip>
               
        <contract>true</contract>
               
    </row>
               
    <row>
               
        <au_id>238-95-7766</au_id>
               
        <au_lname>Carson</au_lname>
               
        <au_fname>Cheryl</au_fname>
               
        <phone>415 548-7723</phone>
               
        <address>589 Darwin Ln.</address>
               
        <city>Berkeley</city>
               
        <state>CA</state>
               
        <zip>94705</zip>
               
        <contract>true</contract>
               
    </row>
               
    <row>
               
        <au_id>267-41-2394</au_id>
               
        <au_lname>O&apos;Leary</au_lname>
               
        <au_fname>Michael</au_fname>
               
        <phone>408 286-2428</phone>
               
        <address>22 Cleveland Av. #14</address>
               
        <city>San Jose</city>
               
        <state>CA</state>
               
        <zip>95128</zip>
               
        <contract>true</contract>
               
    </row>
               
</root>

Notice that each record in the table (that is, each author) is rendered as a separate <row> element. Similarly, each column is rendered as a subelement of <row>, taking the column name (au_id, au_lname, and so on) as its own ( <au_id>, <au_lname>, and so on). The document's root element is given the name <root>.

< Previous | Next >

How Relational Data is Translated to XML

XQuery Lowering the Cost of Healthcare

XSD Mapping

Scientific, Photo & Control Equipment

HTML Tidy

Stylus Most Wanted