October 26, 2011 at 10:26 am
I am attempting to get an XML output for an ASP.NET Menu control, but I am getting an error that I cannot correct. Help is much appreciated on this since I am on a tight deadline.
The contents for the menu are in one table. To get this data, I have a query that contains a subquery. Both reference the same table, so I use AS a for one and AS b for the other.
The problem is that I get this error when I attempt to run the query:
Msg 6809, Level 16, State 1, Line 3
Unnamed tables cannot be used as XML identifiers as well as unnamed columns cannot be used for attribute names. Name unnamed columns/tables using AS in the SELECT statement.
However, I can't see where I don't have my columns and tables named.
Here is the table definition:
CREATE TABLE [dbo].[TableOfContents](
[iMenuItemID] [int] NOT NULL,
[iMenuItemPID] [int] NOT NULL,
[sMenuItemName] [varchar](50) NULL,
[sMenuItemURL] [varchar](255) NULL
)
Here is the query:
select
a.sMenuItemName as text,
a.sMenuItemURL as url ,
(
select
b.sMenuItemName as text,
b.sMenuItemURL as url
from
TableOfContent as b
where
b.iMenuItemPID = a.iMenuItemID
order by
b.iMenuItemID
for xml raw ('Submenu')
)
from
TableOfContent as a
where
a.iMenuItemPID = 0
order by
a.iMenuItemPID asc,
a.iMenuItemID asc
for xml raw ('Menu')
The output that I need to get I want to use in a Menu control. From what I understand, the information has to be in this basic format:
<Menu text="xxx" value="yyy'>
<Submenu text="xxx" value="yyy" />
<Submenu text="xxx" value="yyy" />
</Menu>
October 26, 2011 at 10:36 am
The short answer: add ', type' to the Submenu XML statement:
for xml raw ('Submenu'), type
Eddie Wuerch
MCM: SQL
October 26, 2011 at 11:46 am
The submenu XML column isn't named. That's what's causing the error. Can't have an unnamed column in an XML query.
Add something like "AS Submenu" after the close-parentheses for the sub-query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 12:07 pm
Thanks. That did work for me.
October 26, 2011 at 12:08 pm
Thanks for the response. That gave me a return set, but what it did was stuff the entire subquery into an XML element. It looks like adding ",type" to the subquery was the ticket.
October 27, 2011 at 6:10 am
For nested XML, it's usual to use the Type operator at all levels. Otherwise, the subquery ends up as a string value, and it can do weird things if you have ampersands, greater-than symbols, et al, in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2014 at 10:45 pm
Hi,
I want to add one more submenu to the main menu.
I can add it using the folluwing code.
But if there is any blank value for any of the columns then it returns the self closing tag in xml.
I want it in separate open close tag.
eg. if sMenuItemName is blank in table then in xml it returns <text />, however want it like <text></text>.
This work perfectly when we have only single submenu.
Please help.
select
a.sMenuItemName as text,
a.sMenuItemURL as url ,
(
select
b.sMenuItemName as text,
b.sMenuItemURL as url
from
TableOfContent as b
where
b.iMenuItemPID = a.iMenuItemID
order by
b.iMenuItemID
for xml raw ('Submenu'),type
),
(
select
c.sMenuItemName as text,
c.sMenuItemURL as url
from
TableOfContent as c
for xml raw ('Submenu1'),type
)
from
TableOfContent as a
where
a.iMenuItemPID = 0
order by
a.iMenuItemPID asc,
a.iMenuItemID asc
for xml raw ('Menu')
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply