FOR XML子句可以将SQL查询返回的结果集转换为XML格式的数据。FOR XML子句包括以下几种模式(详细的子句语法,可以查看微软MSDN上的详细描述):
RAW
Auto
EXPLICIT
PATH
此处引入FOR XML子句是为了与读者分享一种高效的字段合并方式,笔者非常推崇这样的书写方式,因为它很高效,并且在资源消耗上也是远远小于传统编写方式的。
1.传统的编写方式
在一些业务中,经常会碰到合并字段的需求。此时,通常需要定义一个变量,然后通过变量将数据拼凑起来。假设有这样的一个业务需求:查询出某个采购单中所有子订单的提单号。子订单以行的方式存储在子表中,为了能够合并子订单编号,需要将子订单的数据查询出来,并进行字段合并。示例代码如下:
DECLARE @pOrderNum VARCHAR(MAX)=''; SELECT @pOrderNum+=soh.PurchaseOrderNumber+',' FROM Sales.SalesOrderHeader AS soh WHERE soh.PurchaseOrderNumber IS NOT NULL; 该语句消耗的时间如下: SQL Server Execution Times: CPU time = 172 ms, elapsed time = 193 ms.
执行以上语句可以看到,表中的数据占用了686个数据页,且均已经保存到了内存中,不会有I/O上的消耗,但依然使用了190毫秒,其效率并不是非常高。
2.高性能的字段合并
当采购单的数据量增长到当前的10万倍时,193ms*100000=19300s的速率可能令人无法接受,此时需要一个更高效的算法。看下面的这个示例:
SET @pOrderNum=( SELECT soh.PurchaseOrderNumber+',' FROM Sales.SalesOrderHeader AS soh WHERE soh.PurchaseOrderNumber IS NOT NULL FOR XML PATH('') ) 该语句消耗的时间如下: SQL Server Execution Times: CPU time = 15 ms, elapsed time = 3 ms.
从CPU和执行时间的消耗来看,FOR XML子句只用了3毫秒,性能却提升了60倍左右,这是一个非常可观的提升。