Example

This example shows how to define a DB-to-XML data source, and how DB-to-XML data sources interact with a relational database.

Consider a DB-to-XML data source that performs a simple SELECT and INSERT.

The SELECT Statement

The SELECT statement is defined on the SELECT tab of the DB-to-XML editor:


SELECT * FROM dbo.authors

This SELECT statement returns every author record from the dbo.authors table. Thus, the XML will have one <row> element for each author record in the table. There are currently 25 author records in the dbo.authors table.

The INSERT Statement

The INSERT statement is defined on the UPDATE tab of the DB-to-XML data source editor:

INSERT xml_document(?)
               
 
               
INTO dbo.authors 
(au_id,au_lname,au_fname,phone,address,city,state,zip,contract)
               
 
               
xml_row_pattern('/root/row')
               
 
               
VALUES(
               
	xml_xpath('au_id/text()','VARCHAR'),
               
	xml_xpath('au_lname/text()','VARCHAR'),
               
	xml_xpath('au_fname/text()','VARCHAR'),
               
	xml_xpath('phone/text()','CHAR'),
               
	xml_xpath('address/text()','VARCHAR'),
               
	xml_xpath('city/text()','VARCHAR'),
               
	xml_xpath('state/text()','CHAR'),
               
	xml_xpath('zip/text()','CHAR'),
               
	xml_xpath('contract/text()','BIT'))

This INSERT statement attempts to insert the entire XML document content into table dbo.authors, matching the document contents using xml_row_pattern(`/root/row').

Saving the File

After executing the query from the DB-to-XML data source editor, we examine the query results, which are rendered as XML in the Preview window. (See Figure 392 , for an example of how SQL/XML query results are displayed in Stylus Studio.) Checking the results allows us to verify that the table we are querying is providing us with the data we expect.

Once we have verified the query result, we save the .rdbxml file. Although this file contains both SELECT and UPDATE statements, it is important to note that the SQL/XML defined on the UPDATE tab has not been executed at this point.

Opening the .rdbxml as XML

Next, we open the .rdbxml file using the XML editor, as shown here:

Figure 393. Opening a DB-to-XML Data Source as XML

When the DB-to-XML data source is opened in the XML editor, it looks just like any other XML document - it displays the XML returned when we executed the SQL/XML query. Other data source attributes (connection settings and the SQL/XML statements themselves) are not accessible or visible when a data source is opened as XML, but they are present as metadata.

Updating the Data in the Database

To update the data from the authors table in the database, we modify the XML in the .rdbxml file, adding the following record (a new <row> element):

<row>
               
        <au_id>781-23-4956</au_id>
               
        <au_lname>Black</au_lname>
               
        <au_fname>Frank</au_fname>
               
        <phone>301 282-7361</phone>
               
        <address>23 Bishop St.</address>
               
        <city>Baltimore</city>
               
        <state>MD</state>
               
        <zip>21281</zip>
               
        <contract>false</contract>
               
</row>

The document now contains a total of 26 <row> elements - the 25 that were read from the database, and the one which we just added. If we save this document as it is currently written, the INSERT statement we defined in the DB-to-XML data source will attempt to insert the entire document back into the database - all 26 <row> elements.

Similarly, if we deleted all existing <row> elements from the document, and saved the .rdbxml file with only the new record we created (the <row> element for author Frank Black), only that record would be added to table dbo.authors.


Note		How the INSERT operation behaves - whether or not duplicate records are created on the database, for example - is determined by the controls for data integrity established by the database administrator.