为何SQL中XML的.query()在节点不存在时返回空字符串而非NULL？

嘿，刚接触SQL XML查询的话确实容易碰到这个问题！你给出的示例代码完全复现了场景：当<Detail>节点存在时，.query()返回正常的XML片段；可节点不存在时，它返回的是空字符串，而不是我们预期的NULL。

先把你的示例代码贴出来方便大家参考：

declare @xml xml 
set @xml = N'<xdoc><Header><OrderID>1234</OrderID><Detail><ProductID>12345</ProductID><Amount>12.50</Amount></Detail></Header></xdoc>' 
SELECT Tbl.Col.value('OrderID[1]', 'varchar(10)') as OrderID, Tbl.Col.query('Detail') as Detail 
FROM @xml.nodes('//Header') Tbl(Col) 

set @xml = N'<xdoc><Header><OrderID>1234</OrderID></Header></xdoc>' 
SELECT Tbl.Col.value('OrderID[1]', 'varchar(10)') as OrderID, Tbl.Col.query('Detail') as Detail 
FROM @xml.nodes('//Header') Tbl(Col)

为啥会这样？ #

这其实是SQL Server XML方法的设计特性：.query()方法的返回类型是xml，不管有没有匹配到节点，它都会返回一个xml类型的实例。当没有找到节点时，它返回的是空XML实例，这个实例在显示的时候看起来就是空字符串，但它本身并不是NULL值。

怎么改成返回NULL？ #

给你两种实用的解决办法：

方法1：用.exist()判断节点存在性（推荐）

.exist()方法可以直接判断节点是否存在，返回1（存在）或0（不存在），配合CASE语句就能轻松返回NULL：

declare @xml xml 
set @xml = N'<xdoc><Header><OrderID>1234</OrderID></Header></xdoc>' 
SELECT 
    Tbl.Col.value('OrderID[1]', 'varchar(10)') as OrderID,
    CASE WHEN Tbl.Col.exist('Detail') = 1 THEN Tbl.Col.query('Detail') ELSE NULL END as Detail
FROM @xml.nodes('//Header') Tbl(Col)

这个方法直接高效，不需要额外的类型转换，是最推荐的写法。

方法2：用NULLIF转换空XML实例

如果不想用CASE，也可以把空XML实例转成字符串后，用NULLIF把空字符串转成NULL，再转回xml类型：

declare @xml xml 
set @xml = N'<xdoc><Header><OrderID>1234</OrderID></Header></xdoc>' 
SELECT 
    Tbl.Col.value('OrderID[1]', 'varchar(10)') as OrderID,
    CAST(NULLIF(CAST(Tbl.Col.query('Detail') AS varchar(max)), '') AS xml) as Detail
FROM @xml.nodes('//Header') Tbl(Col)

不过这个方法需要两次类型转换，相比第一种稍显繁琐，适合特定场景下使用。

测试效果 #

用上面的方法，当<Detail>节点不存在时，Detail列就会返回NULL，完全符合你的预期啦！

内容的提问来源于stack exchange，提问作者Hazen