sql-server - 使用 FOR XML PATH 向根元素添加计数

Question

我有一个返回产品 xml 的 Sql 语句，其中根元素是产品。如何将计数属性添加到根元素。

我的sql是:

SELECT 
id AS 'product_id',
name AS 'product_name'
 FROM product
WHERE status = 1 AND ......
ORDER BY productid
FOR XML PATH('product'), ROOT('products')

结果是

<products>
   <product>
    .
    .
   </product>
</products>

我想把结果改成

<products count="100">
   <product>
    .
    .
   </product>
</products>

Answer 1

SELECT 
COUNT(*) AS '@count',
(
    SELECT * 
    FROM product c1 FOR XML PATH('product'), TYPE
)
FROM product ct FOR XML PATH('products')

最简单的过滤方式是在查询和子查询中都添加条件:

SELECT 
COUNT(*) AS '@count',
(
    SELECT * 
    FROM product c1 
    WHERE c1.status = 1 AND ......
    FOR XML PATH('product'), TYPE
)
FROM product ct 
WHERE ct.status = 1 AND ......
FOR XML PATH('products')

或者使用临时表:

SELECT *
INTO #Temp
FROM product c1
WHERE c1.status = 1 AND ......


SELECT 
COUNT(*) AS '@count',
(
    SELECT * 
    FROM #Temp c1 
    FOR XML PATH('product'), TYPE
)
FROM #Temp ct 
FOR XML PATH('products')