February 15, 2008 at 12:00 pm
How do I name a column when using a For XML Explicit clause.
I would have expected this to be simple, but it seems not
Here is a mock up that runs without any schema changes
Note the column name that results.
declare @SiteMapItem table (ID int IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(100),
URL nvarchar(100),
Parent int,
DisplayOrder int
)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('home', '/home', null, 1)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('help', '/home/help', 1, 3)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('about', '/home/about', 1, 2)
--Select * from @sitemapitem
SELECT distinct
1 as Tag,
null as Parent,
SiteMapItemParent.Name [SiteMapItem!1!Name],
null [SiteMapItem!2!Name],
null [SiteMapItem!2!Url]
FROM @SiteMapItem as SiteMapItem
INNER JOIN @SiteMapItem as SiteMapItemParent on SiteMapItem.Parent = SiteMapItemParent.ID
UNION All
--find all the children
SELECT distinct
2 AS Tag,
1 AS Parent,
null [SiteMapItem!1!Name],
SiteMapItem.Name [SiteMapItem!2!Name],
SiteMapItem.Url [SiteMapItem!2!Url]
FROM @SiteMapItem as SiteMapItem
INNER JOIN @SiteMapItem as SiteMapItemParent on SiteMapItem.Parent = SiteMapItemParent.ID
for xml explicit, Root('SiteMap')
The output looks like:
<SiteMap>
<SiteMapItem Name="home”>
<SiteMapItem Name="about” Url="/home/about” />
<SiteMapItem Name="help” Url="/home/help” />
</SiteMapItem>
</SiteMap>
When I try to name the column I by adding the
As ColumnName
I get an error “Incorrect syntax near the keyword ‘As’.”
The code that produces this is:
declare @SiteMapItem table (ID int IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(100),
URL nvarchar(100),
Parent int,
DisplayOrder int
)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('home', '/home', null, 1)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('help', '/home/help', 1, 3)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('about', '/home/about', 1, 2)
--Select * from @sitemapitem
SELECT distinct
1 as Tag,
null as Parent,
SiteMapItemParent.Name [SiteMapItem!1!Name],
null [SiteMapItem!2!Name],
null [SiteMapItem!2!Url]
FROM @SiteMapItem as SiteMapItem
INNER JOIN @SiteMapItem as SiteMapItemParent on SiteMapItem.Parent = SiteMapItemParent.ID
UNION All
--find all the children
SELECT distinct
2 AS Tag,
1 AS Parent,
null [SiteMapItem!1!Name],
SiteMapItem.Name [SiteMapItem!2!Name],
SiteMapItem.Url [SiteMapItem!2!Url]
FROM @SiteMapItem as SiteMapItem
INNER JOIN @SiteMapItem as SiteMapItemParent on SiteMapItem.Parent = SiteMapItemParent.ID
for xml explicit, Root('SiteMap') as ColumnName
February 15, 2008 at 12:24 pm
If I understand you correctly - you're looking to create an xml fragment using for XML Explicit, and then insert it into an XML column?
If so - you need to a. add on the ,TYPE at the end of the nested XML output, and then encapsulate it in parentheses FIRST, then name the column.
As in
SELECT --<<---This is new
( --<<---This is new
SELECT distinct
1 as Tag,
null as Parent,
SiteMapItemParent.Name [SiteMapItem!1!Name],
null [SiteMapItem!2!Name],
null [SiteMapItem!2!Url]
FROM @SiteMapItem as SiteMapItem
INNER JOIN @SiteMapItem as SiteMapItemParent
on SiteMapItem.Parent = SiteMapItemParent.ID
UNION All
SELECT distinct
2 AS Tag,
1 AS Parent,
null [SiteMapItem!1!Name],
SiteMapItem.Name [SiteMapItem!2!Name],
SiteMapItem.Url [SiteMapItem!2!Url]
FROM @SiteMapItem as SiteMapItem
INNER JOIN @SiteMapItem as SiteMapItemParent
on SiteMapItem.Parent = SiteMapItemParent.ID
for xml explicit, Root('SiteMap')
,TYPE) --<<this is new
as ColumnName
Type instructs it to turn that into a single XML fragment for use.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2008 at 6:44 pm
Matt Thanks so much for your response.
It didn't quite work out of the blocks, but the TYPE keyword was key and lead to this solution. The only significant change is the wrapping of the query with the union into a subselect.
declare @SiteMapItem table (ID int IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(100),
URL nvarchar(100),
Parent int,
DisplayOrder int
)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('home', '/home', null, 1)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('help', '/home/help', 1, 3)
insert into @SiteMapItem ([Name], URL, Parent, DisplayOrder) values
('about', '/home/about', 1, 2)
--Select * from @sitemapitem
select (
Select * from ( --New
SELECT distinct
1 as Tag,
null as Parent,
SiteMapItemParent.Name [SiteMapItem!1!Name],
null [SiteMapItem!2!Name],
null [SiteMapItem!2!Url]
FROM @SiteMapItem as SiteMapItem
INNER JOIN @SiteMapItem as SiteMapItemParent on SiteMapItem.Parent = SiteMapItemParent.ID
UNION All
--find all the children
SELECT distinct
2 AS Tag,
1 AS Parent,
null [SiteMapItem!1!Name],
SiteMapItem.Name [SiteMapItem!2!Name],
SiteMapItem.Url [SiteMapItem!2!Url]
FROM @SiteMapItem as SiteMapItem
INNER JOIN @SiteMapItem as SiteMapItemParent on SiteMapItem.Parent = SiteMapItemParent.ID
) As tbl --New
for xml explicit, Root('SiteMap') , Type) as ColumnName
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply