之前我们介绍过:SQL Server数据库中FOR XML AUTO的使用详解,本文我们介绍一些稍微复杂的应用,首先我们先看下面的代码:
class="dp-xml">
- 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 行受影响)
理下其中的关系:
会员表Person与订单表Order为一对多关系,其中Order.PersonID = Person.ID
订单表与订单详情表为一对多关系,其中OrderDetail.OrderID = Order.ID
产品表与订单详情表为一对多关系,其中OrderDetail.ProductID = Product.ID
如下图1
files/uploadimg/20110824/0941480.png" />图1
可以开始了:
先来看有那些订单,分别为谁的订单,以及订单详情,SQL脚本及查询结果如下:
- 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')
查询结果如下:
- 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>
调整下查询列的顺序,再看下结果,变了啊:
- 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')
输出结果很猛:
- <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结构变了,是变了,MSDN中是这样解释滴:
第一个ID引用自Order表,故创建Order节点,PersonID同样引用自Order表,第一步已经创建Order节点,故此处仅为节点添加PersonID属性。
接下来三列OrderID,ProductID,Price引用OrderDetail表,因此在Order节点下创建OrderDetail子节点 列的别名显示为属性名称,没有别名使用列名,节点的名称使用表别名,没有别名使用表名。
Order By对XML结构的影响
再看个例子,对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')
输出结果:
- <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>
其实这个结果与第一个查询结果是一样滴,行默认顺序会影响XML结果,排序也会影响XML结构,只要当前行与上一行数据可以合并时,就会影响XML结构,再来看一个排序影响XML结构的例子,这次以OrderDetail.ProductID排序,直接看结果:
- <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的应用的相关知识,本文我们就介绍到这里了,希望本次的介绍能够对您有所收获!