declare @xml xml
set @xml =
'<OnDemandMonitors pollingIntervalInMinutes="5">
<TriggerFile job="Job1" file="D:\Job\Triggerfile1.txt"></TriggerFile>
<TriggerFile job="Job2" file="D:\Job\Triggerfile2.txt"></TriggerFile>
</OnDemandMonitors>'
select @xml
and you will get :
<OnDemandMonitors pollingIntervalInMinutes="5">
<TriggerFile job="Job1" file="D:\Job\Triggerfile1.txt" />
<TriggerFile job="Job2" file="D:\Job\Triggerfile2.txt" />
</OnDemandMonitors>
create table #sample_data
( [job] varchar(20),
[file] varchar(50),
[interval] int
);
GO
insert #sample_data values
('Job1','d:\job\triggerfile1',5),
('Job2','d:\job\triggerfile2',5);
GO
select g.interval as "@PollingIntervalInMinutes",
(select job as "@job", [file] as "@file" from #sample_data s where s.interval = g.interval FOR XML path ('TriggerFile'), type)
from #sample_data g group by interval for xml path('OnDemandMonitors')
GO
declare @str varchar(max) = replace(
(select g.interval as "@PollingIntervalInMinutes",
(select job as "@job", [file] as "@file" from #sample_data s where s.interval = g.interval FOR XML path ('TriggerFile'), type)
from #sample_data g group by interval for xml path('OnDemandMonitors')),'"/>','"></Triggerfile>')
Print @str
Open in new window
or
Open in new window