To perform an INSERT, you use only an sql:after block. To perform a DELETE, you use only an sql:before block. And to perform an UPDATE, you use both. The database must complete all operations within an sql:sync block for a transaction to succeed.
OLE DB Provider Extensions for XML
SQL Server 2000's new OLE DB provider extensions for XML—ICommandText::SetCommandText and ICommand::Execute—let you set XML documents as command text, execute the command, and retrieve the result as a stream, which you can then use in further processing. For example, you might pass the XML document to the Document Object Model (DOM), an interface that lets programs and scripts dynamically access and update a document's content, structure, and style.
SQL Server also supports templates as command text; you just pass DBGUID_
MSSQLXML as the command's globally unique ID (GUID). The consumer must call ICommand::Execute to execute XML templates. To obtain the result set as an XML document, set the riid to Istream, which tells the provider to return the result set as a stream.
An XML-Enabling Example
The first thing most SQL Server developers will want to do with XML is to get standard relational data into XML format. Although many organizations will be developing new applications as well, the first order of business will be to quickly leverage what they already have by publishing existing data as XML. Here's a simple example that should get you on your way.
First, set up a login and user for the Northwind database to use for all XML-related querying. Then, select Programs, Microsoft SQL Server 2000, Configure SQL XML Support in IIS to reach the screen that lets you configure IIS's XML SQL Server extensions.
Screen 2 shows the configuration screen, with most of the defaults selected. However, I selected Allow URL queries so that I could perform ad hoc queries. As explained earlier, a URL query is an SQL statement embedded in the URL of an IIS machine configured to support SQL Server XML. In ad hoc queries, the URL passes the SQL statement as a single parameter. I also configured the connection settings to use a SQL Server account I had already set up. (Note that SQL Server Books Online—BOL—currently refers to a virtual root of Nwind throughout, so you might want to change the virtual root's value to correspond.)
I'm running both the Web server and SQL Server 2000 on the same, clean installation of Windows 2000 (Win2K). If you install SQL Server 2000 on a machine already running SQL Server 7.0, make sure you refer to the correct SQL Server version.
Now, let's look at a URL query example:
http://auril/
Northwind?sql=
SELECT+CustomerID,
ContactName+
FROM+Customers+
FOR+XML+RAW
where auril is the name of the machine running IIS, /Northwind refers to the virtual directory set up for XML support, and ?sql= is the single parameter that describes an SQL statement. The rest of the query is the actual SQL statement that you encode so that it's safe for passing as a URL. SQL Server receives the statement as
SELECT CustomerID, ContactName
FROM Customers FOR XML RAW
The query requests RAW XML, which returns each row as a separate element, with fields in each row listed as separate attributes of that element. Screen 3 shows an example of this query's output.
In n-tier applications that use a limited number of stored procedures with set parameters to control data access, you can modify the database schema without modifying any client code by simply modifying the relevant stored procedures. Consider the stored procedure from the Northwind database in Listing 1. To XML-enable this stored procedure, you create a new stored procedure with the additions highlighted in Listing 2. You then send SQL Server an "execute customerodersxml 'VINET'" request by entering the following URL from IE 5.0:
http://auril/northwind?sql=
execute+custordersordersxml+
VINET
You'll receive the results as XML-formatted records within your browser, as Screen 4 shows. This example uses the "execute <spname>" T-SQL convention, but the SQL Server XML extensions for IIS also support the ODBC-style CALL function.
This query demonstrates basic but useful functionality. And by adding XSL, you can quickly generate an HTML document that contains a customer orders tracking report. Just combine this XML document and an XSL document (available online at http:// www.sqlmag.com), which represent a total of about 40 lines of code.
The advantages of the XML method over the traditional ASP method is that XML offers more flexibility for simple data presentation and you can implement it quickly. To show you all the possibilities, you can expect a slew of sample applications by the release version of SQL Server 2000.
Scratching the Surface
With XML, you can define standard document types that let you share, transmit, and transform business information and facilitate automated business processes. The box "XML Links," at left, points to some Web sites that can help you research XML and its benefits.
This article barely scratches the surface of what you can do—and do quickly—with XML in SQL Server 2000. For example, I recently created a simple HTTP-based service—a set of URLs with well-defined query parameters that return XML data—in a staggering 10 minutes. I had the URL running in less time than the phone call asking whether the specification had arrived. This kind of productivity enhancement will make SQL Server 2000 a platform of choice for XML delivery.
End of Article
Prev. page
1
2
[3]
next page -->
