• Chapter 1: Getting Started with Stylus Studio®
• Chapter 2: Editing and Querying XML
• Chapter 3: Converting Non-XML Files to XML
• Chapter 4: Working with XSLT
• Chapter 5: Creating XSLT Using the XSLT Mapper
• Chapter 6: Debugging Stylesheets
• Chapter 7: Defining XML Schemas
• Chapter 8: Defining Document Type Definitions
• Chapter 9: Writing XPath Expressions
• Chapter 10: Working with XQuery in Stylus Studio
• Chapter 11: Composing Web Service Calls
• Chapter 12: Accessing Relational Data as XML
  • Overview of DB-to-XML Data Sources
  • Creating a DB-to-XML Data Source
  • Specifying Connection Settings
  • Using an ODBC Data Source in Stylus Studio
  • Working with Scenarios
  • Composing SQL/XML in Stylus Studio
  • Working with Relational Data as XML
    • Understanding SELECT and UPDATE
    • Example
      • The SELECT Statement
      • The INSERT Statement
      • Saving the File
      • Opening the .rdbxml as XML
      • Updating the Data in the Database
• Chapter 13: Integrating with Third-Party File Systems
• Chapter 14: Extending Stylus Studio
• Chapter 15: The Stylus Studio Java API
• Chapter 16: Stylus Studio GUI Reference

Home > Online Product Documentation > Table of Contents > Example

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.