SSMS中读取XML提取多语言long-description字段的实现问题

优先选择SQL Server原生XML数据类型方法（对应你原思路的Method2），相比OPENXML无需手动释放XML文档内存，性能和安全性更高，以下是两种输出形式的可直接运行代码：

输出形式1：2条记录，带lang标识列 #

DECLARE @doc NVARCHAR(max);   
SET @doc ='  
<enfinity>
  <offer sku="123456">
    <sku>123456</sku>
    <long-description xml:lang="de-DE">German</long-description>
    <long-description xml:lang="en-US">English</long-description>
  </offer>
</enfinity>
';   

DECLARE @T XML = @doc

SELECT 
    Y.ID.value('../@sku', 'int') as sku,
    Y.ID.value('.', 'nvarchar(max)') as [long-description],
    Y.ID.value('@xml:lang', 'nvarchar(10)') as lang
FROM @T.nodes('/enfinity/offer/long-description') as Y(ID)

运行后输出：

输出形式2：1条记录，两个语言分列 #

DECLARE @doc NVARCHAR(max);   
SET @doc ='  
<enfinity>
  <offer sku="123456">
    <sku>123456</sku>
    <long-description xml:lang="de-DE">German</long-description>
    <long-description xml:lang="en-US">English</long-description>
  </offer>
</enfinity>
';   

DECLARE @T XML = @doc

SELECT 
    Y.ID.value('@sku', 'int') as sku,
    Y.ID.value('(long-description[@xml:lang="de-DE"])[1]', 'nvarchar(max)') as [long-description-de],
    Y.ID.value('(long-description[@xml:lang="en-US"])[1]', 'nvarchar(max)') as [long-description-en]
FROM @T.nodes('/enfinity/offer') as Y(ID)

运行后输出：

原Method1（OPENXML写法）错误点 #

• 你已经通过OPENXML的第二个参数定位到了/enfinity/offer/long-description节点，取节点文本无需回退到父节点，直接用.即可
• xml:lang是当前long-description节点的属性，不是父节点属性，不需要加../前缀
• 注意OPENXML使用完必须调用sp_xml_removedocument释放内存，否则会出现内存泄漏
  修正后的OPENXML写法：

DECLARE @idoc INT, @doc NVARCHAR(max);   
SET @doc ='  
<enfinity>
  <offer sku="123456">
    <sku>123456</sku>
    <long-description xml:lang="de-DE">German</long-description>
    <long-description xml:lang="en-US">English</long-description>
  </offer>
</enfinity>
';   

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   

SELECT *  
FROM   OPENXML (@idoc, '/enfinity/offer/long-description')   
WITH(               
    sku   int    '../@sku',
    [long-description]  nvarchar(max)  '.',
    lang  nvarchar(10)  '@xml:lang'
);

-- 必须手动释放文档，避免内存泄漏
EXEC sp_xml_removedocument @idoc

原Method2错误点 #

• long-description是offer的子节点，不是属性，不能用@前缀取值
• 没有加XPath过滤条件区分两种语言的节点

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