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

I want to create the following xml file using sql query.

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

Appreciate if any one can help me with this.

Thank You in Advance
Pradeep chandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
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;

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');

Open in new window

Ryan ChongSoftware Team LeadCommented:
or probably this is better

declare @table table
(
	job varchar(10),
	[file] varchar(255),
	pollingIntervalInMinutes int
)
insert into @table values
('Job1', 'D:\Job\Triggerfile1.txt', 5),
('Job2', 'D:\Job\Triggerfile2.txt', 5),
('Job3', 'D:\Job\Triggerfile3.txt', 15),
('Job4', 'D:\Job\Triggerfile4.txt', 25);

declare @poolingInterval int
set @poolingInterval = 5

select @poolingInterval as '@pollingIntervalInMinutes',
	(
		select job, [file]
		from @table
		where pollingIntervalInMinutes = @poolingInterval
		For XML Raw('TriggerFile')
		, type
	)
 for xml Path('OnDemandMonitors')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
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

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>

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

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

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.