Error 6833 when running a SQL XML format quer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error 6833 when running a SQL XML format quer

Author

Topic

Jayeff_land
Starting Member

2 Posts

Posted - 2008-04-03 : 08:34:58

Hi, I am working on a SQL Server query that is generating a XML file
format (with For XML EXPLICIT) and I am getting an error in the SQL
Server error when a executing the string with sp_executesql.
The Error is:
The Message is : Msg 6833.
Parent tag ID 2 is not among the open tags. FOR XML EXPLICIT requires
parent tags to be opened first. Check the ordering of the result set.

I have read a lot about the error on the forum except that I think
that in my case it is a little bit different cause the query return
the exact xml file but cannot execute the same string with the Execute
command.
When I am running the a copy of the query in a @str variable and
executing with 'execute sp_executesql @str' command or only with
Excute @str I am getting the error.
But...
When printing the string with print @str and executing (button
execute) the string within the query analyser it is working.

Here is my SQL query :

select
1 as tag,
NULL as parent,
ID_metric as [lvl1!1!ID_metric],
lineage as [lvl1!1!lineage],
sort as [lvl1!1!sort],
NULL as [lvl2!2!ID_metric],
NULL as [lvl2!2!lineage],
sort as [lvl2!2!sort],
NULL as [lvl3!3!ID_metric],
NULL as [lvl3!3!lineage],
sort as [lvl3!3!sort]
from
#buildData
t1
where
dbo.fn_countPatternInString('.',lineage)+1 = 1

union all

select
2 as tag,
1 as parent,
NULL as [lvl1!1!ID_metric],
NULL as [lvl1!1!lineage],
sort as [lvl1!1!sort],
ID_metric as [lvl2!2!ID_metric],
lineage as [lvl2!2!lineage],
sort as [lvl2!2!sort],
NULL as [lvl3!3!ID_metric],
NULL as [lvl3!3!lineage],
sort as [lvl3!3!sort]
from
#buildData t2
where
dbo.fn_countPatternInString('.',lineage)+1 = 2

union all

select
3 as tag,
2 as parent,
NULL as [lvl1!1!ID_metric],
NULL as [lvl1!1!lineage],
sort as [lvl1!1!sort],
NULL as [lvl2!2!ID_metric],
NULL as [lvl2!2!lineage],
sort as [lvl2!2!sort],
ID_metric as [lvl3!3!ID_metric],
lineage as [lvl3!3!lineage],
sort as [lvl3!3!sort]
from
#buildData t3
where
dbo.fn_countPatternInString('.',lineage)+1 = 3
order by
[lvl1!1!sort],
[lvl2!2!sort],
[lvl3!3!sort]
for XML explicit

For your information : buildData is a temporary table and
fn_countPatternInString is a function that is parsing a lineage to get
depht of the tree.

Anybody have an a clue about this isssue? Tx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources