0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

-- reasoncode table
declare @ReasonCode table
(
  id int,
  description nvarchar(50)
)

-- jobstation table
declare @JobStation table
(
	id int,
	description nvarchar(50)
)

-- Add sample data
insert into @ReasonCode values (0, 'Leave Early')
insert into @ReasonCode values (1, 'Job Station Change')
insert into @ReasonCode values (2, 'Personal Reasons')
insert into @ReasonCode values (3, 'Sick')
insert into @ReasonCode values (4, 'Scheduling Error')
insert into @ReasonCode values (5, 'Cancel')
insert into @ReasonCode values (6, null) -- just for testing

insert into @JobStation  values(0, 'Station A')
insert into @JobStation  values(1, 'Station B')
insert into @JobStation  values(2, 'Station C')
insert into @JobStation  values(3, 'Station D')
insert into @JobStation  values(4, 'Station E')
insert into @JobStation  values(5, null) -- just for testing

declare @xml xml
set @xml = (
	select    
		cast(rc.id      as int) as '@id'
		,rc.description as 'name'
		,case rc.id
		when 1 then
		(
			select
				js.id as '@id'
				,js.description as 'name'
			from @jobstation js
			for xml path('item'), type
		)
		else null
		end
	from @reasoncode rc
	for xml path('category'), root('root')
)

SET @xml.modify('delete //*[not(node())]')
select @xml

fork query

edited sep 7 12

created sep 7 12 anonymous