时间戳

首页 > 解决方案 > 在 SSMS 中读取 XML

xml - 在 SSMS 中读取 XML

问题描述

我正在尝试生成一个输出，它会给我两个长描述，即一个用德语和一个用英语。它可以是两条记录，1 个 [long-description] 列和一个 [lang] 列，或者 1 个记录，同时具有 [long-description-de] 和 [long-description-en] 列。到目前为止，我已经找到了 2 种方法，不确定哪种方法更好，但我仍然无法使用其中任何一种方法产生准确的输出：

-- XML
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>
';   
  


-- Method 1 
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   

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


-- Method 2
DECLARE @T XML
SET @T = @doc

SELECT Y.ID.value('@sku', 'nvarchar(max)') as [sku],
        Y.ID.value('@long-description', 'nvarchar(max)') as [long-description-de],
        Y.ID.value('@long-description', 'nvarchar(max)') as [long-description-en]
FROM @T.nodes('/enfinity/offer') as Y(ID)

标签： xmlxml-parsingopenxmlsql-server-openxmlopenxml-table

解决方案

请尝试以下解决方案。

要点：

  • 最好使用 XML 数据类型而不是NVARCHAR(MAX).
  • 最好不要使用 Microsoft 专有OPENXML()的 . 它是为现已过时的 SQL Server 2000 制作的。
  • 最好使用 XQuery 方法.nodes()和.value(). 它们从 MS SQL Server 2005 开始可用。
  • 区分 XML 元素与属性很重要。这就是为什么你的两次尝试都没有成功的原因。

SQL

DECLARE @doc XML = 
N'<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>';

SELECT c.value('@sku', 'nvarchar(max)') as [sku]
    , c.value('(long-description[@xml:lang="de-DE"]/text())[1]', 'nvarchar(max)') as [long-description-de]
    , c.value('(long-description[@xml:lang="en-US"]/text())[1]', 'nvarchar(max)') as [long-description-en]
FROM @doc.nodes('/enfinity/offer') as t(c);

输出

+--------+---------------------+---------------------+
|  sku   | long-description-de | long-description-en |
+--------+---------------------+---------------------+
| 123456 | German              | English             |
+--------+---------------------+---------------------+

推荐阅读