Integration of SQL Server-SQL Server and XML

XML is the latest data storage solution. XML gives web developers more programming flexibility than HTML. This new technology drives development institutions to try to integrate XML with their own products. Microsoft is the pioneer in taking such initiatives. When Microsoft was developing an Internet product, it was a little messy. Now it's different. Recently, a Microsoft senior employee said, "You have to give us a knife. XML is the only difference in the wound ." It may be a bit too much, but it is true that XML can be seen in almost all Microsoft products. For example, how does Microsoft integrate XML in its SQL Server product line? Next let's take a look at the key for XML clauses.

Get information in the name of XML

The first point of integration between SQL Server and XML is to create an XML file based on SQL data. The structure of XML files is not complex, and can be easily generated using simple scripts and ADO record sets. Although this task is not difficult, developers need to generate different scripts for the result sets they obtain from the server, or write more complex General scripts. The SELECT statement is then equipped with a new for XML clause.

The syntax of this clause is as follows:
[For {XML {raw auto explicit}
[, Xmldata]
[, Elements]
[, Binary base64]}]

The XML mode of the for XML clause is represented by raw, auto, or explicit. The format and composition of the result XML are determined. Next we will learn more about the above XML options through the following example.

Raw example
Run 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 result is as follows:
<Row orderid = "10248" orderdate = "1996-07-04t00: 00: 00" productid = "11"/>
<Row orderid = "10248" orderdate = "1996-07-04t00: 00: 00" productid = "42"/>
<Row orderid = "10248" orderdate = "1996-07-04t00: 00: 00" productid = "72"/>

Auto example
Run 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 result is as follows:
<Orders orderid = "10248" orderdate = "1996-07-04t00: 00: 00">
<Order_x0020_details productid = "11"/>
<Order_x0020_details productid = "42"/>
<Order_x0020_details productid = "72"/>
</Orders>

Explicit example
The explicit it mode gives query programmers full control over XML generation. However, this kind of control is expensive: You have to write every query so that SQL statements can contain XML Information.

The related syntax is complex and beyond the scope of this article. [, Xmldata] [, elements] [, binary base64] is an optional parameter.

Optional element


The example allows us to learn more about the internal working mechanism of various settings. Next we will further study the optional element xmldata of the for XML statement.

If you set this option, the XML-data schema will be included in the result set. The following are SQL statements:
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 preceding 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 = "regular"> <element type = "order_x0020_details" maxoccurs = "*"/>...

Elements
The elements option indicates that each data column is returned as a child element rather than an attribute. If you use auto mode, you can only use this option.

Binary base64
This option indicates that you want to use base64 encoding format to represent binary data.

There are many ways to use XML clauses. You 'd better refer to SQL online books.

Online Guide
SQL Server online books describe the usage of for XML clauses. It also mentions multiple restrictions that must be paid attention to when using this clause. For example, the for XML clause cannot be used with the view definition or the compute by clause. For more details, see the relevant documentation.

This article is just a brief description of the for XML clause. It is worth noting that this part is only a point of attention for integration of XML with SQL Server, other problems include IIS openxml functions and template files. It seems that every pore of SQL Server is filled with XML.