SQL Server數(shù)據(jù)庫(kù)中FOR XML AUTO的使用詳解續(xù)

作者：kkun 2011-08-24 09:15:36

本文我們主要介紹了SQL Server數(shù)據(jù)庫(kù)中FOR XML AUTO的使用的一些詳細(xì)的例子以及Order by對(duì)XML的影響的實(shí)例，希望能夠?qū)δ兴鶐椭?/div>

之前我們介紹過(guò)：SQL Server數(shù)據(jù)庫(kù)中FOR XML AUTO的使用詳解，本文我們介紹一些稍微復(fù)雜的應(yīng)用，首先我們先看下面的代碼：

CREATE TABLE Person(  
ID INT IDENTITY(1,1) NOT NULL,  
Name NVARCHAR(20) NOT NULL DEFAULT(''),  
Age INT DEFAULT(0) NOT NULL,  
Sex BIT DEFAULT(0) NOT NULL  
)  
--DROP TABLE [Order]  
CREATE TABLE [Order](  
ID INT IDENTITY(1,1) PRIMARY KEY,  
PersonID INT DEFAULT(0) NOT NULL,  
TotalPrice DECIMAL DEFAULT(0) NOT NULL  
)  
CREATE TABLE Product(  
ID INT IDENTITY(1,1) PRIMARY KEY,  
Price DECIMAL(8,2) DEFAULT(0.00) NOT NULL,  
Img NVARCHAR(50) DEFAULT('') NOT NULL  
)  
CREATE TABLE OrderDetail(  
ID INT IDENTITY(1,1) PRIMARY KEY,  
OrderID INT DEFAULT(0) NOT NULL,  
ProductID INT DEFAULT(0) NOT NULL,  
Price DECIMAL(8,2) DEFAULT(0.00) NOT NULL  
)  
INSERT INTO Person(Name,Age,Sex)  
SELECT '張三',28,1 UNION ALL  
SELECT '李四',28,1 UNION ALL  
SELECT '王五',28,1   
INSERT INTO [Order](PersonID,TotalPrice)  
SELECT 1,100.0 UNION ALL  
SELECT 1,111.0 UNION ALL  
SELECT 2,112.0 UNION ALL  
SELECT 3,98.0 UNION ALL  
SELECT 3,150.49 UNION ALL  
SELECT 3,58  
INSERT INTO Product(Price,Img)  
SELECT 101.0,'1.png' UNION ALL  
SELECT 102.0,'2.png' UNION ALL  
SELECT 103.0,'3.png' UNION ALL  
SELECT 104.0,'4.png' UNION ALL  
SELECT 105.0,'5.png'  
INSERT INTO OrderDetail(OrderID,ProductID,Price)  
SELECT TOP 3 3,ID,Price FROM Product  
SELECT * FROM Person  
SELECT * FROM [Order]  
SELECT * FROM Product  
SELECT * FROM OrderDetail  
Person  
ID Name Age Sex  
----------- -------------------- ----------- -----  
1 張三 28 1  
2 李四 28 1  
3 王五 28 1  
(3 行受影響)  
[Order]  
ID PersonID TotalPrice  
----------- ----------- ---------------------------------------  
1 1 100  
2 1 111  
3 2 112  
4 3 98  
5 3 150  
6 3 58  
(6 行受影響)  
Product  
ID Price Img  
----------- --------------------------------------- --------------------------------------------------  
1 101.00 1.png  
2 102.00 2.png  
3 103.00 3.png  
4 104.00 4.png  
5 105.00 5.png  
(5 行受影響)  
OrderDetail  
ID OrderID ProductID Price  
----------- ----------- ----------- ---------------------------------------  
1 1 1 101.00  
2 1 2 102.00  
3 1 3 103.00  
4 1 4 104.00  
5 1 5 105.00  
6 2 1 101.00  
7 2 2 102.00  
8 2 3 103.00  
9 3 1 101.00  
10 3 2 102.00  
11 3 3 103.00  
(11 行受影響)

理下其中的關(guān)系：

會(huì)員表Person與訂單表Order為一對(duì)多關(guān)系,其中Order.PersonID = Person.ID

訂單表與訂單詳情表為一對(duì)多關(guān)系,其中OrderDetail.OrderID = Order.ID

產(chǎn)品表與訂單詳情表為一對(duì)多關(guān)系,其中OrderDetail.ProductID = Product.ID

如下圖1

SQL Server數(shù)據(jù)庫(kù)中FOR XML AUTO的使用詳解續(xù) 圖1

可以開(kāi)始了:

先來(lái)看有那些訂單,分別為誰(shuí)的訂單,以及訂單詳情,SQL腳本及查詢(xún)結(jié)果如下：

1: SELECT   
2: [Order].ID,  
3: [Order].PersonID,  
4: OrderDetail.OrderID,  
5: OrderDetail.ProductID,  
6: OrderDetail.Price  
7: FROM [Order]  
8: INNER JOIN OrderDetail ON [Order].ID = OrderDetail.OrderID  
9: FOR XML AUTO,ROOT('Record')

查詢(xún)結(jié)果如下：

1: <Record> 
2: <Order ID="1" PersonID="1"> 
3: <OrderDetail OrderID="1" ProductID="1" Price="101.00" /> 
4: <OrderDetail OrderID="1" ProductID="2" Price="102.00" /> 
5: <OrderDetail OrderID="1" ProductID="3" Price="103.00" /> 
6: <OrderDetail OrderID="1" ProductID="4" Price="104.00" /> 
7: <OrderDetail OrderID="1" ProductID="5" Price="105.00" /> 
8: </Order> 
9: <Order ID="2" PersonID="1"> 
10: <OrderDetail OrderID="2" ProductID="1" Price="101.00" /> 
11: <OrderDetail OrderID="2" ProductID="2" Price="102.00" /> 
12: <OrderDetail OrderID="2" ProductID="3" Price="103.00" /> 
13: </Order> 
14: <Order ID="3" PersonID="2"> 
15: <OrderDetail OrderID="3" ProductID="1" Price="101.00" /> 
16: <OrderDetail OrderID="3" ProductID="2" Price="102.00" /> 
17: <OrderDetail OrderID="3" ProductID="3" Price="103.00" /> 
18: </Order> 
19: </Record>

調(diào)整下查詢(xún)列的順序,再看下結(jié)果,變了啊：

SELECT   
OrderDetail.OrderID,  
OrderDetail.ProductID,  
OrderDetail.Price,  
[Order].ID,  
[Order].PersonID  
FROM [Order]  
INNER JOIN OrderDetail ON [Order].ID = OrderDetail.OrderID  
FOR XML AUTO,ROOT('Record')

輸出結(jié)果很猛：

<Record> 
<OrderDetail OrderID="1" ProductID="1" Price="101.00"> 
<Order ID="1" PersonID="1" /> 
</OrderDetail> 
<OrderDetail OrderID="1" ProductID="2" Price="102.00"> 
<Order ID="1" PersonID="1" /> 
</OrderDetail> 
<OrderDetail OrderID="1" ProductID="3" Price="103.00"> 
<Order ID="1" PersonID="1" /> 
</OrderDetail> 
<OrderDetail OrderID="1" ProductID="4" Price="104.00"> 
<Order ID="1" PersonID="1" /> 
</OrderDetail> 
<OrderDetail OrderID="1" ProductID="5" Price="105.00"> 
<Order ID="1" PersonID="1" /> 
</OrderDetail> 
<OrderDetail OrderID="2" ProductID="1" Price="101.00"> 
<Order ID="2" PersonID="1" /> 
</OrderDetail> 
<OrderDetail OrderID="2" ProductID="2" Price="102.00"> 
<Order ID="2" PersonID="1" /> 
</OrderDetail> 
<OrderDetail OrderID="2" ProductID="3" Price="103.00"> 
<Order ID="2" PersonID="1" /> 
</OrderDetail> 
<OrderDetail OrderID="3" ProductID="1" Price="101.00"> 
<Order ID="3" PersonID="2" /> 
</OrderDetail> 
<OrderDetail OrderID="3" ProductID="2" Price="102.00"> 
<Order ID="3" PersonID="2" /> 
</OrderDetail> 
<OrderDetail OrderID="3" ProductID="3" Price="103.00"> 
<Order ID="3" PersonID="2" /> 
</OrderDetail> 
</Record>

XML結(jié)構(gòu)變了,是變了,MSDN中是這樣解釋滴：

***個(gè)ID引用自O(shè)rder表,故創(chuàng)建Order節(jié)點(diǎn)，PersonID同樣引用自O(shè)rder表,***步已經(jīng)創(chuàng)建Order節(jié)點(diǎn),故此處僅為節(jié)點(diǎn)添加PersonID屬性。

接下來(lái)三列OrderID,ProductID,Price引用OrderDetail表,因此在Order節(jié)點(diǎn)下創(chuàng)建OrderDetail子節(jié)點(diǎn) 列的別名顯示為屬性名稱(chēng),沒(méi)有別名使用列名，節(jié)點(diǎn)的名稱(chēng)使用表別名,沒(méi)有別名使用表名。

Order By對(duì)XML結(jié)構(gòu)的影響

再看個(gè)例子,對(duì)Order.PersonID排序:

SELECT   
[Order].ID,  
[Order].PersonID,  
OrderDetail.OrderID,  
OrderDetail.ProductID,  
OrderDetail.Price  
FROM [Order]  
INNER JOIN OrderDetail ON [Order].ID = OrderDetail.OrderID  
ORDER BY [Order].PersonId  
FOR XML AUTO,ROOT('Record')

輸出結(jié)果：

<Record> 
<Order ID="1" PersonID="1"> 
<OrderDetail OrderID="1" ProductID="1" Price="101.00" /> 
<OrderDetail OrderID="1" ProductID="2" Price="102.00" /> 
<OrderDetail OrderID="1" ProductID="3" Price="103.00" /> 
<OrderDetail OrderID="1" ProductID="4" Price="104.00" /> 
<OrderDetail OrderID="1" ProductID="5" Price="105.00" /> 
</Order> 
<Order ID="2" PersonID="1"> 
<OrderDetail OrderID="2" ProductID="1" Price="101.00" /> 
<OrderDetail OrderID="2" ProductID="2" Price="102.00" /> 
<OrderDetail OrderID="2" ProductID="3" Price="103.00" /> 
</Order> 
<Order ID="3" PersonID="2"> 
<OrderDetail OrderID="3" ProductID="1" Price="101.00" /> 
<OrderDetail OrderID="3" ProductID="2" Price="102.00" /> 
<OrderDetail OrderID="3" ProductID="3" Price="103.00" /> 
</Order> 
</Record>

其實(shí)這個(gè)結(jié)果與***個(gè)查詢(xún)結(jié)果是一樣滴,行默認(rèn)順序會(huì)影響XML結(jié)果,排序也會(huì)影響XML結(jié)構(gòu),只要當(dāng)前行與上一行數(shù)據(jù)可以合并時(shí),就會(huì)影響XML結(jié)構(gòu)，再來(lái)看一個(gè)排序影響XML結(jié)構(gòu)的例子,這次以O(shè)rderDetail.ProductID排序,直接看結(jié)果：

<Record> 
<Order ID="1" PersonID="1"> 
<OrderDetail OrderID="1" ProductID="1" Price="101.00" /> 
</Order> 
<Order ID="2" PersonID="1"> 
<OrderDetail OrderID="2" ProductID="1" Price="101.00" /> 
</Order> 
<Order ID="3" PersonID="2"> 
<OrderDetail OrderID="3" ProductID="1" Price="101.00" /> 
<OrderDetail OrderID="3" ProductID="2" Price="102.00" /> 
</Order> 
<Order ID="2" PersonID="1"> 
<OrderDetail OrderID="2" ProductID="2" Price="102.00" /> 
</Order> 
<Order ID="1" PersonID="1"> 
<OrderDetail OrderID="1" ProductID="2" Price="102.00" /> 
<OrderDetail OrderID="1" ProductID="3" Price="103.00" /> 
</Order> 
<Order ID="2" PersonID="1"> 
<OrderDetail OrderID="2" ProductID="3" Price="103.00" /> 
</Order> 
<Order ID="3" PersonID="2"> 
<OrderDetail OrderID="3" ProductID="3" Price="103.00" /> 
</Order> 
<Order ID="1" PersonID="1"> 
<OrderDetail OrderID="1" ProductID="4" Price="104.00" /> 
<OrderDetail OrderID="1" ProductID="5" Price="105.00" /> 
</Order> 
</Record>

以上就是FOR XML AUTO的應(yīng)用的相關(guān)知識(shí)，本文我們就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

【編輯推薦】

JSP鏈接SQL Server 2005出錯(cuò)的解決方法
JDBC調(diào)用帶輸出參數(shù)的存儲(chǔ)過(guò)程的實(shí)例解析
SQL Server數(shù)據(jù)庫(kù)多表關(guān)聯(lián)匯總查詢(xún)的問(wèn)題解決
設(shè)置SQL Server數(shù)據(jù)庫(kù)AWE機(jī)制使其支持大內(nèi)存
SQL Server 2008數(shù)據(jù)庫(kù)被標(biāo)記為可疑的解決方法

責(zé)任編輯：趙鵬來(lái)源：博客園

SQL Server數(shù)據(jù)庫(kù)FOR XML AUTO