Chapter: XML and Web Services : Building XML-Based Applications : Using XML in the .NET Enterprise Servers

SQL Server 2000

SQL Server 2000

With the advent of XML and the increasing requirements for distributed applications in today’s marketplace, a larger demand has been placed on the developer to provide messaging and data in an XML format. Providing data in XML format, until now, has con-sisted of querying data in the form of a cursor or ADO record set and using a conversion routine to convert the data into XML format. Now with the new features in SQL Server 2000, this task can be accomplished with minimal effort, which allows the developer to concentrate on the more important task of writing the business logic.

SQL Server 2000 allows the developer to query SQL Server data and receive that data in XML format through the use of a special clause: FOR XML. This clause provides three dif-ferent options by which SQL Server can return data in XML format: AUTO, RAW, and

EXPLICIT.

Issuing the SQL SELECT command with FOR XML AUTO will return the result set in XML format, with each record having a node whose tag name is the same as the table name on which the query was performed. Each node will have attributes equal to the field names specified in the query with values equal to the values of the fields within the table. Using the FOR XML RAW clause will return XML in which each record is represented by a node whose tag name is “row” and whose attributes are the fields from the query. The last option, FOR XML EXPLICIT, uses queries written in a specific format to return the XML in a specific format. However, these options are only available from within the SQL Server Query Analyzer or by accessing SQL server through a URL.

In this chapter, we will be using Web Release 2 for SQL Server 2000.

Configuring IIS

Before you can utilize the new XML support within SQL Server 2000, you must first configure it. Simply click Start, Programs, Microsoft SQL Server XML Tools, Configure IIS Support—Web Release 2. This will launch the configuration utility necessary to pro-vide XML support within SQL Server 2000, as shown in Figure 18.1.

Once you have the IIS Virtual Directory Management for SQL Server page open, drill down in the tree on the left side of the screen until you get to the Default Web Site node or the Web site from which you wish to access SQL Server 2000. On the right side of the screen, right-click and select New, Virtual Directory. This will bring up the New Virtual Directory Properties dialog box, where you enter information regarding how SQL Server 2000 should be configured to run when accessed from a URL, as shown in Figure 18.2.

On the “General” page, you will be required to give the new virtual directory a name and provide the physical path on the hard drive associated with this virtual directory. This first page determines how you may access SQL Server via HTTP. For instance, in the case of Default Web Site, if you enter a virtual directory name of SQL2000, you could access it via HTTP as http://localhost/sql2000.

The “Security” page, shown in Figure 18.3, is where you indicate how you wish to log in to SQL Server 2000.

The “Data Source” page, shown in Figure 18.4, allows you to specify the SQL Server installation to use and the database name to access.

The “Settings” page, shown in Figure 18.5, allows you to indicate the different types of queries that can be run: URL queries, template queries, and/or XPath queries, as well as whether HTTP POSTs are allowed.

The “Virtual Names” page allows you to map various special virtual directories to your main SQL Server virtual directory. If you want to execute template queries, you’ll need to at least create a template virtual directory here. If you want to use XPath queries, you’ll need to create a schema virtual directory as well. Figure 18.6 shows you the “Virtual Names” page of the dialog box, whereas Figure 18.7 shows you the Virtual Name Configuration dialog box.

There’s one “Advanced” page on the dialog box for advanced configuration options. This page allows you to specify the SQL ISAPI DLL to use for the virtual directory as well as whether various items in memory, such as templates, schemas, and XSLT style sheets, should be cached. Figure 18.8 shows this final page.

Once you’ve configured IIS to support XML for SQL Server 2000, you can begin access-ing your new virtual directory via HTTP in the form of URL queries, template queries, and XPath queries.

URL Queries

The easiest way in which to test or become familiar with URL queries in SQL Server is to open Microsoft’s Internet Explorer and enter queries into the address space available. It is important to keep in mind that the XML string returned by SQL Server 2000 is not well-formed XML; rather, it is an XML fragment. This is because there is no single root node from which all other nodes are children. However, a parameter can be passed along with the query itself to specify the root node, which will eliminate this problem by wrap-ping the returned XML string with the node specified.

For instance, the query

SELECT * FROM Customers

as a URL query looks like this:

http://localhost/sql2000?sql=Select+*+From+Customers+Where+ ➥ CustomerID+Like+’A%25’+For+XML+Auto&Root=Results

The preceding URL query returns all the records in the Customers table that have a CustomerID that begins with the letter A, wrapped with a root node called Results, as shown in Listing 18.1.

LISTING 18.1 The Output Returned from SQL Server 2000 for All Customers with a CustomerID beginning with A

<?xml version=”1.0”?>

<Customers CustomerID=”ALFKI” CompanyName=”Alfreds

➥ Futterkiste” ContactName=”Maria Anders” ContactTitle=

➥ ”Sales Representative” Address=”Obere Str. 57” City=

➥ ”Test” PostalCode=”12209” Country=”Germany” Phone=

➥ ”030-0074321” Fax=”030-0076545”/>

<Customers CustomerID=”ANATR” CompanyName=”Ana Trujillo

➥ Emparedados y helados” ContactName=”Ana Trujillo”

➥ ContactTitle=”Owner” Address=”Avda. de la Constitución

➥ 2222” City=”México D.F.” PostalCode=”05021” Country=

➥ ”Mexico” Phone=”(5) 555-4729” Fax=”(5) 555-3745”/>

<Customers CustomerID=”ANTON” CompanyName=”Antonio Moreno

➥ Taquería” ContactName=”Antonio Moreno” ContactTitle=

➥ ”Owner” Address=”Mataderos 2312” City=”México D.F.”

➥ PostalCode=”05023” Country=”Mexico” Phone=”(5) 555-3932”/>

<Customers CustomerID=”AROUT” CompanyName=”Around the Horn”

➥ ContactName=”Thomas Hardy” ContactTitle=”Sales

➥ Representative” Address=”120 Hanover Sq.” City=”London”

➥ PostalCode=”WA1 1DP” Country=”UK” Phone=”(171) 555-7788”

➥ Fax=”(171) 555-6750”/>

</Results>

In this example, each record is represented by a node called Customers whose attributes are the fields within the Customers table. The URL query used to return these results uses the For XML Auto clause. The Auto portion indicates to SQL ISAPI that it should produce an XML string with the specified table as the main element and that it should include the fields as attributes.

The For XML Raw clause will produce a slightly different XML grammar from the one created by For XML Auto.

For instance, the following URL query returns all records in the Customers table that have a CustomerID that begins with the letter A, wrapped in a root node called Results:

http://localhost/sql2000?sql=Select+*+From+Customers+Where+ ➥ CustomerID+Like+’A%25’+For+XML+Raw&Root=Results

The main difference here is that every record is represented by a tag named “row,” as shown in Listing 18.2.

LISTING 18.2 Results Returned from SQL Server 2000 for All Customers with a CustomerID beginning with A Using the For XML Raw Clause

<?xml version=”1.0”?>

<row CustomerID=”ALFKI” CompanyName=”Alfreds Futterkiste”

➥ ContactName=”Maria Anders” ContactTitle=”Sales

➥ Representative” Address=”Obere Str. 57” City=”Test”

➥ PostalCode=”12209” Country=”Germany” Phone=”030-0074321”

➥ Fax=”030-0076545”/>

<row CustomerID=”ANATR” CompanyName=”Ana Trujillo Emparedados

➥ y helados” ContactName=”Ana Trujillo” ContactTitle=

➥ ”Owner” Address=”Avda. de la Constitución 2222”

➥ City=”México D.F.” PostalCode=”05021” Country=”Mexico”

➥ Phone=”(5) 555-4729” Fax=”(5) 555-3745”/>

<row CustomerID=”ANTON” CompanyName=”Antonio Moreno Taquería”

➥ ContactName=”Antonio Moreno” ContactTitle=”Owner”

➥ Address=”Mataderos 2312” City=”México D.F.” PostalCode=

➥ ”05023” Country=”Mexico” Phone=”(5) 555-3932”/>

<row CustomerID=”AROUT” CompanyName=”Around the Horn”

➥ ContactName=”Thomas Hardy” ContactTitle=”Sales

➥ Representative” Address=”120 Hanover Sq.” City=”London” ➥ PostalCode=”WA1 1DP” Country=”UK” Phone=”(171) 555-7788” ➥ Fax=”(171) 555-6750”/>

</Results>

Both versions of the returned XML appear very similar to the way in which ADO stores and loads record sets in XML format. However, this may not always coincide with the grammar of XML that is expected within an application. For this reason, another parame-ter, xsl, can be included with the URL to specify an XSLT style sheet to use to trans-form the native XML grammar given by SQL Server 2000 into the expected grammar. Let’s look at the following example of an XSLT style sheet called Customers1.xsl, which, in this case, resides in the root of the virtual directory SQL2000, as shown in Listing 18.3.

LISTING 18.3 Customers1.xsl Contains an XSLT Transformation for Use with Results Returned from SQL Server 2000

<?xml version=”1.0”?>

<xsl:stylesheet xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”

➥ version=”1.0”> <xsl:template match=”/”>

<xsl:for-each select=”results/customers”>

</CUSTOMER>

</xsl:for-each> </CUSTOMERS>

</xsl:template>

</xsl:stylesheet>

As an example, the URL query

http://localhost/sql2000?sql=Select+*+From+Customers+Where+

➥ +CustomerID+Like+’A%25’+For+XML+Auto&Root=Results&xsl= ➥ Customers1.xsl

produces the results shown in Listing 18.4.

LISTING 18.4 Results Returned from SQL Server 2000 Using the Customers1.xsl Style Sheet to Transform the Native Results

<?xml version=”1.0”?>

<CUSTOMERID>ALFKI</CUSTOMERID> <COMPANY>Alfreds Futterkiste</COMPANY> <CONTACT>Maria Anders</CONTACT> <ADDRESS>Obere Str. 57</ADDRESS> <CITY>Test</CITY> <PHONE>030-0074321</PHONE>

</CUSTOMER>

<CUSTOMERID>ANATR</CUSTOMERID>

<COMPANY>Ana Trujillo Emparedados y helados</COMPANY> <CONTACT>Ana Trujillo</CONTACT>

<ADDRESS>Avda. de la Constitución 2222</ADDRESS> <CITY>México D.F.</CITY>

<CUSTOMERID>ANTON</CUSTOMERID> <COMPANY>Antonio Moreno Taquería</COMPANY> <CONTACT>Antonio Moreno</CONTACT> <ADDRESS>Mataderos 2312</ADDRESS> <CITY>México D.F.</CITY>

<CUSTOMERID>AROUT</CUSTOMERID> <COMPANY>Around the Horn</COMPANY> <CONTACT>Thomas Hardy</CONTACT> <ADDRESS>120 Hanover Sq.</ADDRESS> <CITY>London</CITY>

</CUSTOMERS>

In this case, our query is executed first, and then the XSLT style sheet is applied to per-form the transformation.

Knowing that an XSLT style sheet can be applied to the result set, using another parame-ter (contenttype) will return the output in HTML format. Using the style sheet in Listing 18.5, coupled with the contenttype parameter, indicates that the resulting XML grammar should be interpreted as HTML.

LISTING 18.5 Customers2.xsl Contains an XSLT Transformation to Convert the Results into an HTML Table

<?xml version=”1.0”?>

<xsl:stylesheet xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”

➥ version=”1.0”> <xsl:template match=”/”>

<TD valign=”top”>Customer Id</TD> <TD valign=”top”>Company</TD>

<TD valign=”top”>Contact</TD> <TD valign=”top”>Address</TD> <TD valign=”top”>City</TD> <TD valign=”top”>Phone</TD>

</TR>

<xsl:for-each select=”Results/Customers”>

</TR>

</xsl:for-each>

</TABLE>

</xsl:template>

</xsl:stylesheet>

Because we’ve specified that the content type should be text/HTML, a browser will be sure to interpret the resulting XML as HTML and display the results appropriately. In our case, by specifying the URL query

http://localhost/sql2000?sql=Select+*+From+Customers+Where+

➥ +CustomerID+Like+’A%25’+For+XML+Auto&Root=Results&xsl=

➥ Customers2.xsl&ContentType=text.html

we get the result set in Listing 18.6.

LISTING 18.6 The Results from Applying the Customers2.xsl Style Sheet to the Results Returned from SQL Server 2000

<?xml version=”1.0”?>

<TD valign=”top”>Customer Id</TD> <TD valign=”top”>Company</TD>

<TD valign=”top”>Contact</TD> <TD valign=”top”>Address</TD> <TD valign=”top”>City</TD> <TD valign=”top”>Phone</TD>

</TR>

<TD valign=”top”>ALFKI</TD>

<TD valign=”top”>Alfreds Futterkiste</TD> <TD valign=”top”>Maria Anders</TD>

<TD valign=”top”>Obere Str. 57</TD> <TD valign=”top”>Test</TD>

<TD valign=”top”>ANATR</TD>

<TD valign=”top”>Ana Trujillo Emparedados y helados</TD> <TD valign=”top”>Ana Trujillo</TD>

<TD valign=”top”>Avda. de la Constitución 2222</TD> <TD valign=”top”>México D.F.</TD>

<TD valign=”top”>ANTON</TD>

<TD valign=”top”>Antonio Moreno Taquería</TD> <TD valign=”top”>Antonio Moreno</TD>

<TD valign=”top”>Mataderos 2312</TD> <TD valign=”top”>México D.F.</TD>

<TD valign=”top”>AROUT</TD>

<TD valign=”top”>Around the Horn</TD> <TD valign=”top”>Thomas Hardy</TD>

<TD valign=”top”>120 Hanover Sq.</TD> <TD valign=”top”>London</TD>

</TABLE>

It is up to the individual browser to figure out how to display the resulting HTML. Also, some browsers, such as Internet Explorer version 6.0, will automatically interpret the resulting XML as HTML and display it appropriately even without the contenttype parameter being explicitly specified.

Template Queries

Another method of retrieving XML result sets from SQL Server 2000 is to template queries. These are XML files that tell SQL Server how to run queries, what the root node will be, what XSLT style sheet to apply, and so on. These files eliminate the need to specify a SELECT statement at the URL level.

In this case, our query (Select * From Customers), written as a template query, would appear as shown in Listing 18.7.

LISTING 18.7 Customers1.xml Contains a Template Query to Return All Customers from SQL Server 2000 Whose CustomerID Begins with A

Select * From Customers Where CustomerID Like ‘A%’ For XML Auto </sql:query>

</Results>

Also, it returns the result set shown in Listing 18.8.

LISTING 18.8 The Results Returned from Navigating to the Customers1.xml Template Query

<?xml version=”1.0”?>

<Customers CustomerID=”ALFKI” CompanyName=”Alfreds

➥ Futterkiste” ContactName=”Maria Anders” ContactTitle=

➥ ”Sales Representative” Address=”Obere Str. 57” City=

➥ ”Test” PostalCode=”12209” Country=”Germany” Phone=

➥ ”030-0074321” Fax=”030-0076545”/>

<Customers CustomerID=”ANATR” CompanyName=”Ana Trujillo

➥ Emparedados y helados” ContactName=”Ana Trujillo”

➥ ContactTitle=”Owner” Address=”Avda. de la Constitución ➥ 2222” City=”México D.F.” PostalCode=”05021” Country= ➥ ”Mexico” Phone=”(5) 555-4729” Fax=”(5) 555-3745”/>

<Customers CustomerID=”ANTON” CompanyName=”Antonio Moreno ➥ Taquería” ContactName=”Antonio Moreno” ContactTitle=

➥ ”Owner” Address=”Mataderos 2312” City=”México D.F.”

➥ PostalCode=”05023” Country=”Mexico” Phone=”(5) 555-3932”/> <Customers CustomerID=”AROUT” CompanyName=”Around the Horn”

➥ ContactName=”Thomas Hardy” ContactTitle=”Sales

➥ Representative” Address=”120 Hanover Sq.” City=”London” ➥ PostalCode=”WA1 1DP” Country=”UK” Phone=”(171) 555-7788” ➥ Fax=”(171) 555-6750”/>

</Results>

To run this query, save the template as Customers1.xml and store it in the template vir-tual directory beneath the main SQL Server 2000 virtual directory. Then, simply navigate to http://localhost/sql2000/template/customers1.xml, which returns the XML in the native SQL Server format.

Now let’s replace the keyword AUTO with RAW in our template query, as shown in Listing 18.9.

LISTING 18.9 Customers2.xml Contains the Revised Query

Select * From Customers Where CustomerID Like ‘A%’ For XML Raw </sql:query>

</Results>

Here’s the result:

<?xml version=”1.0”?>

<row CustomerID=”ALFKI” CompanyName=”Alfreds Futterkiste”

➥ ContactName=”Maria Anders” ContactTitle=”Sales

➥ Representative” Address=”Obere Str. 57” City=”Test”

➥ PostalCode=”12209” Country=”Germany” Phone=”030-0074321” ➥ Fax=”030-0076545”/>

<row CustomerID=”ANATR” CompanyName=”Ana Trujillo Emparedados

➥ y helados” ContactName=”Ana Trujillo” ContactTitle= ➥ ”Owner” Address=”Avda. de la Constitución 2222”

➥ City=”México D.F.” PostalCode=”05021” Country=”Mexico” ➥ Phone=”(5) 555-4729” Fax=”(5) 555-3745”/>

<row CustomerID=”ANTON” CompanyName=”Antonio Moreno Taquería”

➥ ContactName=”Antonio Moreno” ContactTitle=”Owner”

➥ Address=”Mataderos 2312” City=”México D.F.” PostalCode= ➥ ”05023” Country=”Mexico” Phone=”(5) 555-3932”/>

<row CustomerID=”AROUT” CompanyName=”Around the Horn”

➥ ContactName=”Thomas Hardy” ContactTitle=”Sales

➥ Representative” Address=”120 Hanover Sq.” City=”London” ➥ PostalCode=”WA1 1DP” Country=”UK” Phone=”(171) 555-7788” ➥ Fax=”(171) 555-6750”/>

</Results>

To apply a style sheet to the result set, the template query would appear as shown in Listing 18.10.

LISTING 18.10 Customers3.xml Contains the Query to Execute and the Location of Style Sheet to Use to Transform the Results

<Results xmlns:sql=”urn:schemas-microsoft-com:xml-sql”

➥ sql:xsl=’../Customers1.xsl’> <sql:query>

Select * From Customers Where CustomerID Like ‘A%’ For XML Auto

</sql:query>

</Results>

This returns the results shown in Listing 18.11.

LISTING 18.11 The Results from Navigating to the Customers3.xml Template QUERY

<?xml version=”1.0”?>

<CUSTOMERID>ALFKI</CUSTOMERID> <COMPANY>Alfreds Futterkiste</COMPANY> <CONTACT>Maria Anders</CONTACT> <ADDRESS>Obere Str. 57</ADDRESS> <CITY>Test</CITY> <PHONE>030-0074321</PHONE>

</CUSTOMER>

<CUSTOMERID>ANATR</CUSTOMERID>

<COMPANY>Ana Trujillo Emparedados y helados</COMPANY> <CONTACT>Ana Trujillo</CONTACT>

<ADDRESS>Avda. de la Constitución 2222</ADDRESS> <CITY>México D.F.</CITY>

</CUSTOMER>

<CUSTOMERID>ANTON</CUSTOMERID> <COMPANY>Antonio Moreno Taquería</COMPANY> <CONTACT>Antonio Moreno</CONTACT> <ADDRESS>Mataderos 2312</ADDRESS> <CITY>México D.F.</CITY>

<CUSTOMERID>AROUT</CUSTOMERID> <COMPANY>Around the Horn</COMPANY> <CONTACT>Thomas Hardy</CONTACT> <ADDRESS>120 Hanover Sq.</ADDRESS> <CITY>London</CITY>

</CUSTOMERS>

As is the case with URL queries, you can format the final results to be displayed in HTML. In this case, simply change Customers1.xsl to Customers2.xsl. The result is the HTML table created earlier, as shown in Listing 18.12.

LISTING 18.12 The Results from Using the Customers2.xsl Style Sheet Instead in the

Customers3.xml Template Query

<?xml version=”1.0”?>

<TD valign=”top”>Customer Id</TD>

<TD valign=”top”>Company</TD>

<TD valign=”top”>Contact</TD>

<TD valign=”top”>Address</TD>

<TD valign=”top”>Phone</TD>

</TR>

<TD valign=”top”>ALFKI</TD>

<TD valign=”top”>Alfreds Futterkiste</TD>

<TD valign=”top”>Maria Anders</TD>

<TD valign=”top”>Obere Str. 57</TD>

</TR>

<TD valign=”top”>ANATR</TD>

<TD valign=”top”>Ana Trujillo Emparedados y helados</TD> <TD valign=”top”>Ana Trujillo</TD>

<TD valign=”top”>Avda. de la Constitución 2222</TD> <TD valign=”top”>México D.F.</TD>

<TD valign=”top”>ANTON</TD>

<TD valign=”top”>Antonio Moreno Taquería</TD> <TD valign=”top”>Antonio Moreno</TD>

<TD valign=”top”>Mataderos 2312</TD> <TD valign=”top”>México D.F.</TD>

<TD valign=”top”>AROUT</TD>

<TD valign=”top”>Around the Horn</TD> <TD valign=”top”>Thomas Hardy</TD> <TD valign=”top”>120 Hanover Sq.</TD> <TD valign=”top”>London</TD>

</TABLE>

Template queries can also accept parameters to help filter the result set. Specifying that we only want the customers whose CustomerID begins with the letter A is kind of short sighted. It would make a lot more sense to allow the value of the CustomerID to vary.

Therefore, the template file could be rewritten as shown in Listing 18.13.

LISTING 18.13 Customers4.xml Contains a Parameterized Template QUERY

<sql:param name=’CustomerId’>%</sql:param> </sql:header>

<sql:query>

Select * From Customers Where CustomerID Like @CustomerId For XML Auto </sql:query>

</Results>

In this example, we specify that the template query will accept one parameter, CustomerId, and that this parameter has a default value of %, which, in conjunction with the query, will return all records from the Customers table. To execute this query, we just provide the following URL:

http://localhost/sql2000/template/customers.xml?CustomerId=A%25

Now we can return any number of records from the Customers table. For instance, if we supply the URL

http://localhost/sql2000/template/customers.xml?CustomerId=B%25

we’d get the result set shown in Listing 18.14.

LISTING 18.14 The Results from Navigating to the Customers4.xml Template Query

<Customers CustomerID=”BERGS” CompanyName=”Berglunds snabbköp”

➥ ContactName=”Christina Berglund” ContactTitle=

➥ ”Order Administrator” Address=”Berguvsvägen 8” City= ➥ ”Luleå” PostalCode=”S-958 22” Country=”Sweden” Phone= ➥ ”0921-12 34 65” Fax=”0921-12 34 67”/>

<Customers CustomerID=”BLAUS” CompanyName=”Blauer See

➥ Delikatessen” ContactName=”Hanna Moos” ContactTitle= ➥ ”Sales Representative” Address=”Forsterstr. 57” City= ➥ ”Mannheim” PostalCode=”68306” Country=”Germany” Phone= ➥ ”0621-08460” Fax=”0621-08924”/>

<Customers CustomerID=”BLONP” CompanyName=”Blondesddsl père

➥ et fils” ContactName=”Frédérique Citeaux” ContactTitle= ➥ ”Marketing Manager” Address=”24, place Kléber” City=

➥ ”Strasbourg” PostalCode=”67000” Country=”France” Phone= ➥ ”88.60.15.31” Fax=”88.60.15.32”/>

<Customers CustomerID=”BOLID” CompanyName=”Bólido Comidas

➥ preparadas” ContactName=”Martín Sommer” ContactTitle= ➥ ”Owner” Address=”C/ Araquil, 67” City=”Madrid”

➥ PostalCode=”28023” Country=”Spain” Phone= ➥ ”(91) 555 22 82” Fax=”(91) 555 91 99”/>

<Customers CustomerID=”BONAP” CompanyName=”Bon app’” ContactName=

➥ ”Laurence Lebihan” ContactTitle=”Owner” Address=

➥ ”12, rue des Bouchers” City=”Marseille” PostalCode= ➥ ”13008” Country=”France” Phone=”91.24.45.40” Fax= ➥ ”91.24.45.41”/>

<Customers CustomerID=”BOTTM” CompanyName=”Bottom-Dollar

➥ Markets” ContactName=”Elizabeth Lincoln” ContactTitle= ➥ ”Accounting Manager” Address=”23 Tsawassen Blvd.” City= ➥ ”Tsawassen” Region=”BC” PostalCode=”T2F 8M4” Country= ➥ ”Canada” Phone=”(604) 555-4729” Fax=”(604) 555-3745”/>

<Customers CustomerID=”BSBEV” CompanyName=”B’s Beverages” ➥ ContactName=”Victoria Ashworth” ContactTitle=”Sales

➥ Representative” Address=”Fauntleroy Circus” City= ➥ ”London” PostalCode=”EC2 5NT” Country=”UK” Phone= ➥ ”(171) 555-1212”/>

</Results>

XPath Queries

Previously, when SQL Server 2000 initially shipped at the beginning of 2001, support had not been included for annotated XSD schemas. Therefore, everyone was forced to create annotated XDR schemas. However, with Web Release 2 for SQL Server 2000, support for creating annotated XSD schemas has been added. Although annotated XDR schemas are still supported for backward compatibility, the XDR schemas will most likely be phased out in favor of the XSD schemas.

Two basic attributes and one basic element are needed to author annotated XSD schemas. The attributes needed are sql:field and sql:relation. The element is sql:relation-ship. The sql:relation attribute is used to map an element to a table. This has the effect of creating one XML element for every record in the table. The sql:field attribute is used to map a particular attribute or node value to a field from the related table. The sql:relationship element is used to relate elements within the XML docu-ment to other elements. It defines the two tables and the join condition necessary to relate them together.

Using those attributes and elements, an annotated XSD schema can be authored to return data from SQL Server 2000 in a specific format. The only required attribute is sql:relation. This attribute refers to a table or view in the database and can be placed on an element in the XSD schema. The schema shown in Listing 18.15 is a simple exam-ple of using an sql:relation attribute in an annotated XSD schema for the Customers table in the Northwind database.

LISTING 18.15 Customers1.xsd Contains an Annotated XSD Schema for Customers Within the Northwind Database

<?xml version=”1.0” encoding=”UTF-8”?>

<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”

➥ xmlns:sql=”urn:schemas-microsoft-com:mapping-schema”> <xsd:element name=”CUSTOMER” sql:relation=”Customers”>

<xsd:complexType>

<xsd:attribute name=”CustomerID” type=”xsd:string”/> <xsd:attribute name=”CompanyName” type=”xsd:string”/> <xsd:attribute name=”ContactName” type=”xsd:string”/> <xsd:attribute name=”Address” type=”xsd:string”/> <xsd:attribute name=”City” type=”xsd:string”/> <xsd:attribute name=”Phone” type=”xsd:string”/>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Because the sql:relation attribute is specified on an xsd:element element within our schema, the relation is inherited by all elements and attributes contained within the ele-ment declaration. This means we do not need to specify the sql:relation attribute on every <element> or <attribute> element within our schema. Because we defined our attribute names exactly how they exist in the Northwind database, we don’t need to use the sql:field attribute. Keep in mind that XML is case sensitive. Therefore, the attribute names defined earlier must match exactly with the field names defined in the database for this XSD schema to work. This schema, when used for an XPath query for all customers within the City of London, returns an XML document whose structure matches that of the one defined in the schema, as shown here:

<?xml version=”1.0”?>

<CUSTOMER City=”London” CompanyName=”Around the Horn”

➥ CustomerID=”AROUT” Address=”120 Hanover Sq.”

➥ ContactName=”Thomas Hardy” Phone=”(171) 555-7788”/> <CUSTOMER City=”London” CompanyName=”B’s Beverages”

➥ CustomerID=”BSBEV” Address=”Fauntleroy Circus”

➥ ContactName=”Victoria Ashworth” Phone=”(171) 555-1212”/> <CUSTOMER City=”London” CompanyName=”Consolidated Holdings”

➥ CustomerID=”CONSH” Address=”Berkeley Gardens 12 Brewery” ➥ ContactName=”Elizabeth Brown” Phone=”(171) 555-2282”/>

<CUSTOMER City=”London” CompanyName=”North/South” CustomerID=

➥ ”NORTS” Address=”South House 300 Queensbridge”

➥ ContactName=”Simon Crowther” Phone=”(171) 555-7733”/> <CUSTOMER City=”London” CompanyName=”Seven Seas Imports”

➥ CustomerID=”SEVES” Address=”90 Wadhurst Rd.”

➥ ContactName=”Hari Kumar” Phone=”(171) 555-1717”/> </Results>

The sql:field attribute may be used in conjunction with the sql:relation attribute to create elements or attributes that do not exactly match their definitions in the database, as demonstrated by the annotated XSD schema shown in Listing 18.16.

LISTING 18.16 Customers2.xsd Demonstrates How Fields from the Database Are Mapped to Attributes Within the Output XML Document

<?xml version=”1.0” encoding=”UTF-8”?>

<xsd:schema xmlns:sql=”urn:schemas-microsoft-com:mapping-schema”

➥ xmlns:xsd=”http://www.w3.org/2001/XMLSchema”> <xsd:element name=”CUSTOMER” sql:relation=”Customers”>

<xsd:complexType>

<xsd:attribute name=”Id” type=”xsd:string” sql:field=”CustomerID”/>

<xsd:attribute name=”Company” type=”xsd:string” sql:field=”CompanyName”/> <xsd:attribute name=”Contact” type=”xsd:string” sql:field=”ContactName”/> <xsd:attribute name=”Address” type=”xsd:string”/>

<xsd:attribute name=”City” type=”xsd:string”/> <xsd:attribute name=”Phone” type=”xsd:string”/>

</xsd:complexType>

</xsd:element>

</xsd:schema>

When the schema in Listing 18.16 is used in the same XPath query, it will return the following XML result set:

<?xml version=”1.0”?>

<CUSTOMER City=”London” Id=”AROUT” Company=”Around the Horn”

➥ Address=”120 Hanover Sq.” Phone=”(171) 555-7788” ➥ Contact=”Thomas Hardy”/>

<CUSTOMER City=”London” Id=”BSBEV” Company=”B’s Beverages”

➥ Address=”Fauntleroy Circus” Phone=”(171) 555-1212” ➥ Contact=”Victoria Ashworth”/>

<CUSTOMER City=”London” Id=”CONSH” Company=”Consolidated

➥ Holdings” Address=”Berkeley Gardens 12 Brewery” ➥ Phone=”(171) 555-2282” Contact=”Elizabeth Brown”/>

<CUSTOMER City=”London” Id=”EASTC” Company=”Eastern Connection”

➥ Address=”35 King George” Phone=”(171) 555-0297” ➥ Contact=”Ann Devon”/>

<CUSTOMER City=”London” Id=”NORTS” Company=”North/South”

➥ Address=”South House 300 Queensbridge” Phone= ➥ ”(171) 555-7733” Contact=”Simon Crowther”/>

<CUSTOMER City=”London” Id=”SEVES” Company=”Seven Seas Imports”

➥ Address=”90 Wadhurst Rd.” Phone=”(171) 555-1717” ➥ Contact=”Hari Kumar”/>

</Results>

Now let’s say we don’t like having the fields mapped to attributes. Instead, we could use the schema in Listing 18.17 to produce an XML document with elements for the fields in the Customers table.

LISTING 18.17 Customers3.xsd Demonstrates How Fields from the Database Can Be Mapped to Elements Within the Output XML Document

<?xml version=”1.0” encoding=”UTF-8”?>

<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”

➥ xmlns:sql=”urn:schemas-microsoft-com:mapping-schema”> <xsd:element name=”CUSTOMER” sql:relation=”Customers”>

<xsd:complexType>

<xsd:all>

<xsd:element name=”CUSTOMERID” type=”xsd:string” ➥ sql:field=”CustomerID”/>

<xsd:element name=”COMPANY” type=”xsd:string” ➥ sql:field=”CompanyName”/>

<xsd:element name=”CONTACT” type=”xsd:string” ➥ sql:field=”ContactName”/>

<xsd:element name=”ADDRESS” type=”xsd:string” ➥ sql:field=”Address”/>

<xsd:element name=”CITY” type=”xsd:string” ➥ sql:field=”City”/>

<xsd:element name=”PHONE” type=”xsd:string” ➥ sql:field=”Phone”/>

</xsd:all>

</xsd:complexType>

</xsd:element>

</xsd:schema>

If we use the preceding annotated XSD schema in the same XPath query as before, the XML shown in Listing 18.18 is returned by SQL Server 2000.

LISTING 18.18 The Resulting XML Document from Executing an XPath Query Against the Customers3.xsd Schema

<?xml version=”1.0”?>

<CUSTOMERID>AROUT</CUSTOMERID> <COMPANY>Around the Horn</COMPANY> <CONTACT>Thomas Hardy</CONTACT> <ADDRESS>120 Hanover Sq.</ADDRESS> <CITY>London</CITY>

<CUSTOMERID>BSBEV</CUSTOMERID> <COMPANY>B’s Beverages</COMPANY> <CONTACT>Victoria Ashworth</CONTACT> <ADDRESS>Fauntleroy Circus</ADDRESS> <CITY>London</CITY>

<CUSTOMERID>CONSH</CUSTOMERID> <COMPANY>Consolidated Holdings</COMPANY> <CONTACT>Elizabeth Brown</CONTACT>

<ADDRESS>Berkeley Gardens 12 Brewery</ADDRESS> <CITY>London</CITY>

<CUSTOMERID>EASTC</CUSTOMERID> <COMPANY>Eastern Connection</COMPANY> <CONTACT>Ann Devon</CONTACT> <ADDRESS>35 King George</ADDRESS> <CITY>London</CITY>

<CUSTOMERID>NORTS</CUSTOMERID>

<COMPANY>North/South</COMPANY> <CONTACT>Simon Crowther</CONTACT>

<ADDRESS>South House 300 Queensbridge</ADDRESS> <CITY>London</CITY>

<CUSTOMERID>SEVES</CUSTOMERID> <COMPANY>Seven Seas Imports</COMPANY> <CONTACT>Hari Kumar</CONTACT> <ADDRESS>90 Wadhurst Rd.</ADDRESS> <CITY>London</CITY>

</Results>

In addition to using the sql:relation and sql:field attributes, we can use the sql:relationship element, which must be used within the <xsd:appinfo> element, to produce nested XML documents in which elements may contain related child elements. Four attributes of the sql:relationship element must be specified: parent, parent-key, child, and child-key. The parent attribute specifies the parent table, and the par-ent-key attribute specifies the key on the parent table to use to relate it to a child table. The child attribute specifies the child table, and the child-key attribute specifies the key on the child table used to relate it to the parent table. When these four attributes are used in conjunction with the sql:relationship element, SQL Server 2000 is able to relate and nest XML elements using the values specified in the attributes of the sql:relationship element, as shown in the annotated XSD schema in Listing 18.19.

LISTING 18.19 Customers4.xsd Demonstrates How to Create a Relationship Between Two Tables

<?xml version=”1.0” encoding=”UTF-8”?>

<xsd:schema xmlns:sql=”urn:schemas-microsoft-com:mapping-schema”

➥ xmlns:xsd=”http://www.w3.org/2001/XMLSchema”> <xsd:element name=”CUSTOMER” sql:relation=”Customers”>

<xsd:complexType>

<xsd:sequence>

<xsd:element name=”CUSTOMERID” type=”xsd:string” ➥ sql:field=”CustomerID”/>

<xsd:element name=”COMPANY” type=”xsd:string” ➥ sql:field=”CompanyName”/>

<xsd:element name=”CONTACT” type=”xsd:string” ➥ sql:field=”ContactName”/>

<xsd:element name=”ADDRESS” type=”xsd:string” ➥ sql:field=”Address”/>

<xsd:element name=”CITY” type=”xsd:string” ➥ sql:field=”City”/>

<xsd:element name=”PHONE” type=”xsd:string” ➥ sql:field=”Phone”/>

<xsd:element name=”ORDER” maxOccurs=”unbounded” ➥ sql:relation=”Orders”>

<xsd:annotation>

<xsd:appinfo>

<sql:relationship parent=”Customers” parent-key= ➥ ”CustomerID” child=”Orders” child-key=”CustomerID”/>

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name=”ORDERID” type=”xsd:integer” ➥ sql:field=”OrderID”/>

<xsd:element name=”ORDERDATE” type=”xsd:date” ➥ sql:field=”OrderDate”/>

<xsd:element name=”DETAILS” maxOccurs=”unbounded” ➥ sql:relation=”[Order Details]”>

<xsd:annotation>

<xsd:appinfo>

<sql:relationship parent=”Orders” parent-key= ➥ ”OrderID” child=”[Order Details]” child-key=”OrderID”/>

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name=”ORDERID” type= ➥ ”xsd:integer” sql:field=”OrderID”/>

<xsd:element name=”PRODUCTID” type= ➥ ”xsd:integer” sql:field=”ProductID”/>

<xsd:element name=”UNITPRICE” sql:field=

➥ ”UnitPrice”>

<xsd:simpleType>

<xsd:restriction base=”xsd:decimal”> <xsd:fractionDigits value=”2”/>

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name=”QUANTITY” type= ➥ ”xsd:positiveInteger” sql:field=”Quantity”/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Performing an XPath query for the customer with the CustomerID ALFKI against the preceding annotated XSD schema will produce the following result shown in Listing 18.20.

LISTING 18.20 The Results from an XPath Query Against Customers4.xsd

<?xml version=”1.0”?>

<CUSTOMERID>ALFKI</CUSTOMERID> <COMPANY>Alfreds Futterkiste</COMPANY> <CONTACT>Maria Anders</CONTACT> <ADDRESS>Obere Str. 57</ADDRESS> <CITY>Test</CITY> <PHONE>030-0074321</PHONE>

<ORDER>

</DETAILS>

</DETAILS>

</DETAILS>

</ORDER>

<ORDER>

</DETAILS>

</ORDER>

<ORDER>

</DETAILS>

</DETAILS>

</ORDER>

<ORDER>

</DETAILS>

</DETAILS>

</ORDER>

<ORDER>

</DETAILS>

</DETAILS>

</ORDER>

<ORDER>

</DETAILS>

</DETAILS>

</ORDER>

</CUSTOMER>

</Results>

But wait, why is there so much interest in creating annotated XSD schemas? Unfortunately, these files have to exist to perform an XPath query. Basically, to perform an XPath query, the syntax is as follows:

http://servername/sqlvirtualdirectory/schemavirtualdirectory/ ➥ schemafilename/Xpathexpression?Root=RootNodeName

Because we’ve already covered the syntax for XPath expressions in Chapter 4, “Creating XML Schemas,” we will not go over it again here. The main difference you need to keep in mind is the exclusion of the root element within the expression because it is not defined within the schema. For instance, the following XPath query would result in an error:

http://localhost/sql2000/schema/customers.xsd/Results/CUSTOMER? ➥ Root=Results

Instead, you need to write this XPath query as follows:

http://localhost/sql2000/schema/customers.xsd/CUSTOMER?Root=Results

XML Updategrams

Rather than having to code multiple stored procedures to handle the inserting, updating, and deleting of records within a database, you can use XML updategrams instead. These XML documents are posted to SQL Server’s virtual directory and are intercepted by the SQL ISAPI extension. By using various elements within an XML updategram, SQL Server can modify the data in the database accordingly.

The basic premise behind an XML updategram is that it behaves the same as a template and uses a snapshot of information, both before and after changes are made to the XML fragment, to determine how SQL Server must proceed. By using a combination of <sync> elements, which mark the beginning and end of a transaction, and <before> and <after> elements, SQL Server can determine whether a new record is being created or an existing record is being modified or deleted.

The <before> element identifies what the database’s existing state is. The <after> element tells what the database’s new state will be. The <sync> element indicates what a transaction encompasses. It contains one or more <before> and <after> elements, and all the pairs within a <sync> element will be executed as one transaction. Therefore, either everything within a <sync> element will be performed, or nothing will be performed.

Here’s the basic structure of an XML updategram:

<updg:before>

...

</updg:before>

<updg:after>

...

</updg:after>

</updg:sync>

</ROOT>

Note the inclusion of the updategram namespace: urn:schemas-microsoft-com:xml-updategram. This namespace is required for XML updategrams. However, the actual namespace you decide to use within your updategrams is completely up to you. For instance, the following is also a perfectly valid XML updategram structure:

<eps:before>

...

</eps:before>

<eps:after>

...

</eps:after>

</eps:sync>

</ROOT>

An XML updategram determines what function to perform based on the contents of the <before> and <after> elements, which is outlined as follows:

If a record’s XML definition appears only in the <before> element with no corre-sponding definition in the <after> element, the XML updategram performs a delete operation.

If a record’s XML appears only in the <after> element with no corresponding XML in the <before> element, an insert operation is performed.

If the XML for a record appears in the <before> element and has a corresponding definition in the <after> element, an update operation is performed. In this case, the updategram updates the record instance to the values specified in the <after> element.

Mapping the XML to the Database

An XML updategram can map the XML back into the database either implicitly or explicitly. In other words, specifying the XDR schema in the updategram is optional as long as you follow a few simple rules. First and foremost, remember that you must use FOR XML AUTO or FOR XML AUTO,ELEMENTS in order to use the default mapping that SQL ISAPI provides.

In simple insert, update, and delete scenarios, implicit mapping may be enough to per-form the necessary operation on the given XML fragment. SQL ISAPI will attempt to map the elements and attributes back to the database in a fashion that’s similar to how it maps the database to an XML fragment using FOR XML AUTO. The key for this approach to work, however, is that each element, which represents a table, must be named the same as the table name. Remember, XML is case sensitive. Also, each element or attribute that represents a field must be named likewise. For instance, the following XML updategram will insert a new customer into the Customers table of the Northwind data-base:

<updg:before>

</updg:before>

<updg:after>

</updg:sync>

</ROOT>

By examining the preceding XML updategram, you can see how SQL Server identifies how it must behave. Given this XML updategram, SQL ISAPI sees that there is nothing in the <before> element that corresponds to anything in the <after> element, so it deter-mines that an insert operation must be performed. SQL ISAPI also examines the contents of the <after> element and determines from the existence of the <Customers> element that a new record will be inserted into the Customers table. Furthermore, SQL ISAPI realizes that two fields will have values specified for them—CustomerID and CompanyName—based on the occurrence of attributes with those names.

For complex mappings, you can explicitly identify an annotated XSD schema to use in order to map the elements and attributes back to the appropriate tables and fields in the database. Each transaction unit, identified by a <sync> element, can have its own map-ping schema to use to map elements and attributes back into the database.

Implicit Mapping in Updategrams

An element-centric updategram is comprised of elements that contain subelements. The elements map to a table in the database, and the subelements map to fields within that table. Therefore, to use element-centric mapping, we need an XML document that looks something like this:

<CustomerID>TEST</CustomerID> <CompanyName>Test insert comp</CompanyName>

</Customers>

In this case, to perform an insert operation, we would construct the following updategram:

<updg:after>

<CustomerID>TEST</CustomerID> <CompanyName>Test insert comp</CompanyName>

</Customers>

</updg:after>

</updg:sync>

</ROOT>

For attribute-centric mappings, the table element contains attributes that map to fields within the table. Therefore, the previous example could be represented in an attribute-centric approach by using an XML document, like this:

An XML updategram like this one would also be used:

<updg:after>

</updg:sync>

</ROOT>

Interestingly enough, a combination of element-centric and attribute-centric mappings can be used. As long as the elements and attributes map back into the database using the default mapping provided by FOR XML AUTO, the two approaches can be mixed. For instance, imagine the following XML document:

<Customers CustomerID=”Test”> <CompanyName>Test insert comp</CompanyName>

</Customers>

This record could be inserted into SQL Server by using an XML updategram like the following:

<updg:after>

<Customers CustomerID=”TEST”> <CompanyName>Test insert comp</CompanyName>

</Customers>

</updg:after>

</updg:sync>

</ROOT>

Explicit Mapping in Updategrams

In the case of complex mappings, an annotated XSD schema can be specified for each transaction the XML updategram performs by specifying the name of the corresponding XDR schema in the mapping-schema attribute of the <sync> element. By doing this, explicit mapping is chosen, and every element and attribute of the XML document must map to the elements and attributes within the specified XSD schema. For example, in a simple case, an annotated XSD schema could contain the following:

<?xml version=”1.0” encoding=”UTF-8”?>

<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema” ➥ xmlns:sql=”urn:schemas-microsoft-com:mapping-schema”>

<xsd:element name=”CUSTOMER” sql:relation=”Customers”> <xsd:complexType>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Using this schema, an XML document like the following could be placed into an XML updategram:

<CUSTOMER CustomerID=”TEST” CompanyName=”Test insert comp”

➥ ContactName=”Test contact” Address=”Some address” ➥ City=”Unknown” Phone=”9999999”/>

To insert this XML document into the Customers table of the Northwind database, we need to post an updategram like the following to the SQL Server virtual directory:

<updg:after>

<CUSTOMER CustomerID=”TEST” CompanyName=

➥ ”Test insert comp” ContactName=”Test contact” Address= ➥ ”Some address” City=”Unknown” Phone=”9999999”/>

</updg:after>

</updg:sync>

</ROOT>

However, the XSD schemas and the corresponding XML documents that will be inserted can get quite complex. For instance, the XSD schema shown in Listing 18.19, maps fields and tables from the Northwind database into a hierarchical XML document.

Based on the schema in Listing 18.19, an XML document like the following could be placed into an XML updategram:

<CUSTOMERID>TEST</CUSTOMERID> <COMPANY>Test insert comp</COMPANY> <CONTACT>Test contact</CONTACT> <ADDRESS>Test address</ADDRESS> <CITY>Test city</CITY> <PHONE>9999999</PHONE>

</DETAILS>

</ORDER>

</CUSTOMER>

Given this XML document, the information could be inserted by creating the XML updategram shown in Listing 18.21.

LISTING 18.21 A Sample XML Updategram Using a Mapping Schema

<updg:sync mapping-schema=”../Schema/Customers4.xsd”>

<updg:after>

<CUSTOMERID>TEST</CUSTOMERID> <COMPANY>Test insert comp</COMPANY> <CONTACT>Test contact</CONTACT> <ADDRESS>Test address</ADDRESS> <CITY>Test city</CITY> <PHONE>9999999</PHONE>

<ORDERID>OrderId</ORDERID>

</DETAILS>

</ORDER>

</CUSTOMER>

</updg:after>

</updg:sync>

</ROOT>

This example uses a new attribute for the updategram: at-identity. This attribute cap-tures the identity value for the identity column of the referenced table and stores it in the specified variable for later use in the updategram, as shown in the <ORDERID> element contained in the <DETAILS> element.

NULL Handling in Updategrams

NULL fields in SQL Server are not returned in the XML document. However, it is some-times useful to set a field to NULL. Using updategrams, this can be accomplished by using the nullvalue attribute on a <sync> element. When the nullvalue attribute is specified in an <sync> element, SQL ISAPI can determine that when it encounters the specified string, it should insert NULL into the field. For instance, to insert a NULL value into the CompanyName field of the Customers table in the Northwind database, we could use the following XML updategram:

<updg:sync updg:nullvalue=”.NULL.” >

<updg:before>

<updg:after>

</updg:sync>

</ROOT>

When SQL ISAPI encounters the .NULL. value in the CompanyName attribute, it inserts NULL into the CompanyName field.

Executing Updategrams

Knowing how XML updategrams function is one thing, but how do we get SQL ISAPI to perform the necessary operations? Quite simply, we can post the updategram to the SQL Server virtual directory. This can be accomplished using an HTML page or the XMLHTTP COM object available in MSXML2. For instance, we could use the following code in Visual Basic to post an XML updategram to a SQL Server virtual directory called local-host/sql2000:

Dim loPost As New MSXML2.xmlHttp

Dim loXML As New MSXML2.DOMDocument

loXML.Async=.F. loXML.loadXML MyUpdateGram

‘ Post the template.

loPost.Open “POST”, “http://localhost/sql2000”, False loPost.setRequestHeader “Content-type”, “text/xml” loPost.send loXML

We can then query the ResponseText property of the XMLHTTP object to find out whether an error occurred and what the result was.

Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail

XML and Web Services : Building XML-Based Applications : Using XML in the .NET Enterprise Servers : SQL Server 2000 |