Relational Databases

The most common databases in use today fit into the relational model. Data is stored as logical rows in one or more tables. Manipulation is typically accomplished with a dialect of Structured Query Language (SQL). Two of the most common relational databases in use today that provide XML features are Microsoft SQL Server and Oracle.

Microsoft SQL Server 2005

Microsoft SQL Server, currently version 2005, is a popular and powerful database server. XML support, including XQuery support and the addition of an XML column type, is one of the primary areas of improvement in this version

Retrieving XML

SQL Server's T-SQL dialect includes the FOR XML clause for SELECT queries. This clause, which must be the last clause in the SELECT statement, causes the data returned from the query to be formatted as XML. This feature was first added with SQL Server 2000, but it has been improved in SQL Server 2005. The actual format of the XML is configurable using one of the optional keywords listed in the following table.

Open table as spreadsheet

FOR XML Formatting	Notes
RAW	Each row in the query is returned as an XML element. Individual columns are returned as attributes of that element. There is no root node by default, although this can be added. By default, the element name is row. This can be changed by including the name as a parameter to RAW (FOR XML RAW(‘myrowname’)).
AUTO	Each row is returned as an XML element named for the table providing the data. Individual columns returned are attributes of that element. There is no root node by default. If related columns are included, the resulting XML is nested.
EXPLICIT	The structure of the resulting XML must be defined. This provides the most flexibility in creating XML, but also requires the most work by the developer.
PATH	The structure of the resulting XML can be defined. This method, added with SQL Server 2005, is much easier to use than the EXPLICIT model. By default, it creates a structure similar to the AUTO output, but columns are output as elements, not attributes.

RAW format is, as the name implies, the rawest output of SQL data to XML. It generates a document fragment (that is, the result is not a well-formed document because no a single root node exists). Listing 11-1 shows part of the output from the following simple raw query on the Northwind sample database.

Note 

The Northwind sample database is available for download from Microsoft at http://www.microsoft.com/downloads/details.aspx?FamilyID=&displaylang=en.

      SELECT CategoryName, ProductName, UnitPrice      FROM Categories INNER JOIN Products      ON Categories.CategoryID = Products.CategoryID      WHERE CategoryName='Beverages'      ORDER BY ProductName      FOR XML RAW 

Listing 11-1: Output of FOR XML RAW query

      <row CategoryName="Beverages" ProductName="Chai" UnitPrice="18.0000" />      <row CategoryName="Beverages" ProductName="Chang" UnitPrice="19.0000" />      <row CategoryName="Beverages" ProductName="Chartreuse verte" UnitPrice="18.0000" />      <row CategoryName="Beverages" ProductName="Côte de Blaye" UnitPrice="263.5000" />      <row CategoryName="Beverages" ProductName="Guaraná Fantástica" UnitPrice="4.5000" />      <row CategoryName="Beverages" ProductName="Ipoh Coffee" UnitPrice="46.0000" />      <row CategoryName="Beverages" ProductName="Lakkalikööri" UnitPrice="18.0000" />      <row CategoryName="Beverages" ProductName="Laughing Lumberjack Lager"      UnitPrice="14.0000" />      <row CategoryName="Beverages" ProductName="Outback Lager" UnitPrice="15.0000" />      <row CategoryName="Beverages" ProductName="Rhönbräu Klosterbier" UnitPrice="7.7500" />      <row CategoryName="Beverages" ProductName="Sasquatch Ale" UnitPrice="14.0000" />      <row CategoryName="Beverages" ProductName="Steeleye Stout" UnitPrice="18.0000" /> 

The AUTO format takes a few guesses about the structure of the resulting XML. It then structures the XML to nest child data appropriately. The output from an AUTO query can be either a complete document or a document fragment, depending on the query. Listing 11-2 shows the output for the following query:

      SELECT CategoryName, ProductName, UnitPrice      FROM Categories INNER JOIN Products      ON Categories.CategoryID = Products.CategoryID      WHERE CategoryName='Beverages'      ORDER BY ProductName      FOR XML AUTO 

Listing 11-2: Output for FOR XML AUTO query

      <Categories CategoryName="Beverages">        <Products ProductName="Chai" UnitPrice="18.0000" />        <Products ProductName="Chang" UnitPrice="19.0000" />        <Products ProductName="Chartreuse verte" UnitPrice="18.0000" />        <Products ProductName="Côte de Blaye" UnitPrice="263.5000" />        <Products ProductName="Guaraná Fantástica" UnitPrice="4.5000" />        <Products ProductName="Ipoh Coffee" UnitPrice="46.0000" />        <Products ProductName="Lakkalikööri" UnitPrice="18.0000" />        <Products ProductName="Laughing Lumberjack Lager" UnitPrice="14.0000" />        <Products ProductName="Outback Lager" UnitPrice="15.0000" />        <Products ProductName="Rhönbräu Klosterbier" UnitPrice="7.7500" />        <Products ProductName="Sasquatch Ale" UnitPrice="14.0000" />        <Products ProductName="Steeleye Stout" UnitPrice="18.0000" />      </Categories> 

As you can see from the output, the products are nested within the categories node because this is the relationship between the two tables. In this case, the output is well-formed; however, if the WHERE clause hadn't been included, the result would have been a document fragment with multiple Categories nodes and no single root node.

EXPLICIT format is more complex than the preceding two formats because it has no default output. The developer is responsible for defining the structure of the resulting XML. When defining an EXPLICIT query, you must add two columns to the query. These two provide the relationship between each row and its parent. Figure 11-1 shows the desired structure of the output, if it is returned normally.

Figure 11-1

            SELECT 1    as Tag,             NULL as Parent,             C.CategoryID as [cat!1!id],             NULL        as [prod!2!name],             NULL        as [prod!2!price]      FROM   Categories C, Products P      WHERE  C.CategoryID = P.CategoryID      UNION      SELECT 2 as Tag,             1 as Parent,             P.CategoryID,             ProductName,             UnitPrice      FROM   Categories C, Products P      WHERE  C.CategoryID = P.CategoryID      ORDER BY [cat!1!id],[prod!2!name]      FOR XML EXPLICIT 

The Tag column identifies each level in the generated XML, whereas the Parent column identifies the Tag representing the parent of each item. For the top level elements, the Parent column should be NULL. In addition, placeholder fields must be added to the root element. This is the purpose of the two NULL entries in the first half of the UNION query. Finally, the ElementName!TagNumber!AttributeName!Directive syntax is used to shape the resulting XML. The term [cat!1!id] causes the element name to be cat with an attribute id, and this element is placed at the root. [prod!2!name] is placed as a child element because of the position=2. It assigns the element and attribute names to prod and name respectively.

Running the preceding query returns the XML shown in Listing 11-3 (not all the XML is shown).

Listing 11-3: Output for FOR XML EXPLICIT query

      <cat >        <prod name="Chai" price="18.0000" />        <prod name="Chang" price="19.0000" />        <prod name="Chartreuse verte" price="18.0000" />        <prod name="Côte de Blaye" price="263.5000" />        <prod name="Guaraná Fantástica" price="4.5000" />        <prod name="Ipoh Coffee" price="46.0000" />        <prod name="Lakkalikööri" price="18.0000" />        <prod name="Laughing Lumberjack Lager" price="14.0000" />        <prod name="Outback Lager" price="15.0000" />        <prod name="Rhönbräu Klosterbier" price="7.7500" />        <prod name="Sasquatch Ale" price="14.0000" />        <prod name="Steeleye Stout" price="18.0000" />      </cat> 

The PATH format is new with SQL Server 2005. It provides an easier model for manipulating the output than using EXPLICIT queries. Creating PATH queries is based on the aliases assigned to the result columns. If the alias starts with a @ character, the data is placed in an attribute. If the alias contains one or more / characters, these create child elements. You could view this as XPath in reverse. For example, in the following query, the field aliased as product/name creates a new child element named product. That element then has a name child. Listing 11-4 shows the output from this query.

      SELECT ProductID "@id",       CategoryName "category",       ProductName "product/name",       UnitPrice "product/price"      FROM Categories INNER JOIN Products      ON Categories.CategoryID = Products.CategoryID      WHERE CategoryName='Beverages'      ORDER BY ProductName      FOR XML PATH 

Listing 11-4: Partial output for FOR XML PATH query

      <row >        <category>Beverages</category>        <product>          <name>Chai</name>          <price>18.0000</price>         </product>      </row>      <row >        <category>Beverages</category>        <product>          <name>Chang</name>          <price>19.0000</price>         </product>      </row>      <row >        <category>Beverages</category>        <product>          <name>Chartreuse verte</name>          <price>18.0000</price>         </product>      </row>      <row >        <category>Beverages</category>        <product>          <name>Côte de Blaye</name>          <price>263.5000</price>         </product>      </row>      <row >        <category>Beverages</category>        <product>          <name>Guaraná Fantástica</name>          <price>4.5000</price>        </product>      </row> 

In addition to the parameters outlined here, you can also manipulate the resulting XML to change the resulting structure. The following table shows additional keywords and the formats they can be used with.

Open table as spreadsheet

Directive	Can be used with	Notes
ELEMENTS	AUTO, RAW, PATH	Causes the column values to be output as elements, not attributes.
ROOT	any	Adds a root node to the resulting XML. The name defaults to root, but you can change this by adding the name as a parameter to the ROOT keyword (FOR XML AUTO, ROOT(‘rootElement’)) (see Listing 11-5).
TYPE	any	Ensures that the output is treated as XML. This becomes important when you are assigning the output of the query to the XML data type in T-SQL. Alternatively, if you are building the XML using nested queries, failing to include the type may cause the inner blocks of XML to be encoded.
XMLSCHEMA	AUTO, RAW	Causes the XML Schema to be added to the resulting XML (see Listing 11-6).
XMLDATA	AUTO, RAW, EXPLICIT	Causes the XML Data Reduced schema to be included in the resulting XML.
BINARY BASE64	any	Outputs binary data Base 64 encoded. This enables output of binary data using simple ASCII.

Listing 11-5 shows an FOR XML PATH query with an additional root node added. This ensures that the resulting XML is a well-formed document.

Listing 11-5: Adding a root node to a FOR XML PATH query

      SELECT TOP 3 Products.ProductID "@id",       CategoryName "category",       ProductName "product/name",       UnitPrice "product/price"      FROM Categories INNER JOIN Products      ON Categories.CategoryID = Products.CategoryID      WHERE CategoryName='Beverages'      ORDER BY ProductName      FOR XML PATH, ROOT('catalog')      ================================      <catalog>        <row >         <category>Beverages</category>         <product>           <name>Chai</name>           <price>18.0000</price>         </product>         </row>         <row >          <category>Beverages</category>          <product>            <name>Chang</name>            <price>19.0000</price>          </product>           </row>          <row >          <category>Beverages</category>          <product>            <name>Chartreuse verte</name>            <price>18.0000</price>          </product>          </row>      </catalog> 

Queries can use as many of these additional commands as necessary. Listing 11-6 shows adding both a root node and an XML schema to an AUTO query. This would be useful when transmitting this data to another system because the schema could be then used to validate the document or to create a serializer to convert the XML into an object for further processing.

Listing 11-6: Adding an XML Schema to a FOR XML AUTO query

      SELECT CategoryName, ProductName, UnitPrice      FROM Categories INNER JOIN Products      ON Categories.CategoryID = Products.CategoryID      WHERE CategoryName='Beverages'      ORDER BY ProductName      FOR XML AUTO, ROOT('catalog'), XMLSCHEMA      ==========================================      <catalog>        <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"          xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1"          xmlns:xsd="http://www.w3.org/2001/XMLSchema"          xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"          elementFormDefault="qualified">          <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"        schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"          />          <xsd:element name="Categories">            <xsd:complexType>              <xsd:sequence>                <xsd:element ref="schema:Products" minOccurs="0" maxOccurs="unbounded" />              </xsd:sequence>              <xsd:attribute name="CategoryName" use="required">                <xsd:simpleType>                  <xsd:restriction base="sqltypes:nvarchar" sqltypes:locale                    sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth"                    sqltypes:sqlSort>                   <xsd:maxLength value="15" />                  </xsd:restriction>                </xsd:simpleType>              </xsd:attribute>            </xsd:complexType>          </xsd:element>          <xsd:element name="Products">            <xsd:complexType>              <xsd:attribute name="ProductName" use="required">                <xsd:simpleType>                  <xsd:restriction base="sqltypes:nvarchar" sqltypes:locale                    sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth"                     sqltypes:sqlSort>                   <xsd:maxLength value="40" />                  </xsd:restriction>                </xsd:simpleType>              </xsd:attribute>              <xsd:attribute name="UnitPrice" type="sqltypes:money" />            </xsd:complexType>          </xsd:element>          </xsd:schema>        <Categories xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"            CategoryName="Beverages">          <Products ProductName="Chai" UnitPrice="18.0000" />          <Products ProductName="Chang" UnitPrice="19.0000" />          <Products ProductName="Chartreuse verte" UnitPrice="18.0000" />          <Products ProductName="Côte de Blaye" UnitPrice="263.5000" />          <Products ProductName="Guaraná Fantástica" UnitPrice="4.5000"/>          <Products ProductName="Ipoh Coffee" UnitPrice="46.0000" />          <Products ProductName="Lakkalikööri" UnitPrice="18.0000" />          <Products ProductName="Laughing Lumberjack Lager" UnitPrice="14.0000" />          <Products ProductName="Outback Lager" UnitPrice="15.0000" />          <Products ProductName="Rhönbräu Klosterbier" UnitPrice="7.7500" />          <Products ProductName="Sasquatch Ale" UnitPrice="14.0000" />          <Products ProductName="Steeleye Stout" UnitPrice="18.0000" />        </Categories>      </catalog> 

Although SQL Server does not have support for the SQL/XML extensions, Microsoft is a member of the group working on the standard. As such, future versions of SQL Server may provide access to that functionality as well.

Storing XML

SQL Server 2005 adds support for the XML column type. You can create a table containing one of these columns just as you can for any other data type (see Listing 11-7).

Listing 11-7: Creating a table containing XML data in Microsoft SQL Server

      CREATE TABLE dbo.Articles(       id int IDENTITY(1,1) NOT NULL PRIMARY KEY,       Title nvarchar(255) NOT NULL,       CreatedOn datetime NOT NULL DEFAULT (getdate()),       Body xml NULL     ) 

After the table is created, you can populate and query it just as you do any other table:

      INSERT INTO dbo.Articles(Title, Body)      VALUES('Welcome',       '<div >Welcome to the system</div>')      SELECT Body FROM dbo.Articles 

Simply dumping XML into an XML column, although it is useful, has few benefits over using a text column. To improve the process, you can add an XML Schema to the column. Then, adding data to the table triggers validation, ensuring the column contains data of the appropriate type. To do this with SQL Server, you create a schema collection in the database. The CREATE XML SCHEMA COLLECTION command creates the schema collection (see Listing 11-8). In addition to adding an entry in the database for the schema, adding a schema collection to a database creates a number of new system tables and views to track the schemas, as well as support validation.

Listing 11-8: Creating an article schema collection

      CREATE XML SCHEMA COLLECTION ArticleSchemaCollection AS      '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"        elementFormDefault="qualified" attributeFormDefault="unqualified"        targetNamespace="http://example.com/articleSchema.xsd">        <xs:element name="article">          <xs:complexType>            <xs:sequence>              <xs:element name="encoding">                <xs:complexType>                  <xs:attribute name="type" />                </xs:complexType>              </xs:element>              <xs:element name="author" type="xs:string"/>             <xs:element name="body" type="xs:string"/>            <xs:element name="published" type="xs:dateTime"/>           </xs:sequence>         </xs:complexType>       </xs:element>      </xs:schema>' 

You can view the new schema collection by querying the sys.xml_schema_collections system view (see Figure 11-2).

Figure 11-2

After you have created the schema collection, you can apply it to the table. Drop the previous articles table and recreate it using the schema collection (Listing 11-9).

Listing 11-9: Applying a schema collection to a table

            CREATE TABLE dbo.Articles(       id int IDENTITY(1,1) NOT NULL PRIMARY KEY,       Title nvarchar(255) NOT NULL,       CreatedOn datetime NOT NULL DEFAULT (getdate()),       Body xml(ArticleSchemaCollection) NULL     ) 

By applying the schema collection to the XML column, SQL Server validates the data on insert/update (Listing 11-10).

Listing 11-10: Inserting into a validating column

      INSERT INTO dbo.Articles (Title, Body)          VALUES ('Validated item',                 '<article xmlns="http://example.com/articleSchema.xsd ">                   <encoding type="text/plain" />                   <author>Foo deBar ([e-mail])</author>                   <body>This item will be validated upon insert.</body>                   <published>2001-11-17T09:30:47.0Z</published>                  </article>') 

This item should be saved because it matches the schema. However, if you try the insert with invalid XML (for example, XML missing the body element), it fails. Adding a schema to validate your XML is a good idea unless you need the capability to store less-structured documents.

A second means of storing XML is via the OPENXML function, first added with SQL Server 2000. This function enables you to open a block of XML in a stored procedure. After it is open, you can perform other processing on the XML. Essentially, it converts a block of XML into rows and columns.

The format of the OPENXML function is:

      OPENXML(@doc, @xpath, @flags)      WITH row definitions 

Here, @doc points at an in-memory block of XML; @xpath is an XPath statement identifying the XML to process, and @flags provides additional hints to the processor. The WITH clause provides one or more columns into which you convert the XML.

For example, you can use the OPENXML function to extract the attributes from the XML shown in Listing 11-2 back into columns with the query shown in Listing 11-11.

Listing 11-11: Using OPENXML to extract attributes

      DECLARE @idoc int      DECLARE @doc nvarchar(1000)      SET @doc ='<Categories CategoryName="Beverages">        <Products ProductName="Chai" UnitPrice="18.0000" />        <Products ProductName="Chang" UnitPrice="19.0000" />        <Products ProductName="Chartreuse verte" UnitPrice="18.0000" />        <Products ProductName="Côte de Blaye" UnitPrice="263.5000" />        <Products ProductName="Guaraná Fantástica" UnitPrice="4.5000" />         <Products ProductName="Ipoh Coffee" UnitPrice="46.0000" />        <Products ProductName="Lakkalikööri" UnitPrice="18.0000" />        <Products ProductName="Laughing Lumberjack Lager" UnitPrice="14.0000" />        <Products ProductName="Outback Lager" UnitPrice="15.0000" />        <Products ProductName="Rhönbräu Klosterbier" UnitPrice="7.7500" />        <Products ProductName="Sasquatch Ale" UnitPrice="14.0000" />        <Products ProductName="Steeleye Stout" UnitPrice="18.0000" />      </Categories>'      -- Create an internal representation of the XML document.      EXEC sp_xml_preparedocument @idoc OUTPUT, @doc      -- Execute a SELECT statement using OPENXML rowset provider.      SELECT *      FROM OPENXML (@idoc, '/Categories/Products',1)            WITH (ProductName  nvarchar(50),                  UnitPrice decimal)      EXEC sp_xml_removedocument @idoc 

The sp_xml_preparedocument stored procedure loads the block of XML into the @idoc variable, and sp_xml_removedocument frees the memory and handles used by the variable. The OPENXML function first applies the XPath /Categories/Products to extract the individual rows. The ProductName and UnitPrice attributes are mapped to the columns identified in the WITH clause. At this point, you can walk the RowSet, perhaps saving the individual items. The preceding code simply returns the resulting RowSet, as shown in Figure 11-3.

Figure 11-3

Rather than simply returning the resulting rows and columns, you can use the OPENXML function to perform a bulk insert, as Listing 11-12 shows.

Listing 11-12: A bulk insert using OPENXML

      CREATE TABLE NewProducts(        id int identity(1,1) NOT NULL,        productName nvarchar(50) NOT NULL,        unitPrice decimal)      GO      DECLARE @idoc int      DECLARE @doc nvarchar(1000)      SET @doc ='<Categories CategoryName="Beverages">        <Products ProductName="Chai" UnitPrice="18.0000" />        <Products ProductName="Chang" UnitPrice="19.0000" />        <Products ProductName="Chartreuse verte" UnitPrice="18.0000" />        <Products ProductName="Côte de Blaye" UnitPrice="263.5000" />        <Products ProductName="Guaraná UnitPrice="4.5000" />        <Products ProductName="Ipoh Coffee" UnitPrice="46.0000" />        <Products ProductName="Lakkalikööri" UnitPrice="18.0000" />        <Products ProductName="Laughing Lumberjack Lager" UnitPrice="14.0000" />        <Products ProductName="Outback Lager" UnitPrice="15.0000" />        <Products ProductName="Rhönbräu Klosterbier" UnitPrice="7.7500" />        <Products ProductName="Sasquatch Ale" UnitPrice="14.0000" />        <Products ProductName="Steeleye Stout" UnitPrice="18.0000" />      </Categories>'      EXEC sp_xml_preparedocument @idoc OUTPUT, @doc      INSERT INTO NewProducts SELECT *      FROM OPENXML (@idoc, '/Categories/Products',1)            WITH (ProductName  nvarchar(50),                  UnitPrice decimal)      EXEC sp_xml_removedocument @idoc 

First, a new table is created. You wrap the CREATE TABLE command in an if exists statement to avoid any errors caused by the effort to create a table that already exists. The INSERT INTO clause then retrieves the list of products and creates new rows for each entry. This technique can be used to load a number of tables quickly from a block of XML.

Additional XML-related features

In addition to storing and retrieving data as XML, SQL Server 2005 adds support for exposing stored procedures or functions as Web services. These Web services are then available to clients via HTTP or TCP. This can provide a method of sharing the functionality of a SQL Server without requiring a dedicated Web service layer or a Web server. You must be running Windows Server 2003 or Windows XP Professional Service Pack 2 or later to get this functionality because it depends on the HTTP.SYS driver, which is present only on those operating systems.

To expose or not to expose?

Exposing Web Services directly from the database is a bit of a controversial exercise.

Those in favor of it believe that providing functionality like this directly from the source allows for more optimization. The database is more aware of the structure of the data and, therefore, can optimize better. For example, indexes can make data retrieval faster than is possible in the business tier code. In addition, joins and views can mean that the data required by the Web service is more readily available in the database itself. Therefore, it makes sense to provide the Web service from the database.

Developers opposed to providing Web service access to the database point out that doing so allows a dangerous direct connection to the database-possibly from the Internet. They argue that the database should be behind a firewall (if not multiple firewalls), and adding another port that can access the data means that the system is that much more vulnerable to attack.

However, keep in mind that just because you have Web services does not mean that the clients are coming from the Internet. They could be coming from within the firewall. In this case, Web services provide platform independence and reduce the need to install database client functionality on client machines. For example, using these SQL endpoints could provide access to data to Unix or other non-Windows workstations that lack SQL connectivity. Finally, Ajax clients could access the Web services without requiring database connectivity. In short, SQL endpoints become another tool in your developer's toolbox: not perfect for all scenarios, but useful when employed correctly.

The basic syntax of the CREATE ENDPOINT command is shown in Listing 11-13. Although both AS HTTP and AS TCP are shown, only one can occur per create endpoint command.

Listing 11-13: The CREATE ENDPOINT command

      CREATE ENDPOINT endPointName [ AUTHORIZATION login ]      STATE = { STARTED | STOPPED | DISABLED }      AS HTTP (      PATH = 'url',        AUTHENTICATION =({ BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ]),        PORTS = ({ CLEAR | SSL} [ ,... n ])        [ SITE = {'*' |        [, CLEAR_PORT = clearPort ]        [, SSL_PORT = SSLPort ]        [, AUTH_REALM = { 'realm' | NONE } ]        [, DEFAULT_LOGON_DOMAIN = { 'domain'| NONE }        [, COMPRESSION = { ENABLED | DISABLED } ]       )      AS TCP (        LISTENER_PORT = listenerPort        [ , LISTENER_IP = ALL | (<4-part-ip> | <ip_address_v6>) ]       )       FOR SOAP(        [ { WEBMETHOD [ 'namespace' .] 'method_alias'           (  NAME = 'database.owner.          [ , SCHEMA = { NONE | STANDARD | DEFAULT } ]          [ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]          )        } [ ,...n ] ]        [   BATCHES = { ENABLED | DISABLED } ]        [ , WSDL = { NONE | DEFAULT | 'sp_name' } ]        [ , SESSIONS = { ENABLED | DISABLED } ]        [ , LOGIN_TYPE = { MIXED | WINDOWS } ]        [ , SESSION_TIMEOUT = timeoutInterval | NEVER ]        [ , DATABASE = { 'database_name' | DEFAULT }        [ , NAMESPACE = { 'namespace' | DEFAULT } ]        [ , SCHEMA = { NONE | STANDARD } ]        [ , CHARACTER_SET = { SQL | XML }]        [ , HEADER_LIMIT = int ]       ) 

The main points to consider when creating an endpoint are:

• q What stored procedure or function (or UDF) will you be exposing? This is identified in the WebMethod clause.

• q What authentication will clients need to use? Typically, if your clients are part of the same network, you use integrated or NTLM authentication. If clients are coming across the Internet or from non-Windows, you may want to use Kerberos, digest, or basic authentication.

• q What network port will the service use? The basic choices when creating an HTTP endpoint are CLEAR (port 80) or SSL (port 443). Generally, you should use SSL if the data transmitted requires security and you are using public networks. Note that Internet Information Services (IIS) and other Web servers also use these ports. If you have both IIS and SQL Server on the same machine, you should alternate ports (using CLEAR_PORT or SSL_PORT) for your HTTP endpoints. When creating TCP endpoints, you should select a LISTENER_PORT that is unused on your server.

Listing 11-14 shows the creation of an HTTP endpoint exposing the system stored procedure sp_monitor.

Listing 11-14: Creating an HTTP endpoint

      CREATE ENDPOINT SampleEndpoint      STATE = STARTED      AS HTTP(         PATH = '/sql',         AUTHENTICATION = (INTEGRATED),         PORTS = (CLEAR),         CLEAR_PORT = 8888,         SITE = 'localhost'        )      FOR SOAP (         WEBMETHOD 'GetServerStats'                   (name='master.sys.sp_monitor'),         WSDL = DEFAULT,         SCHEMA = STANDARD,         DATABASE = 'master',         NAMESPACE = 'http://tempUri.org/'        );      GO 

The CREATE ENDPOINT command creates an endpoint that responds to SOAP requests via HTTP. In this case, the endpoint is created on the local instance of SQL Server. One Web method is created in the preceding sample. However, you can add multiple WEBMETHOD clauses to create multiple methods in a single call. Figure 11-4 shows a portion of the WSDL dynamically created by querying the new endpoint (http://www.localhost:8888/sql?WSDL).

Figure 11-4

After a method is created, you can use any SOAP client to bind to the WSDL and call the HTTP endpoint on the SQL Server. Figure 11-5 shows a portion of the result when you call this Web service using XML Spy.

Figure 11-5

One last feature of SQL Server 2005 is not directly related to XML, but can provide a great deal of assistance when working with XML: the capability to use C# or Visual Basic to create stored procedures. This provides a number of benefits in some scenarios, most notably the availability of .NET classes (including the System.Xml namespace) for processing data. This means you can perform XPath or XSLT processing within the stored procedure, giving you additional flexibility in processing XML, either for storage or retrieval.

Oracle 10g

Oracle and databases are synonymous for many developers. Oracle is frequently at the forefront in providing database functionality, and its support for XML is yet another example of this. Oracle 10g includes support for SQL/XML and XQuery via the XML DB subsystem. In addition to SQL/XML and XQuery, Oracle adds hierarchical indices, a number of PL/SQL extensions for working with XML.

Retrieving XML

Retrieving XML from Oracle is based on the emerging SQL/XML standard. You can use the operators defined in the standard to construct queries that output XML. Oracle supports most of the proposed operators. However, the XML type in the standard is implemented using the XMLType keyword.

To query the table shown in Figure 11-6 and return XML, you construct the following query:

      SELECT XMLElement("emp", XMLAttributes(employee_id AS id),                XMLElement("fname",first_name),                XMLElement("lname",last_name),                XMLElement("email", email))      FROM employees      WHERE last_name LIKE 'L%'; 

Figure 11-6

The SQL/XML operators convert the data returned into XML elements and attributes. As you can see in the preceding query, the employee_id field is converted into an attribute and applied to a newly created emp element, showing that you can easily create elements as needed. As the first and last names are enclosed within the emp element, the resulting elements are created as child elements. Listing 11-15 shows the output of the query.

Listing 11-15: Output from SQL/XML query

      <emp >        <fname>Renske</fname>        <lname>Ladwig</lname>        <email>RLADWIG</email>      </emp>      <emp >        <fname>James</fname>        <lname>Landry</lname>        <email>JLANDRY</email>      </emp>      <emp >        <fname>David</fname>        <lname>Lee</lname>        <email>DLEE</email>      </emp>      <emp >        <fname>Jack</fname>        <lname>Livingston</lname>        <email>JLIVINGS</email>      </emp>      <emp >        <fname>Diana</fname>        <lname>Lorentz</lname>        <email>DLORENTZ</email>      </emp> 

Rather than listing each of the child elements separately, you can use the XMLForest command to simplify the query (the following query generates the same output as Listing 11-15):

      SELECT XMLElement("emp",        XMLAttributes(employee_id AS id),           XMLForest(first_name AS "fname",                    last_name AS "lname",                    email AS "email"))      FROM employees      WHERE last_name LIKE 'L%'; 

The XMLForest command provides an easy means of adding multiple elements using the one command. Each of the elements listed in the XMLForest command is a child element of the preceding XMLElement.

The SQL/XML extensions enable you to define complex queries and provide a syntax similar to the normal SELECT. For example, the query in Listing 11-16 retrieves the employees and groups them by their departments.

Listing 11-16: Retrieving employees and departments

            SELECT XMLElement("dept",             XMLAttributes(d.department_name AS "Name"),                XMLAgg(                  XMLElement("emp",                             XMLAttributes(employee_id AS id),                     XMLForest(e.first_name AS "fname",                              e.last_name AS "lname",                              e.email AS "email")))) AS out      FROM departments d INNER JOIN employees e           ON d.department_id = e.department_id      GROUP BY d.department_name; 

As before, the XMLElement and XMLAttributes commands are used to identify the tags to use. The XMLAgg command is used to group the employees based on the GROUP BY clause. Listing 11-17 shows part of the output of this query.

Listing 11-17: Employees and departments

      <dept Name="Accounting">        <emp ><fname>Shelley</fname><lname>Higgins</lname></emp>       <emp ><fname>William</fname><lname>Gietz</lname></emp>      </dept>      <dept Name="Administration">        <emp ><fname>Jennifer</fname><lname>Whalen</lname></emp>      </dept>      <dept Name="Executive">        <emp ><fname>Steven</fname><lname>King</lname></emp>        <emp ><fname>Neena</fname><lname>Kochhar</lname></emp>        <emp ><fname>Lex</fname><lname>De Haan</lname></emp>      </dept> 

Storing XML

Oracle supports storing XML in database tables using the XMLType column type. This type is similar to the CLOB type in that it stores large amounts of text. In addition to standard CLOB behavior, it ensures that the resulting data is well-formed XML and provides a number of helper methods for working with the resulting document. Listing 11-18 creates a table that stores XML in one column.

Listing 11-18: Creating a table containing XML data in Oracle 10g

      CREATE TABLE  "ARTICLES"      (        "ID" NUMBER NOT NULL ENABLE,        "TITLE" VARCHAR2(255) NOT NULL ENABLE,        "BODY"  "XMLTYPE",        CONSTRAINT "ARTICLES_CON" PRIMARY KEY ("ID") ENABLE     )      INSERT INTO Articles(id, Title, Body) VALUES (24, 'An article title',        XMLType('<article>            <author>Foo deBar ([e-mail])</author>             <body>This is the article body.</body>           </article>')); 

The XMLType operator converts the enclosed XML block into the SQL/XML XML type for storage in the Body column.

The table created in Listing 11-18 does not ensure the XML validates against a schema, however. To do that, the schema must be registered with the database. Another benefit in addition to validation is that you can index the schema, allowing queries of the XML data to be almost as fast as queries against indexed tables. Listing 11-19 shows how you would associate a schema with this table.

Listing 11-19: Creating a table containing XML data with a schema

      DBMS_XMLSCHEMA.registerSchema(          SCHEMAURL => 'http://example.com/schemas/articles.xsd',          SCHEMADOC => bfilename('SchemaDir','article.xsd'),          CSID => nls_charset_id('AL32UTF8'));      /      CREATE INDEX iArticleAuthor ON Articles      (extractValue(Body, '/article/author'));      / 

The SchemaUrl points to the target namespace URL of the schema and may not actually point to a physical file. The SchemaDoc value is used to load the actual schema to store in the database. The CSID parameter identifies the character encoding used by the document.

SQL/XML also defines a number of operators for extracting data from the supplied XML. This is useful when you want to extract only a few values from a block of XML. The following table shows some of these operators.

Open table as spreadsheet

Operator	Notes
existsnode()	Queries the XML using an XPath statement to determine if the node exists.
extract()	Retrieves the document or document fragment described by the XPath query. If the query identifies a single node, the resulting XML is well-formed. If it identifies a number of nodes, the resulting XML is a fragment.
extractvalue()	Retrieves the text value for the node identified with the XPath query. The resulting data is converted to the appropriate SQL data type.
updatexml()	Changes the XML based on an XPath statement and the new desired value.
xmlsequence()	Converts a document fragment into a well-formed document.

For example, the following query returns the value of the author element from the article with an ID of 24:

      SELECT extractvalue(Body, '/article/author')      // returns: Foo deBar ([e-mail])