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

15 pts.
Tags:
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')

Answer Wiki

Thanks. We'll let you know when a new response is added.

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'

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Cramhead
    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
    
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following