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

数据库 SQL Server
本文我们主要介绍了SQL Server数据库中FOR XML AUTO的使用的一些详细的例子以及Order by对XML的影响的实例,希望能够对您有所帮助。

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

  1. CREATE TABLE Person(  
  2. ID INT IDENTITY(1,1) NOT NULL,  
  3. Name NVARCHAR(20) NOT NULL DEFAULT(''),  
  4. Age INT DEFAULT(0) NOT NULL,  
  5. Sex BIT DEFAULT(0) NOT NULL  
  6. )  
  7. --DROP TABLE [Order]  
  8. CREATE TABLE [Order](  
  9. ID INT IDENTITY(1,1) PRIMARY KEY,  
  10. PersonID INT DEFAULT(0) NOT NULL,  
  11. TotalPrice DECIMAL DEFAULT(0) NOT NULL  
  12. )  
  13. CREATE TABLE Product(  
  14. ID INT IDENTITY(1,1) PRIMARY KEY,  
  15. Price DECIMAL(8,2) DEFAULT(0.00) NOT NULL,  
  16. Img NVARCHAR(50) DEFAULT('') NOT NULL  
  17. )  
  18. CREATE TABLE OrderDetail(  
  19. ID INT IDENTITY(1,1) PRIMARY KEY,  
  20. OrderID INT DEFAULT(0) NOT NULL,  
  21. ProductID INT DEFAULT(0) NOT NULL,  
  22. Price DECIMAL(8,2) DEFAULT(0.00) NOT NULL  
  23. )  
  24. INSERT INTO Person(Name,Age,Sex)  
  25. SELECT '张三',28,1 UNION ALL  
  26. SELECT '李四',28,1 UNION ALL  
  27. SELECT '王五',28,1   
  28. INSERT INTO [Order](PersonID,TotalPrice)  
  29. SELECT 1,100.0 UNION ALL  
  30. SELECT 1,111.0 UNION ALL  
  31. SELECT 2,112.0 UNION ALL  
  32. SELECT 3,98.0 UNION ALL  
  33. SELECT 3,150.49 UNION ALL  
  34. SELECT 3,58  
  35. INSERT INTO Product(Price,Img)  
  36. SELECT 101.0,'1.png' UNION ALL  
  37. SELECT 102.0,'2.png' UNION ALL  
  38. SELECT 103.0,'3.png' UNION ALL  
  39. SELECT 104.0,'4.png' UNION ALL  
  40. SELECT 105.0,'5.png'  
  41. INSERT INTO OrderDetail(OrderID,ProductID,Price)  
  42. SELECT TOP 3 3,ID,Price FROM Product  
  43. SELECT * FROM Person  
  44. SELECT * FROM [Order]  
  45. SELECT * FROM Product  
  46. SELECT * FROM OrderDetail  
  47. Person  
  48. ID Name Age Sex  
  49. ----------- -------------------- ----------- -----  
  50. 1 张三 28 1  
  51. 2 李四 28 1  
  52. 3 王五 28 1  
  53. (3 行受影响)  
  54. [Order]  
  55. ID PersonID TotalPrice  
  56. ----------- ----------- ---------------------------------------  
  57. 1 1 100  
  58. 2 1 111  
  59. 3 2 112  
  60. 4 3 98  
  61. 5 3 150  
  62. 6 3 58  
  63. (6 行受影响)  
  64. Product  
  65. ID Price Img  
  66. ----------- --------------------------------------- --------------------------------------------------  
  67. 1 101.00 1.png  
  68. 2 102.00 2.png  
  69. 3 103.00 3.png  
  70. 4 104.00 4.png  
  71. 5 105.00 5.png  
  72. (5 行受影响)  
  73. OrderDetail  
  74. ID OrderID ProductID Price  
  75. ----------- ----------- ----------- ---------------------------------------  
  76. 1 1 1 101.00  
  77. 2 1 2 102.00  
  78. 3 1 3 103.00  
  79. 4 1 4 104.00  
  80. 5 1 5 105.00  
  81. 6 2 1 101.00  
  82. 7 2 2 102.00  
  83. 8 2 3 103.00  
  84. 9 3 1 101.00  
  85. 10 3 2 102.00  
  86. 11 3 3 103.00  
  87. (11 行受影响) 

理下其中的关系:

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

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

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

如下图1

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

可以开始了:

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

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

查询结果如下:

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

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

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

输出结果很猛:

  1. <Record> 
  2. <OrderDetail OrderID="1" ProductID="1" Price="101.00"> 
  3. <Order ID="1" PersonID="1" /> 
  4. </OrderDetail> 
  5. <OrderDetail OrderID="1" ProductID="2" Price="102.00"> 
  6. <Order ID="1" PersonID="1" /> 
  7. </OrderDetail> 
  8. <OrderDetail OrderID="1" ProductID="3" Price="103.00"> 
  9. <Order ID="1" PersonID="1" /> 
  10. </OrderDetail> 
  11. <OrderDetail OrderID="1" ProductID="4" Price="104.00"> 
  12. <Order ID="1" PersonID="1" /> 
  13. </OrderDetail> 
  14. <OrderDetail OrderID="1" ProductID="5" Price="105.00"> 
  15. <Order ID="1" PersonID="1" /> 
  16. </OrderDetail> 
  17. <OrderDetail OrderID="2" ProductID="1" Price="101.00"> 
  18. <Order ID="2" PersonID="1" /> 
  19. </OrderDetail> 
  20. <OrderDetail OrderID="2" ProductID="2" Price="102.00"> 
  21. <Order ID="2" PersonID="1" /> 
  22. </OrderDetail> 
  23. <OrderDetail OrderID="2" ProductID="3" Price="103.00"> 
  24. <Order ID="2" PersonID="1" /> 
  25. </OrderDetail> 
  26. <OrderDetail OrderID="3" ProductID="1" Price="101.00"> 
  27. <Order ID="3" PersonID="2" /> 
  28. </OrderDetail> 
  29. <OrderDetail OrderID="3" ProductID="2" Price="102.00"> 
  30. <Order ID="3" PersonID="2" /> 
  31. </OrderDetail> 
  32. <OrderDetail OrderID="3" ProductID="3" Price="103.00"> 
  33. <Order ID="3" PersonID="2" /> 
  34. </OrderDetail> 
  35. </Record> 

XML结构变了,是变了,MSDN中是这样解释滴:

***个ID引用自Order表,故创建Order节点,PersonID同样引用自Order表,***步已经创建Order节点,故此处仅为节点添加PersonID属性。

接下来三列OrderID,ProductID,Price引用OrderDetail表,因此在Order节点下创建OrderDetail子节点 列的别名显示为属性名称,没有别名使用列名,节点的名称使用表别名,没有别名使用表名。

Order By对XML结构的影响

再看个例子,对Order.PersonID排序:

  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. ORDER BY [Order].PersonId  
  10. 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> 

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

  1. <Record> 
  2. <Order ID="1" PersonID="1"> 
  3. <OrderDetail OrderID="1" ProductID="1" Price="101.00" /> 
  4. </Order> 
  5. <Order ID="2" PersonID="1"> 
  6. <OrderDetail OrderID="2" ProductID="1" Price="101.00" /> 
  7. </Order> 
  8. <Order ID="3" PersonID="2"> 
  9. <OrderDetail OrderID="3" ProductID="1" Price="101.00" /> 
  10. <OrderDetail OrderID="3" ProductID="2" Price="102.00" /> 
  11. </Order> 
  12. <Order ID="2" PersonID="1"> 
  13. <OrderDetail OrderID="2" ProductID="2" Price="102.00" /> 
  14. </Order> 
  15. <Order ID="1" PersonID="1"> 
  16. <OrderDetail OrderID="1" ProductID="2" Price="102.00" /> 
  17. <OrderDetail OrderID="1" ProductID="3" Price="103.00" /> 
  18. </Order> 
  19. <Order ID="2" PersonID="1"> 
  20. <OrderDetail OrderID="2" ProductID="3" Price="103.00" /> 
  21. </Order> 
  22. <Order ID="3" PersonID="2"> 
  23. <OrderDetail OrderID="3" ProductID="3" Price="103.00" /> 
  24. </Order> 
  25. <Order ID="1" PersonID="1"> 
  26. <OrderDetail OrderID="1" ProductID="4" Price="104.00" /> 
  27. <OrderDetail OrderID="1" ProductID="5" Price="105.00" /> 
  28. </Order> 
  29. </Record> 

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

【编辑推荐】

  1. JSP链接SQL Server 2005出错的解决方法
  2. JDBC调用带输出参数的存储过程的实例解析
  3. SQL Server数据库多表关联汇总查询的问题解决
  4. 设置SQL Server数据库AWE机制使其支持大内存
  5. SQL Server 2008数据库被标记为可疑的解决方法
责任编辑:赵鹏 来源: 博客园
相关推荐

2011-08-22 13:28:56

FOR XMLSQL Server

2011-08-24 12:49:56

SQL Server托管代码

2011-08-22 11:39:53

SQL Server数PIVOT

2011-08-11 09:12:31

SQL Server nolock

2021-03-18 08:20:19

SQLServer数据库SQL

2011-08-15 14:29:52

SQL Server数事务

2011-08-15 13:48:47

SQL ServerROW_NUMBER(

2011-08-19 14:53:02

SQL ServerDataRelatio

2011-04-02 11:02:54

SQL Server数文件恢复

2011-08-22 10:47:09

SQL Server流水号

2010-07-15 17:28:50

SQL Server

2011-08-30 11:04:30

链接查询内连接外连接

2011-04-02 11:14:20

SQL Server数XML

2011-04-13 15:44:12

SQL Server数函数

2010-03-16 10:12:40

SQL Server

2010-07-08 15:55:25

SQL Server

2011-08-22 10:32:32

SQL Server数XML节点XML块

2011-08-25 13:41:50

SQL Server 变更跟踪

2009-07-07 17:42:28

2011-08-09 09:31:39

SQL Server数connectionS
点赞
收藏

51CTO技术栈公众号