如何使用SQL Server 2005中的XML功能

1. FOR XML RAW

SQL语句：

SELECT EmployeeID,FirstName, LastName
FROM Employees
WHERE LastName LIKE 'D%'FOR XML RAW

 

 

所产生的XML文档如下：

<row EmployeeID="1" FirstName="Nancy" LastName="Davolio" />
<row EmployeeID="9" FirstName="Anne" LastName="Dodsworth" />

 

2. FOR XML AUTO

SQL语句：

SELECT EmployeeID,FirstName, LastName
FROM Employees
WHERE LastName LIKE 'D%'FOR XML AUTO

 

所产生的XML文档：

<Employees EmployeeID="1" FirstName="Nancy" LastName="Davolio" />
<Employees EmployeeID="9" FirstName="Anne" LastName="Dodsworth" />

3、关系数据表查询1：

SELECT Customers.CustomerID,CompanyName,OrderID,
CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDate FROM dbo.Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML AUTO

生成XML文档结构：

4、关系数据表查询2：

SQL语句：

SELECT Customers.CustomerID,CompanyName,OrderID,
CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDate FROM dbo.Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML AUTO, ELEMENTS

生成XML文档结果：