Prev: ado
Next: bcp/bulk insert of csv file that contains comma
From: Derek Hart on 2 May 2010 14:55
I saw that in SQL Server 2005 the xml data has a limitation of 128 levels in
the xml itself. I cannot find anything listed about this for SQL Server
2008. Is there a limitation in 2008 also?


From: John Bell on 3 May 2010 06:37
On Sun, 2 May 2010 11:55:08 -0700, "Derek Hart" <derekmhart(a)yahoo.com>
wrote:

>I saw that in SQL Server 2005 the xml data has a limitation of 128 levels in
>the xml itself. I cannot find anything listed about this for SQL Server
>2008. Is there a limitation in 2008 also?
>

The limitation still exists and you could easily prove it

DECLARE @i int;
DECLARE @levels int;
DECLARE @xmlvar varchar(max)
SET @i = 0;
set @levels = 128;
SET @xmlvar = '<root>'

WHILE @i < @levels
BEGIN
SET @xmlvar = @xmlvar + '<level' + CAST(@i as varchar(3)) +
'>' ;
SET @i+=1 ;
END ;
SET @i-=1 ;
WHILE @i > -1
BEGIN
SET @xmlvar = @xmlvar + '</level' + CAST(@i as varchar(3)) +
'>' ;
SET @i-=1 ;
END ;

SET @xmlvar = @xmlvar + '</root>';
SELECT cast(@xmlvar as XML);

This gives you an error:

Msg 6335, Level 16, State 102, Line 21
XML datatype instance has too many levels of nested nodes. Maximum
allowed depth is 128 levels.

If you change the number of sublevels to 127 then the XML is cast
correctly.

John
 | 
Pages: 1
Prev: ado
Next: bcp/bulk insert of csv file that contains comma