How to create XML in below format from SQL Query in SQL Server?

try:

Declare @txt varchar(max)
Declare @xml xml

set @txt = '<OnDemandMonitors pollingIntervalInMinutes="5">
<TriggerFile job="Job1" file="D:\Job\Triggerfile1.txt"></TriggerFile>
<TriggerFile job="Job2" file="D:\Job\Triggerfile2.txt"></TriggerFile>
</OnDemandMonitors>'

set @xml = convert(xml, N'' + @txt)

select @xml;

Select all Open in new window

or

declare @table table
(
	job varchar(10),
	[file] varchar(255)
)
insert into @table values
('Job1', 'D:\Job\Triggerfile1.txt'),
('Job2', 'D:\Job\Triggerfile2.txt');

select job, [file]
from @table
For XML Raw('TriggerFile'), Root('OnDemandMonitors');

Select all Open in new window

Your desired output doesn't really comply with 'normal' XML expectations.

The closing tag for " </Triggerfile> " is redundant because there are no other elements to be contained.

Ryan has given good examples above... and to show you what XML would be expecting, try this :

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

Select all Open in new window

and you will get :

<OnDemandMonitors pollingIntervalInMinutes="5">
  <TriggerFile job="Job1" file="D:\Job\Triggerfile1.txt" />
  <TriggerFile job="Job2" file="D:\Job\Triggerfile2.txt" />
</OnDemandMonitors>

Select all Open in new window

My solution was very similar to Ryan's :

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

Select all Open in new window

But, if you really need that closing triggerfile tag then you might need to manually intervene in a string field :

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

Select all Open in new window

But, would highly recommend reconsidering the need for that closing Triggerfile tag and go with the easier "Select ... For XML " option.