xml路径的字符串串联 [英] Concatenation of strings by for xml path

问题描述

你好！ 今天，我学习了for xml path技术来连接mssql中的字符串.由于我从未在mssql中使用过xml，而Google却没有帮助，所以我需要问你.

Hi! Today I learned for xml path technique to concatenate strings in mssql. Since I've never worked with xml in mssql and google hasn't helped, I need to ask you.

让我们想象一下默认情况.我们需要连接表中的一些字符串:

Let's imagine the default case. We need to concatenate some strings from a table:

declare @xmlRepNames xml = (
    select          
        ', [' + report_name + ']'
    from (
        select distinct
            report_order,
            report_name
        from #report
    ) x
    order by
        report_order
    for xml path(''), type)

select
    stuff((select @xmlRepNames.value('.', 'nvarchar(max)')), 1, 1, '')

所以我得到这样的东西:

So I get smth like this:

[str1], [str2], [strn]

好的.它工作正常.但是我有两个非常相似的连接块.区别仅在于结果字符串的样子:

Ok. It works fine. But I have two very similar concatenate blocks. The difference is just in the way the result string looks like:

• [str1], [str2], [strn]
  和
• isnull([str1], 0) as [str1], isnull([str2], 0) as [str2], isnull([strn], 0) as [strn]

因此，我可以使用不同的字符串构造函数编写2个非常相似的代码块(已完成，顺便说一句)，或尝试将先前的代码扩展为具有包含2种构造函数的xml变量，然后按xml节点类型进行连接.第二种方式遇到了一些问题-我写了这样的话:

So I can write 2 very similar code blocks (already done, btw) with different string constructors or to try extend previous code to has xml variable containing 2 kind of constructors and then concatenate by xml node type. Doing 2nd way I met some problems - I wrote this:

declare @xmlRepNames xml = (
    select
        ', [' + report_name + ']' as name,
        ', isnull([' + report_name + '], 0) as [' + report_name + ']' as res
    from (
        select distinct
            report_order,
            report_name
        from #report
    ) x
    order by
        report_order
    for xml path(''), type)

select
    stuff((select @xmlRepNames.value('/name', 'nvarchar(max)')), 1, 1, ''),
    stuff((select @xmlRepNames.value('/res', 'nvarchar(max)')), 1, 1, '')

，但会引发错误"XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'".
例如，将'/name'替换为'/name[1]'或任何其他'/name[x]'时，将仅返回第x个名称"记录，但不是所有串联的名称"记录.
[问题] :是否可以像我想要的那样以第二种方式解决问题?如果可以，那么如何解决?
[免责声明] :这个问题现在对我来说并不十分严重(第一种方法有点丑陋，但还可以)，但是如何克服似乎很有趣:) 谢谢！

but it raise error "XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'".
To replace, e.g., '/name' to '/name[1]' or any other '/name[x]', will return just x-th 'name' record but not all 'name' records concatenated.
[question]: is it possible to solve problem 2nd way like I want and if it's possible then how?
[disclaimer]: the problem isn't really serious for me now (1st way just a little bit uglier but also fine), but it seems very interesting how to come over :) Thanks!

推荐答案

您的子查询不能返回两个值.如果只想连接字符串，则根本不需要xml数据类型.您可以在单个语句中执行stuff()和子查询:

Your subquery cannot return two values. If you just want to concatenate strings, you do not need the xml data type at all. You can do the stuff() and subquery in a single statement:

declare @Rep1Names nvarchar(max) = (
    stuff((select ', [' + report_name + ']' as name
           from (select distinct report_order, report_name
                 from #report
                ) x
           order by report_order
           for xml path('')
          )
         ), 1, 1, '');

declare @Rep2Names nvarchar(max) = (
    stuff(select ', isnull([' + report_name + '], 0) as [' + report_name + ']' as res
           from (select distinct report_order, report_name
                 from #report
                ) x
           order by report_order
           for xml path('')
          )
   ), 1, 1, '');

这篇关于xml路径的字符串串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！