Integration of SQL Server and XML

Server|sql|xml XML is the latest solution for data storage. XML gives web developers greater programming flexibility than HTML. This new technology drives the development organization to try to integrate XML with its own products. Microsoft is the pioneer of such a move. Microsoft has been slow at the time of developing Internet products, but it's different now. "You're going to give us a knife, and it's all XML in the wound," says a senior Microsoft executive. "Maybe it's a bit too much, but it's true: XML is visible in almost all of Microsoft's products." For example: How does Microsoft integrate XML into its SQL Server product line? Let's take a look at the key FOR XML clause below.
　　
Get information in the name of XML

The first thing about integration between SQL Server and XML is to create an XML file from SQL data. The structure of the XML file is not complex and can be easily generated with simple scripts and an ADO recordset. While this task is not a hassle, developers need to produce different scripts for the result sets they get from the server, or to write more complex generic scripts. The SELECT statement is thus equipped with a new FOR XML clause.
　　
The syntax of the clause is as follows:
[For {XML {RAW | AUTO | EXPLICIT}
[, XMLDATA]
[, ELEMENTS]
[, BINARY BASE64]} ]
　　
The XML schema for the FOR XML clause is represented by three parameter values: RAW, Auto, or explicit. The pattern determines the form and composition of the resulting XML. Let's take a closer look at each of these XML options with the following examples.
　　
Raw sample
We execute the following SQL statement:
SET ROWCOUNT 3
SELECT Orders.OrderID, Orders. OrderDate, ProductID
From Orders, [order Details]
WHERE Orders.OrderID = [Order Details]. OrderID
ORDER BY Orders.OrderID
For XML RAW
　　
The following results have been implemented:
<row orderid= "10248" orderdate= "1996-07-04t00:00:00" productid= "one"/>
Orderid= "10248" orderdate= "1996-07-04t00:00:00" productid= "<row"/>
<row orderid= "10248" orderdate= "1996-07-04t00:00:00" productid= "a"/>
　　
Auto Sample
We execute the following SQL statement:
' The result is limited to 3 records.
SET ROWCOUNT 3
SELECT Orders.OrderID, Orders. OrderDate, ProductID
From Orders, [order Details]
WHERE Orders.OrderID = [Order Details]. OrderID
ORDER BY Orders.OrderID
For XML AUTO
　　
The resulting results are as follows:
<orders orderid= "10248" orderdate= "1996-07-04t00:00:00" >
<order_x0020_details productid= "one"/>
<order_x0020_details productid= "/>"
<order_x0020_details productid= "/>"
</Orders>
　　
Explicit sample
The explicit mode gives the query programmer complete control over the resulting XML. But this control is expensive: you have to write each query so that SQL statements can contain XML information.
　　
The syntax is complex and beyond the scope of this article. [, XMLDATA] [, ELEMENTS] [, BINARY BASE64] is the appropriate optional parameter.
　　
Optional elements
　　

The example allows us to learn more about the internal mechanism of the various settings, and we'll take a closer look at the optional element xmldata for the XML statement below.
　　
If you set this option, the Xml-data schema will be included in the result set. The following is an SQL statement:
SET ROWCOUNT 3
SELECT Orders.OrderID, Orders. OrderDate, ProductID
From Orders, [order Details]
WHERE Orders.OrderID = [Order Details]. OrderID
ORDER BY Orders.OrderID
For XML AUTO, XMLDATA
　　
The above SQL statement produces the following results:
<schema name= "Schema2" xmlns= "Urn:schemas-microsoft-com:xml-data" xmlns:dt= "urn:schemas-microsoft-com": Datatypes ">
<elementtype name= "Orders" content= "eltonly" model= "closed" order= "many" > <element type= "order_x0020_" Details "maxoccurs=" * "/&GT;
　　
ELEMENTS
The elements option indicates that each data column is returned as a child element rather than as a property. If you use Auto mode, you can use this option only.
　　
BINARY BASE64
Use this option to indicate that you want to represent binary data in the Base64 encoding format.
　　
There is more to the use of XML clauses, you'd better refer to the SQL online books.
　　
Online Guide
The SQL Server online book describes the use of FOR XML clauses. It also mentions a number of limitations to be aware of when using this clause. For example, a FOR XML phrase cannot be combined with a view definition or a COMPUTE BY clause. You may wish to refer to the documentation for more details.
　　
This article is just a simple description of the FOR XML clause, and here's a reminder that this is just a bit of XML integration with SQL Server, and other issues include IIS OpenXML functions and template files. It looks like this, and every pore in SQL Server is dripping with XML.