Update Temp table to Insert node in XML data using SQL, Sql Server

Update Temp table to Insert node in XML data using SQL

---Fake tables in Stored procedure1
create table #Cdetails(cid int, name varchar(5), age int)
insert into #Cdetails
values(1001,'John',12),
(1002,'Rick',19),
(1003,'Diane',25),
(1004,'Kippy',26)

--Output of Stored procedure 1:\

create table #final(xml_data xml)
insert into #final
select
XML_data =  
	(select ID = cd1.cid,
	details =
		(
		select cd.name,
		cd.age
		from #Cdetails cd
		where cd.cid = cd1.cid
		For XML Path(''), Type)
	from #Cdetails cd1
	For XML Path('Main'))

select * from #final

----------------------------------------------------------------

---2nd Stored procedure
create table #tbl(cid int,ctype varchar(5))
insert into #tbl
values(1001,'N'),
(1002,'U'),
(1003,'N'),
(1004,'N')

declare @type varchar(5)--@type should read through 'Ctype' value for every CID in #tbl

--Final output of 2nd procedure (incomplete select statement)
select
case when @type = 'N'
	then
	   select * from #final --(include 'N' after 'ID' node )
	when @rtpe = 'U'
	then 
		select * from #final --(include 'U' after 'ID' node)
  end

drop table #tbl,#Cdetails,final


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development