• 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
    • Manually Entering SQL/XML
    • Using Drag-and-Drop
    • How Relational Data is Translated to XML
  • Working with Relational Data as XML
  • Using the URL Builder
• Chapter 13: Extending Stylus Studio
• Chapter 14: Stylus Studio GUI Reference

Home > Online Product Documentation > Table of Contents > Composing SQL/XML in Stylus Studio

Composing SQL/XML in Stylus Studio

There are two ways to compose SQL/XML in Stylus Studio:

You can enter any valid SQL/XML statements in the SQL/XML editor's SELECT and UPDATE tabs, though only those statements for which you have permissions will be executed.

Manually Entering SQL/XML

When you type a qualified name in the SELECT or UPDATE tabs of the editor, Stylus Studio's auto-completion feature displays a list of valid names. For example, when you type the period in this statement, SELECT * FROM dbo., Stylus Studio displays the following:

Figure 317. Example of Stylus Studio's Auto-Completion

To finish the statement, select the object name from the drop-down list and press Enter (or just double-click).

Using Drag-and-Drop

When you use drag-and-drop to compose SQL/XML, you start by selecting an object (a table or a column, for example) from the database schema tree and dragging it to the SQL/XML editor. When you drop the object (by releasing the mouse button) on the editor, Stylus Studio displays a shortcut menu of choices for that object. These include

Example

Following is an example of the INSERT statement Stylus Studio creates using the publishers table:

INSERT xml_document(?)
               
 
               
INTO dbo.publishers (pub_id,pub_name,city,state,country)
               
 
               
xml_row_pattern('/root/row')
               
 
               
VALUES(
               
	xml_xpath('pub_id/text()','CHAR'),
               
	xml_xpath('pub_name/text()','VARCHAR'),
               
	xml_xpath('city/text()','VARCHAR'),
               
	xml_xpath('state/text()','CHAR'),
               
	xml_xpath('country/text()','VARCHAR'))
               

            

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:

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>.