SQL Server从VARCHAR行填充XML变量

I have a single column table in SQL Server.

我在SQL Server中有一个列表。

This column hold each row of an XML document.

此列包含XML文档的每一行。

Sample table :

样品表:

         Column
---------------
Row1:    <ROOT>
Row2:    <Name>name1</Name>
Row3:    </ROOT>

Column data type is nvarchar(max)

列数据类型为nvarchar(max)

I want to do:

我想要做:

DECLARE @RES_XML XML

SET @XML = Set from table above

How can I sum up all rows of the table above and populate @RES_XML?

如何总结上表中的所有行并填充@RES_XML?

Note: when concatenated; all data in the table exceeds nvarchar(max) limit.

注意:连接时;表中的所有数据都超过nvarchar(max)限制。

2 个解决方案

#1

In general, easiest way to concatenate values into variable is

通常,将值连接到变量的最简单方法是

declare @res nvarchar(max)

select @res = isnull(@res, '') + [Column]
from <table above>

select cast(@res as xml)

Of course, order of concatenation in this query is not defined (but there is a trick to check if order is maintained)

当然,此查询中的连接顺序未定义(但有一个技巧可以检查是否维护订单)

#2

I am as surprised by your exceeding the limit of nvarchar(max) columns as Marc is, but maybe you have column limit settings in your database set-up that you can't change. Here's how I would do it, and I'm assuming there is some column that let's you order the tags in the appropriate order, you cannot rely on the order in which the database decided to store and retrieve the rows.

我很惊讶你超过了Marc的nvarchar(max)列的限制,但是你可能在数据库设置中有列限制设置,你无法改变。这是我将如何做到这一点,我假设有一些列让你按照适当的顺序订购标签,你不能依赖于数据库决定存储和检索行的顺序。

declare @t table (
    id int,
    x nvarchar(max)
)

insert into @t
select 1, '<ROOT>' union all
select 2, '<Name>name1</Name>' union all
select 3, '</ROOT>'

DECLARE @RES_XML XML

select @RES_XML = cast((
        select x
        from @t
        order by id
        for xml path(''), type
    ).value('.', 'nvarchar(max)') as xml)

#1