Home > IT Answers > SQL Server > naming column returned from FOR XML EXPLICIT ...
Help

Question

  Asked: Feb 5 2008   6:12 PM GMT
  Asked by: Cramhead

naming column returned from FOR XML EXPLICIT query in SQL Server 2005


SQL Server 2005, XML

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')

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
-1
Click to Vote:
  •   0
  •  -1
Last Answered: Feb 5 2008  10:41 PM GMT  by Mrdenny




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'
  • AddThis Social Bookmark Button

Browse more Questions and Answers on SQL Server and Development.

Looking for relevant SQL Server Whitepapers? Visit the SearchSQLServer.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Cramhead  |   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,
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 ColunmName