XML, the hottest Web technology going these days, is a key part of SQL Server 2000. Although most of SQL Server's new XML-integration capabilities are part of the base SQL Server 2000 product, others are add-ons that are part of the XML for SQL Server 2000 Web Release (which you can find at http://msdn.microsoft.com/downloads/default.asp). Here are my picks for the seven most important XML-integration features in SQL Server 2000.
7. SELECT...FOR XML
In SQL Server 2000, the standard T-SQL SELECT statement now includes a FOR XML clause, which returns a query's results as an XML document. The new FOR XML clause has three modes—RAW, AUTO, and EXPLICIT—each of which provides an additional level of control over the XML document's format.
The OpenXML function is essentially the opposite of the SELECT statement's FOR XML clause. Whereas FOR XML outputs query results as an XML document, OpenXML lets you represent an XML document as a relational rowset. You can use OpenXML in a T-SQL statement anywhere you can use a table or view name.
5. Direct URL Queries
After configuring the SQL Server XML Support for IIS utility, you can embed SQL queries directly into URL strings that you send to Microsoft IIS. The SQL Server XML Support for IIS utility parses the URL, sending the query to SQL Server. You can then have SQL Server return the results as an XML document and display the results in the browser.
4. XML Bulk Load
The XML Bulk Load facility, part of the XML for SQL Server 2000 Web Release, enables high-speed bulk loads of data that you've packaged inside XML tags. XML Bulk Load uses an annotated schema to specify how XML data maps to database tables.
Like direct URL queries, XML templates contain SQL statements that incoming URL requests evoke. However, templates are much more secure and flexible than direct URL queries. Templates aren't limited in size or complexity, and you can store them securely on the Web server.
2. XPath Queries
XPath is an XML-based query language that can process the data in XML documents. XPath queries treat tables and views as XML elements and treat columns as XML attributes. The SQL Server XML Support for IIS utility lets SQL Server process XPath queries and return the results as XML.
The XML for SQL Server 2000 Web Release also supports updategrams, which are
specially formatted XML documents that let you perform database insert, update, and delete operations. You use updategrams just as you use templates, and each updategram can contain multiple database insert, update, and delete tags