for xml path question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

for xml path question

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-06-25 : 13:29:10

Guys,
I am rewriting some FOR XML EXPLICIT queries with the new XML PATH syntax. Check out the setup below.

1. Am i nesting the xml correctly? Is there a better way?
2. I have some question about the isEnabled flag on @RoleOperation. You can see in the query that i isnull the flag for Roles with no rows in RoleOperation, so that I always get back a complete xml tree. With that, it kinda makes the flag useless. Is it a better / worse design to remove the flag and process RoleOperation disables with a delete from the table vs an update on the flag?

Thanks for any feedback.


declare @Securable table (SecurableID tinyint, SecurableName varchar(50))
insert into @Securable
	select 1, 'SystemAdministration' union
	select 2, 'UserAdministration'

declare @Operation table (OperationID int, SecurableID tinyint, OperationName varchar(50))
insert into @Operation
	select 1, 1, 'A system operation' union
	select 2, 1, 'Another system operation' union
	select 3, 2, 'Create users' union
	select 4, 2, 'Edit user profiles'

declare @Role table (RoleID tinyint, RoleName varchar(50))
insert into @Role
	select 1, 'Administrators' union
	select 2, 'Limited Users'

declare @RoleOperation table (RoleID tinyint, OperationID int, isEnabled bit)
insert into @RoleOperation
	select 1, 1, 1 union
	select 1, 2, 1 union
	select 1, 3, 1 union
	select 1, 4, 1 union
	select 2, 1, 0 union
	select 2, 2, 0 union
	select 2, 3, 1 union
	select 2, 4, 0


declare @RoleID tinyint
set @RoleID = 1

select	r.RoleName as '@Name',
		(	select	s.SecurableName as '@Name',
					(	select	o.OperationName	as '@Name',
								isnull(ro.isEnabled, 0) as '@isEnabled'
						from	@Operation o		
						left
						join	@RoleOperation ro on
								o.OperationID = ro.OperationID and
								ro.RoleID = r.RoleID
						where	s.SecurableID = o.SecurableID
						for xml path('Operation'), type
					)
			from	@Securable s
			for xml path('Securable'), type
		)
from	@Role r
where	r.RoleID = @RoleID
for xml path ('Role'), root('root'), type

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources