Integration of SQL Server and XML

Source: Internet
Author: User
Tags rowcount

XML is the latest solution for data storage. XML gives web developers greater programming flexibility than HTML. This technology-driven development organization tries to integrate XML with its own products. Microsoft is the pioneer of such a move. 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="11"/>
<row OrderID="10248" OrderDate="1996-07-04T00:00:00" ProductID="42"/>
<row OrderID="10248" OrderDate="1996-07-04T00:00:00" ProductID="72"/>

We execute the following SQL statement:

‘结果限制为3条记录。
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="11"/>
<Order_x0020_Details ProductID="42"/>
<Order_x0020_Details ProductID="72"/>
</Orders>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.