• 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
  • Opening a New DB-to-XML Data Source Document
  • Specifying Connection Settings
  • Working with Scenarios
  • Composing SQL/XML in Stylus Studio
  • Working with Relational Data as XML
    • Understanding SELECT and UPDATE
    • Example
  • Using the URL Builder
• Chapter 13: Extending Stylus Studio
• Chapter 14: Stylus Studio GUI Reference

Home > Online Product Documentation > Table of Contents > Working with Relational Data as XML

Working with Relational Data as XML

As shown in the example in the preceding section, an SQL/XML query is returned as XML. The XML is displayed in the Preview window in the DB-to-XML data source editor, as shown in Figure 318.

Figure 318. SQL/XML Query Returned as XML

You can work with an SQL/XML query result as XML and, optionally, update the relational database using changes to the XML file and the SQL/XML statements defined on the UPDATE tab of the DB-to-XML data source editor.

Understanding SELECT and UPDATE

As described previously, you define SQL/XML SELECT statements on the SELECT tab; you define INSERT and UPDATE statements on the UPDATE tab. The DB-to-XML data source simply presents the SQL/XML to the database. The database executes the SQL/XML to the best of its ability, based on the statements you have defined and the permissions of the user associated with the DB-to-XML data source. If the user has read-only permissions, for example, any INSERT or UPDATE statement will fail.

When Statements are Executed

Statements on the SELECT tab are executed when you click the Execute Query button ( ). Statements on the UPDATE tab, on the other hand, are executed only when an XML version of the DB-to-XML data source document is saved. (The Execute Query button is disabled when the UPDATE tab is active.)

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 318 , 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 319. 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.