- XML and Traditional Data Access
- XML and ADO.NET
- Serialization, Marshaling, and DataSet
- Typed DataSets
- The XmlDataDocument Class
- Why Databases and XML?
- SQL Server, XML, and Managed Data Access
- Using SQLXML and .NET
- Where Are We?
This chapter is from the book
This chapter is from the book
This chapter is from the book
7.7 SQL Server, XML, and Managed Data Access
SQL Server 2000 and the ensuing Web-released extensions, called SQLXML, have many kinds of support for XML. This topic could take an entire book by itself. Almost all the support is available through ADO.NET. First, let's enumerate them and then go over how each one is supported in ADO.NET.
7.7.1 The FOR XML Keyword
SQL Server added a FOR XML keyword to the SQL SELECT statement. This keyword can produce XML in four formats: RAW, AUTO, NESTED, and EXPLICIT. The AUTO, NESTED, and EXPLICIT formats can produce hierarchical nested XML output and attribute-normal or element-normal form. Listing 7–37 shows examples of using SELECT ... FOR XML and the results obtained.
Listing 7–37 Using SQL Server's FOR XML syntax
-- 1. raw mode:
-- this query:
SELECT Customers.CustomerID, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML RAW
-- produces this XML output document fragment
<row CustomerID="ALFKI" OrderID="10643" />
<row CustomerID="ALFKI" OrderID="10692" />
<row CustomerID="ALFKI" OrderID="10703" />
<row CustomerID="ALFKI" OrderID="10835" />
<row CustomerID="ANATR" OrderID="10308" />
-- 2. auto mode
-- this query:
SELECT Customers.CustomerID, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML AUTO
-- produces the following XML document fragment
<Customers CustomerID="ALFKI">
<Orders OrderID="10643" />
<Orders OrderID="10692" />
<Orders OrderID="10702" />
<Orders OrderID="10835" />
</Customers>
<Customers CustomerID="ANATR">
<Orders OrderID="10308" />
</Customers>
-- 3. explicit mode
-- this query:
SELECT 1 as Tag, NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers
UNION ALL
SELECT 2, 1,
Customers.CustomerID,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID]
FOR XML EXPLICIT
-- produces this output document fragment
<Customer CustomerID="ALFKI">
<Order OrderID="10643"/>
<Order OrderID="10692"/>
<Order OrderID="10702"/>
</Customer>
7.7.2 OpenXML
SQL Server 2000 can decompose XML passed in to a stored procedure using a user-defined function, OpenXML. This technique uses the normal stored procedure mechanism, so I don't discuss it further.
7.7.3 The SQLOLEDB Provider
The SQLOLEDB provider (that is, the native OLE DB provider for SQL Server) accepts two new query dialects: XPath and MSSQLXML. MSSQLXML consists of XPath or SQL queries surrounded by XML wrapper elements. Because SQL Server does not support XPath directly, XPath support requires an XML mapping schema that maps an XML view of a single SQL Server database. Multiple tables and relationships are supported in mapping schemas. The SQLOLEDB provider also supports streamed input and output. An XSLT transform can automatically be run on the output stream by means of a property on the XML query.
7.7.4 The SqlXml Managed Classes
The SqlXml set of managed classes, which provide some functionality similar to an ADO.NET data provider, encapsulate all the XML support in the OLE DB provider, listed earlier. We'll talk a lot more about this one.
7.7.5 The SQLXML Web Application
An ISAPI application exposes the ability to obtain an XML result through the HTTP protocol. The URL endpoint exposed can accommodate MSSQLXML templates in files, direct queries, and HTTP POST requests. This functionality works by calling the OLE DB provider from within the ISAPI application.
7.7.6 Updategrams
An update to the OLE DB provider accepts an XML dialect called updategrams. This functionality works either directly through the provider or through the ISAPI application. Several dialects of updategram are supported. Listing 7–38 shows a sample updategram document. Updategrams are similar in concept to ADO.NET DiffGrams.
Listing 7–38 Updategram formats
<DocumentElement
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:ssync>
<!-- Deleted -->
<sql:before>
<Teachers sql:id="1">
<ID>0</ID>
<Name>Mr Apple</Name>
</Teachers>
</sql:before>
<sql:after></sql:after>
<!-- Unchanged -->
<sql:before>
<Teachers sql:id="2">
<ID>1</ID>
<Name>Mrs Blue</Name>
</Teachers>
</sql:before>
<sql:after>
<Teachers sql:id="2"></Teachers>
</sql:after>
<!-- New -->
<sql:before></sql:before>
<sql:after>
<Courses sql:id="7">
<ID>6</ID>
<Name>Home Ec 200</Name>
</Courses>
</sql:after>
<!-- Modified -->
<sql:before>
<Students sql:id="1">
<ID>0</ID>
<Name>Abe</Name>
</Students>
</sql:before>
<sql:after>
<Students sql:id="1">
<ID>0</ID>
<Name>Abby</Name>
</Students>
</sql:after>
<!-- Removed -->
<sql:before>
<Students sql:id="2"></Students>
</sql:before>
<sql:after>
</sql:after>
</sql:ssync>
</DocumentElement>
7.7.7 FOR XML in the SQLXMLOLEDB Provider
A new OLE DB provider, SQLXMLOLEDB, allows the same processing of FOR XML output as the SQLOLEDB provider. The difference is that the FOR XML processing and conversion to XML occur on the client rather than inside SQL Server. This arrangement lets you optimize data transmission because data is transmitted using SQL Server's TDS protocol rather than XML. Because this client-side processing is exposed as an OLE DB service provider, it is possible that it may support providers other than SQLOLEDB in the future.
7.7.8 Bulk Loading
Bulk loading of XML to SQL Server is provided in SQLXML Web release 1. Because this is a COM interface available in .NET only through interoperability, I don't discuss this one further.
7.7.9 Future Support
Future plans for integration of SQL Server and XML include using SOAP as an output protocol (SQLXML3.0) and support of the relatively new XQuery language in addition to SQL and XPath.