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

Pradeep chand
Pradeep chand used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Ryan ChongSoftware Team Lead

Commented:
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

Software Team Lead
Commented:
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

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial