sql-server - 从 openxml 中选择，将多个节点值连接到一个字符串

Question

我有带有多个标签的 XML，需要将一些值连接到一个字符串以进行输出。

这适用于 MS SQL 2012

DECLARE @XML AS XML, @hDoc AS INT

SELECT @XML = 
'<offers>
<offer>
    <a>AAA1</a>
    <param name="B">A1B</param>
    <param name="C">A1C</param>

</offer>
<offer>
    <a>AAA2</a>
    <param name="B">A2B</param>
    <param name="C">A2C1&amp;</param>
    <param name="C">A2C2&lt;</param>
</offer>
</offers>';


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT a, ParamB, ParamC
FROM OPENXML(@hDoc, 'offers/offer') 
WITH 
(
a [varchar](50) 'a',
ParamB [varchar](255) 'param[@name="B"]',
ParamC [varchar](255) 'param[@name="C"]'
) as S


EXEC sp_xml_removedocument @hDoc

这段代码的输出:

a       |ParamB |ParamC
-----------------------------
AAA1    |A1B    |A1C
AAA2    |A2B    |A2C1&

我需要:

a       |ParamB |ParamC
-----------------------------
AAA1    |A1B    |A1C
AAA2    |A2B    |A2C1& / A2C2<

"/"- 任何拆分器

更新

这个查询:

DECLARE @XML AS XML, @XML1 AS XML, @hDoc AS INT
DECLARE @delimiter VARCHAR(100)=' / ';

SELECT @XML = 
'<offers>
<offer>
    <a>AAA1</a>
    <param name="B">A1B</param>
    <param name="C">A1C</param>

</offer>
<offer>
    <a>AAA2</a>
    <param name="B">A2B</param>
    <param name="C">A2C1&lt;</param>
    <param name="C">A2C2&amp;</param>
</offer>
</offers>';


SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
          ,A.o.query('param[@name="B"]') B
          ,A.o.query('param[@name="C"]') C
    FROM @XML.nodes('/offers/offer') A(o);

实际输出我需要的，只需删除 xml 标签并在参数值之间插入分隔符。

这个查询解决了这个问题:

--Test XML variable equal col C for AAA2 from prev query
SET @XML1 = 
'<param name="C">A2C1&amp;</param><param name="C">A2C2&lt;</param>';

select (
select @delimiter + p.o.value('.', 'nvarchar(100)') 
          from @XML1.nodes('param') p(o)
          FOR XML PATH(''),TYPE 
     /* Use .value to uncomment XML entities e.g. &gt; &lt; etc*/
    ).value('.','VARCHAR(MAX)')      
    as C_result

但我不知道如何在以前的 SQL 查询中将其作为 XML 列的子查询。看:dbfiddle

Answer 1

首先:FROM OPENXML (与准备和删除文件的存储过程一起)已过时，不应再使用。而是使用 native XML methods由 XML 数据类型提供。

像这样尝试:

DECLARE @XML AS XML, @hDoc AS INT

SELECT @XML = 
'<offers>
<offer>
    <a>AAA1</a>
    <param name="B">A1B</param>
    <param name="C">A1C</param>
</offer>
<offer>
    <a>AAA2</a>
    <param name="B">A2B</param>
    <param name="C">A2C1</param>
    <param name="C">A2C2</param>
</offer>
</offers>';

--查询

WITH cte AS
(
    SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
          ,B.p.value('@name','nvarchar(100)') AS Param_Name
          ,A.o.query('.') TheOffer
    FROM @XML.nodes('/offers/offer') A(o)
    CROSS APPLY A.o.nodes('param') B(p)
)
SELECT Offer_a
      ,MAX(CASE WHEN Param_Name='B' THEN REPLACE(concatParams,' ',' / ') END) AS ParamB
      ,MAX(CASE WHEN Param_Name='C' THEN REPLACE(concatParams,' ',' / ') END) AS ParamC
FROM cte
CROSS APPLY(SELECT TheOffer.query('data(offer/param[@name=sql:column("Param_Name")]/text())').value('.','nvarchar(max)')) A(concatParams)
GROUP BY Offer_a;

简而言之:

cte 将返回一组 text()的 <a> , param/@name 的值和 XML 片段 <offer>对于对应的<a> .
魔法 发生在 CROSS APPLY(SELECT ...) 中.此子选择将获取适合当前行的 Param_Name 的参数在 row-wise 调用中。 XQuery 函数 sql:column()允许将当前行的值引入 XQuery 表达式。
非常重要的是 XQuery 函数 data() .这将返回此路径中由空格分隔的所有数据。遗憾的是，此功能不允许用户定义分隔符。
因此有一个严重的警告:如果您的参数值包含空格，您将不知道在哪里分隔它们...如果您需要这个，请回来发表评论。
第二个神奇之处是分组聚合。我们按Offer_a分组并使用 MAX()允许使用非分组列。这是一种老式的支点方法...
替换将放置斜杠而不是空格(来自 data() )。

更新:如果您的参数值中有空格...

方法一

我们使用 XQuery/FLWOR 遍历节点并在 Xquery 中进行连接。我们可以使用 sql:variable()将声明的变量引入 XPath 表达式:

DECLARE @delimiter VARCHAR(100)=' / ';

WITH cte AS
(
    SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
          ,B.p.value('@name','nvarchar(100)') AS Param_Name
          ,A.o.query('.') TheOffer
    FROM @XML.nodes('/offers/offer') A(o)
    CROSS APPLY A.o.nodes('param') B(p)
)
SELECT Offer_a
      ,MAX(CASE WHEN Param_Name='B' THEN STUFF(concatParams,1,LEN(@delimiter),'') END) AS ParamB
      ,MAX(CASE WHEN Param_Name='C' THEN STUFF(concatParams,1,LEN(@delimiter),'') END) AS ParamC
FROM cte
CROSS APPLY(SELECT TheOffer.query('for $p in offer/param[@name=sql:column("Param_Name")]/text()
                                   return <x>{concat(sql:variable("@delimiter"),$p)}</x>
                                  ').value('.','nvarchar(max)')) A(concatParams)
GROUP BY Offer_a;

...或方法 2

我们将所有值提取到一个中间集，并使用相关子查询 和 FOR XML获取连接参数的方法。

WITH cte AS
(
    SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
          ,B.p.value('@name','nvarchar(100)') AS Param_Name
          ,A.o.query('.') TheOffer
    FROM @XML.nodes('/offers/offer') A(o)
    CROSS APPLY A.o.nodes('param') B(p)
)
,cte2 AS
(
    SELECT cte.Offer_a
          ,cte.Param_Name
          ,A.relatedParams.value('text()[1]','nvarchar(100)') AS ParamValue
    FROM cte        
    CROSS APPLY TheOffer.nodes('offer/param[@name=sql:column("Param_Name")]') A(relatedParams)
)
SELECT Offer_a
      ,MAX(CASE WHEN Param_Name='B' THEN concatParamValues END) AS paramB
      ,MAX(CASE WHEN Param_Name='C' THEN concatParamValues END) AS paramC
FROM cte2
CROSS APPLY(SELECT STUFF((SELECT DISTINCT CONCAT(@delimiter,ParamValue) 
                          FROM cte2 csq 
                          WHERE csq.Offer_a=cte2.Offer_a
                            AND csq.Param_Name=cte2.Param_Name
                          FOR XML PATH('')),1,LEN(@delimiter),'')) A(concatParamValues)
GROUP BY Offer_a;

... 如果您的 XML 可能包含禁止使用的字符，请在末尾使用它

CROSS APPLY(SELECT STUFF((SELECT DISTINCT CONCAT(@delimiter,ParamValue) 
                          FROM cte2 csq 
                          WHERE csq.Offer_a=cte2.Offer_a
                            AND csq.Param_Name=cte2.Param_Name
                          FOR XML PATH(''),TYPE).value('.','nvarchar(100)'),1,LEN(@delimiter),'')) A(concatParamValues)

更新 2

您在评论中提出了一个额外的问题，但我必须承认，我并没有真正得到您所需要的。如果我理解正确，那么您的值(value)观中就有实体。你确实在原则上得到了结果，但这些实体仍然没有被翻译？正确吗？

在我上面的回答中，您已经有了所需的一切。为了清楚起见，一个完整的示例:

DECLARE @delimiter VARCHAR(100)=' / ';
DECLARE @XML AS XML, @hDoc AS INT

SELECT @XML = 
'<offers>
<offer>
    <a>AAA1</a>
    <param name="B">A1B&amp;</param>         <!-- Some typical entities -->
    <param name="C">A1C&lt;</param>
</offer>
<offer>
    <a>AAA2</a>
    <param name="B">A2B after space</param>   <!-- A case with some spaces -->
    <param name="C">A2C1 &#0065;</param>      <!-- The &#065; is the capital letter A as entity-->
    <param name="C">A2C2</param>
</offer>
</offers>';

WITH cte AS
(
    SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
          ,B.p.value('@name','nvarchar(100)') AS Param_Name
          ,A.o.query('.') TheOffer
    FROM @XML.nodes('/offers/offer') A(o)
    CROSS APPLY A.o.nodes('param') B(p)
)
,cte2 AS
(
    SELECT cte.Offer_a
          ,cte.Param_Name
          ,A.relatedParams.value('text()[1]','nvarchar(100)') AS ParamValue
    FROM cte        
    CROSS APPLY TheOffer.nodes('offer/param[@name=sql:column("Param_Name")]') A(relatedParams)
)
SELECT Offer_a
      ,MAX(CASE WHEN Param_Name='B' THEN concatParamValues END) AS paramB
      ,MAX(CASE WHEN Param_Name='C' THEN concatParamValues END) AS paramC
FROM cte2
CROSS APPLY(SELECT STUFF((SELECT DISTINCT CONCAT(@delimiter,ParamValue) 
                          FROM cte2 csq 
                          WHERE csq.Offer_a=cte2.Offer_a
                            AND csq.Param_Name=cte2.Param_Name
                          FOR XML PATH(''),TYPE).value('.','nvarchar(100)'),1,LEN(@delimiter),'')) A(concatParamValues)
GROUP BY Offer_a;

结果

Offer_a     paramB              paramC
AAA1        A1B&                A1C<
AAA2        A2B after space     A2C2 / A2C1 A

上面的“方法 1”会产生相同的结果:

WITH cte AS
(
    SELECT A.o.value('(a/text())[1]','nvarchar(100)') AS Offer_a
          ,B.p.value('@name','nvarchar(100)') AS Param_Name
          ,A.o.query('.') TheOffer
    FROM @XML.nodes('/offers/offer') A(o)
    CROSS APPLY A.o.nodes('param') B(p)
)
SELECT Offer_a
      ,MAX(CASE WHEN Param_Name='B' THEN STUFF(concatParams,1,LEN(@delimiter),'') END) AS ParamB
      ,MAX(CASE WHEN Param_Name='C' THEN STUFF(concatParams,1,LEN(@delimiter),'') END) AS ParamC
FROM cte
CROSS APPLY(SELECT TheOffer.query('for $p in offer/param[@name=sql:column("Param_Name")]/text()
                                   return <x>{concat(sql:variable("@delimiter"),$p)}</x>
                                  ').value('.','nvarchar(max)')) A(concatParams)
GROUP BY Offer_a;

最后，我希望这能解决您的问题...