SQL Server中如何从字符串XML查询并关联关系型数据？

一、内联字符串转XML获取结果集的正确写法 #

可以通过内联方式将字符串转XML并提取结果集，之前的语法错误大概率是因为直接在nodes()方法中嵌套转换函数导致的。正确做法是先把字符串转XML放在子查询里，再通过CROSS APPLY拆解：

SELECT 
    x.value('(./MatchField)[1]', 'NVARCHAR(200)') AS XmlMatchValue,
    x.value('(./OtherField)[1]', 'INT') AS XmlOtherValue
FROM (SELECT CAST(@InputXmlStr AS XML) AS XmlDoc) AS XmlSource
CROSS APPLY XmlSource.XmlDoc.nodes('/Root/DataItem') AS XmlItems(x)

如果需要兼容非法XML格式，改用TRY_CAST避免查询崩溃：

SELECT 
    x.value('(./MatchField)[1]', 'NVARCHAR(200)') AS XmlMatchValue
FROM (SELECT TRY_CAST(@InputXmlStr AS XML) AS XmlDoc) AS XmlSource
CROSS APPLY XmlSource.XmlDoc.nodes('/Root/DataItem') AS XmlItems(x)
WHERE XmlSource.XmlDoc IS NOT NULL

二、关联XML与关系表的高效方案 #

针对数千行的数据量，优先选择性能最优的方案：

1. 先转临时表再关联（推荐） #

把XML拆解到临时表，给匹配字段加索引，再和关系表JOIN，能充分利用关系表的索引提升速度：

-- 把XML数据导入临时表
CREATE TABLE #XmlTemp (MatchValue NVARCHAR(200), OtherValue INT)
INSERT INTO #XmlTemp
SELECT 
    x.value('(./MatchField)[1]', 'NVARCHAR(200)'),
    x.value('(./OtherField)[1]', 'INT')
FROM (SELECT CAST(@InputXmlStr AS XML) AS XmlDoc) AS XmlSource
CROSS APPLY XmlSource.XmlDoc.nodes('/Root/DataItem') AS XmlItems(x)

-- 给临时表匹配字段加索引
CREATE INDEX IX_XmlTemp_Match ON #XmlTemp(MatchValue)

-- 关联关系表获取丰富数据
SELECT 
    xt.MatchValue,
    xt.OtherValue,
    rt.RelatedField1,
    rt.RelatedField2
FROM #XmlTemp xt
INNER JOIN RelationTable rt ON xt.MatchValue = rt.MatchField

-- 清理临时表
DROP TABLE #XmlTemp

2. 直接内联关联（适合简单场景） #

如果不允许使用临时表，也可以直接在单条查询中关联，但要注意：x.value()在JOIN条件中可能无法触发关系表索引，数据量大时性能会下降：

SELECT 
    x.value('(./MatchField)[1]', 'NVARCHAR(200)') AS XmlMatchValue,
    rt.RelatedField1,
    rt.RelatedField2
FROM (SELECT CAST(@InputXmlStr AS XML) AS XmlDoc) AS XmlSource
CROSS APPLY XmlSource.XmlDoc.nodes('/Root/DataItem') AS XmlItems(x)
INNER JOIN RelationTable rt ON x.value('(./MatchField)[1]', 'NVARCHAR(200)') = rt.MatchField

3. 返回丰富后的XML结果 #

如果需要输出XML格式，用FOR XML组装结果：

SELECT 
    xt.MatchValue AS '@MatchValue',
    xt.OtherValue AS 'OtherValue',
    rt.RelatedField1 AS 'Relations/Field1',
    rt.RelatedField2 AS 'Relations/Field2'
FROM #XmlTemp xt
INNER JOIN RelationTable rt ON xt.MatchValue = rt.MatchField
FOR XML PATH('EnrichedItem'), ROOT('EnrichedData')

三、适配fn-bea:execute-sql的注意事项 #

如果受标签限制只能用单条SQL（不允许DECLARE/临时表），直接用内联子查询的写法即可，确保所有逻辑在一个查询语句中完成。同时用NVARCHAR(MAX)存储输入的XML字符串，避免截断。

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