SQL Server数据库中FOR XML AUTO的使用详解续

之前我们介绍过：SQL Server数据库中FOR XML AUTO的使用详解，本文我们介绍一些稍微复杂的应用，首先我们先看下面的代码：

CREATETABLEPerson( IDINTIDENTITY(1,1)NOTNULL, NameNVARCHAR(20)NOTNULLDEFAULT(''), AgeINTDEFAULT(0)NOTNULL, SexBITDEFAULT(0)NOTNULL ) --DROPTABLE[Order] CREATETABLE[Order]( IDINTIDENTITY(1,1)PRIMARYKEY, PersonIDINTDEFAULT(0)NOTNULL, TotalPriceDECIMALDEFAULT(0)NOTNULL ) CREATETABLEProduct( IDINTIDENTITY(1,1)PRIMARYKEY, PriceDECIMAL(8,2)DEFAULT(0.00)NOTNULL, ImgNVARCHAR(50)DEFAULT('')NOTNULL ) CREATETABLEOrderDetail( IDINTIDENTITY(1,1)PRIMARYKEY, OrderIDINTDEFAULT(0)NOTNULL, ProductIDINTDEFAULT(0)NOTNULL, PriceDECIMAL(8,2)DEFAULT(0.00)NOTNULL ) INSERTINTOPerson(Name,Age,Sex) SELECT'张三',28,1UNIONALL SELECT'李四',28,1UNIONALL SELECT'王五',28,1 INSERTINTO[Order](PersonID,TotalPrice) SELECT1,100.0UNIONALL SELECT1,111.0UNIONALL SELECT2,112.0UNIONALL SELECT3,98.0UNIONALL SELECT3,150.49UNIONALL SELECT3,58 INSERTINTOProduct(Price,Img) SELECT101.0,'1.png'UNIONALL SELECT102.0,'2.png'UNIONALL SELECT103.0,'3.png'UNIONALL SELECT104.0,'4.png'UNIONALL SELECT105.0,'5.png' INSERTINTOOrderDetail(OrderID,ProductID,Price) SELECTTOP33,ID,PriceFROMProduct SELECT*FROMPerson SELECT*FROM[Order] SELECT*FROMProduct SELECT*FROMOrderDetail Person IDNameAgeSex ----------------------------------------------- 1张三281 2李四281 3王五281 (3行受影响) [Order] IDPersonIDTotalPrice ------------------------------------------------------------- 11100 21111 32112 4398 53150 6358 (6行受影响) Product IDPriceImg ---------------------------------------------------------------------------------------------------- 1101.001.png 2102.002.png 3103.003.png 4104.004.png 5105.005.png (5行受影响) OrderDetail IDOrderIDProductIDPrice ------------------------------------------------------------------------ 111101.00 212102.00 313103.00 414104.00 515105.00 621101.00 722102.00 823103.00 931101.00 1032102.00 1133103.00 (11行受影响)

理下其中的关系：

会员表Person与订单表Order为一对多关系,其中Order.PersonID = Person.ID

订单表与订单详情表为一对多关系,其中OrderDetail.OrderID = Order.ID

产品表与订单详情表为一对多关系,其中OrderDetail.ProductID = Product.ID

如下图1

图1

可以开始了:

先来看有那些订单,分别为谁的订单,以及订单详情,SQL脚本及查询结果如下：

1:SELECT 2:[Order].ID, 3:[Order].PersonID, 4:OrderDetail.OrderID, 5:OrderDetail.ProductID, 6:OrderDetail.Price 7:FROM[Order] 8:INNERJOINOrderDetailON[Order].ID=OrderDetail.OrderID 9:FORXMLAUTO,ROOT('Record')

查询结果如下：

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

调整下查询列的顺序,再看下结果,变了啊：

SELECT OrderDetail.OrderID, OrderDetail.ProductID, OrderDetail.Price, [Order].ID, [Order].PersonID FROM[Order] INNERJOINOrderDetailON[Order].ID=OrderDetail.OrderID FORXMLAUTO,ROOT('Record')

输出结果很猛：

<Record><OrderDetailOrderID="1"ProductID="1"Price="101.00"><OrderID="1"PersonID="1"/></OrderDetail><OrderDetailOrderID="1"ProductID="2"Price="102.00"><OrderID="1"PersonID="1"/></OrderDetail><OrderDetailOrderID="1"ProductID="3"Price="103.00"><OrderID="1"PersonID="1"/></OrderDetail><OrderDetailOrderID="1"ProductID="4"Price="104.00"><OrderID="1"PersonID="1"/></OrderDetail><OrderDetailOrderID="1"ProductID="5"Price="105.00"><OrderID="1"PersonID="1"/></OrderDetail><OrderDetailOrderID="2"ProductID="1"Price="101.00"><OrderID="2"PersonID="1"/></OrderDetail><OrderDetailOrderID="2"ProductID="2"Price="102.00"><OrderID="2"PersonID="1"/></OrderDetail><OrderDetailOrderID="2"ProductID="3"Price="103.00"><OrderID="2"PersonID="1"/></OrderDetail><OrderDetailOrderID="3"ProductID="1"Price="101.00"><OrderID="3"PersonID="2"/></OrderDetail><OrderDetailOrderID="3"ProductID="2"Price="102.00"><OrderID="3"PersonID="2"/></OrderDetail><OrderDetailOrderID="3"ProductID="3"Price="103.00"><OrderID="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] INNERJOINOrderDetailON[Order].ID=OrderDetail.OrderID ORDERBY[Order].PersonId FORXMLAUTO,ROOT('Record')

输出结果：

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

其实这个结果与***个查询结果是一样滴,行默认顺序会影响XML结果,排序也会影响XML结构,只要当前行与上一行数据可以合并时,就会影响XML结构，再来看一个排序影响XML结构的例子,这次以OrderDetail.ProductID排序,直接看结果：

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

以上就是FOR XML AUTO的应用的相关知识，本文我们就介绍到这里了，希望本次的介绍能够对您有所收获！

【编辑推荐】

JSP链接SQL Server 2005出错的解决方法JDBC调用带输出参数的存储过程的实例解析SQL Server数据库多表关联汇总查询的问题解决设置SQL Server数据库AWE机制使其支持大内存SQL Server 2008数据库被标记为可疑的解决方法