The myquery.xml query asks for a given customer ID's customer order information and looks like
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query CustomerID='ALFKI'>
SELECT OrderID, OrderDate, RequiredDate, ShippedDate FROM ORDERS
WHERE CustomerID=? ORDER BY OrderID XML RAW
</sql:query></ROOT>
You can also use parameters with template queries, then combine the queries with Extensible Style Language (XSL) to provide a quick substitute for boilerplate Active Server Pages (ASP) code that queries a database and formats the resulting records as an HTML table. XSL describes how to format or display XML data. Using template queries with XSL stylesheets this way resembles the use of Hypertext Markup Language extension (HTX) files with Internet Database Connector (IDC), which ASP replaced and improved on. Although SQL Server's XML features alone can't replace ASP, for simple data retrieval, the XML features are faster and easier to implement. Screen 1 shows the output for the following simple query, which combines an XML template query with XSL:
http://auril/Northwind/myquery .xml?CustomerID=vinet&xsl=ptab.xsl
Using FOR XML
Using SQL Server 2000's new FOR XML clause in a SELECT statement lets you retrieve results as an XML document instead of a row set. You can use the FOR XML clause in both queries and stored procedures. Arguments for the FOR XML clause are XML mode, SchemaOption, and ELEMENTS.
XML mode. XML mode specifies the XML mode (RAW, AUTO, or EXPLICIT), which determines the shape of the resulting XML tree (see Table 1 for a description of the modes). EXPLICIT mode is the most useful, letting you create an XML tree that (if you have all the requisite data) lets you provide XML data in your preferred format.
SchemaOption. This option specifies that the database return a schema. SchemaOption can be Document Type Definition (DTD) or XMLData. DTD returns the schema and adds the root element to the results. XMLData returns the schema but doesn't add the root element. If you specify SchemaOption, the clause prepends schema to the document. Otherwise, the database doesn't return any schema.
ELEMENTS. If you specify the ELEMENTS option, the SELECT statement returns columns as subelements. Otherwise, the statement maps the columns to XML attributes. SQL Server 2000 supports this option only in AUTO mode.
You specify XML mode in the SELECT statement's FOR clause by using the syntax
FOR | [XML mode [, SchemaOption] [, ELEMENTS]]
System Stored Procedures
SQL Server 2000 adds six system stored procedures for manipulating XML data: sp_xml_preparedocument, sp_xml_fetchdocument, sp_xml_removedocument, sp_xml_insertfromxml, sp_xml_removexml, and sp_xml_fetchintoxml.
The stored procedures for manipulating XML data are on a per-connection basis, which isn't ideal for n-tier applications, for example, that rely heavily on connection pooling to provide scalability. However, the procedures provide useful functionality, letting you store XML (preparedocument) and retrieve it using a pointer (fetchdocument). You can also delete an entire document or specified elements within the document (removedocument, removexml) and insert into an XML document from a table or into a table from an XML document (insertfromxml, fetchintoxml).
Although SQL Server 2000 provides thorough programmatic access to XML, several XML-specific engines and XML add-ons, such as Oracle8i's interMedia, provide a finer grain of control over XML for such tasks as querying a set of XML documents and finding a specific element or pattern in a document. However, other SQL Server 2000 enhancements, such as user-defined functions, might close the gap by letting you code additional functionality as you need it. But even with its current XML functionality, which lets you use XML as a transport medium for relational data, SQL Server 2000 fully meets the requirements for an XML-enabled database and excels in ease of use. For example, you can build an XML-enabled customer service Web application for the Northwind database in just a few hours.
Update Grams
Update grams are XML-based insert, delete, and update batch operations with the following general format:
<sql:sync xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:before>
<TABLENAME [sql:id="value"] col="value"
col="value"...../>
</sql:before>
<sql:after>
<TABLENAME [sql:id="value"] [sql:at-identity="value"]
col="value" col="value"...../>
</sql:after>
</sql:sync>
Prev. page
1
[2]
3
next page 
