naming column returned from FOR XML EXPLICIT query in SQL Server 2005
Home > IT Answers > Development > naming column returned from FOR XML EXPLICIT ...
Cramhead
15 pts.
0
Q:
naming column returned from FOR XML EXPLICIT query in SQL Server 2005
XML, SQL Server 2005
Is there a way to name the column returned from a query that includes a 
for xml explicit
?

I get a column named XML_F52E2B61-18A1-11d1-B105-00805F49916B. I'd like to name it something more appropriate for ORM software

The code I currently have is:


 Declare @RoleIDs table (RoleID int)
 insert into @RoleIDs (RoleID)
 select 1
 
 insert into @RoleIDs (RoleID)
 select 2
 
 insert into @RoleIDs (RoleID)
 select 3
 
 		SELECT distinct  1 as Tag,
 				null as Parent,	
 				SiteMapItemParent.SiteMapItemName [SiteMapHeading!1!Name],  	
 				null [SiteMapItem!2!Name], 
 				null [SiteMapItem!2!Url],
 				SiteMapItemParent.DisplaySequenceNumber as [SiteMapHeading!1!hide!hide]
 		FROM   SiteMapItem  INNER JOIN   
 			SiteMapItem as SiteMapItemParent on SiteMapItem.ParentSiteMapItemID = SiteMapItemParent.SiteMapItemID inner  join
 			SiteMapItemPolicy on 	SiteMapItemPolicy.SiteMapItemID = SiteMapItem.SiteMapItemID   inner join
 			  SystemRolePrivilege on SystemRolePrivilege.SystemPrivilegeID = SiteMapItemPolicy.SystemPrivilegeID inner join 
 				@RoleIDs as R on SystemRolePrivilege.SystemRoleID = R.RoleID
 
 		UNION All
 
 		--find all the children
 		SELECT distinct 2 AS Tag, 
 				1 AS Parent, 		
 				null [Name],  	
 				SiteMapItem.SiteMapItemName [SiteMapItem!2!Name], 
 				SiteMapItem.SiteMapItemUrlText [SiteMapItem!2!Url],
 				SiteMapItem.DisplaySequenceNumber as  [SiteMapItem!2!hide!hide]
 		FROM   SiteMapItem  INNER JOIN   
 			SiteMapItem as SiteMapItemParent on SiteMapItem.ParentSiteMapItemID = SiteMapItemParent.SiteMapItemID inner  join
 			SiteMapItemPolicy on 	SiteMapItemPolicy.SiteMapItemID = SiteMapItem.SiteMapItemID   inner join
 			  SystemRolePrivilege on SystemRolePrivilege.SystemPrivilegeID = SiteMapItemPolicy.SystemPrivilegeID inner join 
 			@RoleIDs as R on SystemRolePrivilege.SystemRoleID = R.RoleID
 		order by  [SiteMapHeading!1!hide!hide]
 
 		for xml explicit,  Root('SiteMap')
ASKED: Feb 5 2008  6:12 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46795 pts.
0
A:
 RATE THIS ANSWER
-1
Click to Vote:
  •   0
  •  -1
  • AddThis Social Bookmark Button
If you put the value of the XML document into a variable then select the variable then you can alias the variable.

This code is very simply, but will show the technique.

 DECLARE @XML AS xml
 set @XML = (select *
 from sys.tables
 FOR XML AUTO)
 
 select @XML AS 'TEST'
Last Answered: Feb 5 2008  10:41 PM GMT by Mrdenny   46795 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Cramhead   15 pts.  |   Feb 8 2008  7:21PM GMT

Thanks for you post but anytime that I use 

For XML explicit

it fail.

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,
 <a href="http://SiteMapItemParent.Name" title="http://SiteMapItemParent. " target="_blank">SiteMapItemParent.Name</a> [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],
 <a href="http://SiteMapItem.Name" title="http://SiteMapItem. " target="_blank">SiteMapItem.Name</a> [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,
 			 <a href="http://SiteMapItemParent.Name" title="http://SiteMapItemParent.	" target="_blank">SiteMapItemParent.Name</a> [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],
 			 <a href="http://SiteMapItem.Name" title="http://SiteMapItem.	" target="_blank">SiteMapItem.Name</a> [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 ColunmName

 
0