1

I have a single column table in SQL Server.

This column hold each row of an XML document.

Sample table :

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

Column data type is 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?

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

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
Ahmet Altun
  • 3,910
  • 9
  • 39
  • 64
  • `` is not valid XML. – Alvin Thompson Apr 21 '15 at 20:24
  • You will at least need a second column to order the table by. If you don't specify the ordering, select statements will return rows ordered at random. –  Apr 21 '15 at 20:36
  • 4
    You're **exceeding** the 2 GB (2 ***billion*** character) limit? REALLY?!?!? That's about **200 times** the whole text of Leo Tolstoj's *War and Peace* - a very thick book to begin with .... – marc_s Apr 21 '15 at 20:41
  • @marc_s according to [your own answer here](http://stackoverflow.com/a/11131977/2684660) it is actually **1** billion characters (**n**varchar has 2 bytes per character) which amounts to about 166 copies. I do like the unit of measure here though, 166 W&P of storage. And I wonder how many kW&P OP would need for their xml document. – asontu Apr 22 '15 at 07:29
  • @funkwurm: gosh - you're right :-) Sorry - good enough for at least 100 copies of "War & Peace" - but even that is hard to exceed ! :-) – marc_s Apr 22 '15 at 07:30

2 Answers2

0

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)

Community
  • 1
  • 1
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
0

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.

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)
asontu
  • 4,548
  • 1
  • 21
  • 29
  • Wouldn't this still bump up against the nvarchar(max) ceiling when cast in the .value() ? OP says "when concatenated; all data in the table exceeds nvarchar(max) limit" – nathan_jr Apr 22 '15 at 16:23
  • @NathanSkerl My assumption is that OP's column-restrictions are more limited than that of an `nvarchar(max)` variable. If it really is the 2 GB limit then I don't know how to help them, Roman Pekar's answer would run into the same problem because his `@res` is also `nvarchar(max)` – asontu Apr 23 '15 at 07:29