SQLServerCentral Article

Putting 'FOR XML' to Use


Putting 'FOR XML' To Use

Want to take advantage of some of the benefits of using XML for data exchange? 'FOR XML'

allows you to query RDBMS and return the data in XML format. I wont discuss 'FOR XML' at great length

here, there are many books that devote several chapters to the subject. What I'd like to do is take

a case study type approach for using 'FOR XML' in the field.

Several weeks ago, I was asked to build a real-time process to convert a job record in our database

to XML and save that XML stream out to a file. That file, in turn, would be picked up by another

process to be distributed to a regional office where the job would be filled by a recruiter.

This process was to only happen if the job met certain criteria.


  • The job must be assigned to be distributed to an internal affiliate. (In this case, the

    'ID' of the affiliate had to equal 24)

  • The company or individual who owned the job record had to belong to a certain group.

    (In this case the 'ID' of the group equals 7)

  • The process had to work in real-time


I work for a local area job board. One of the services we offer is to distribute a job

entered in our system to several other job boards at the request of the user.

When a job is posted via the website, in addition to the demographic and job description

information supplied, the user has the ability to choose to post the job to several job boards

i.e.(Monster, DICE, etc). So, when a user posts a job and chooses to post the job to a job board,

the job is initially inserted into the Jobs table. A trigger is fired in turn, and a record

for that job is inserted into the JobDistribution table for each job board the user has choosen

for that specific job. This table keeps track of the job boards that a job is assigned to be posted to.

A trigger also resides on the JobDistribution table. On insert, if the 'ID' of the affiliate = 24 and

the 'ID' of the group equals 7, then a process is started to build the XML file. Let's take a look at

the trigger on the JobDistribution Table.

CREATE TRIGGER [CreateXML] ON [dbo].[JobDistribution] 
@outputfile varchar(255),
@query varchar(50),
@templatefile varchar(255),
@JobID int,
@tmpFile varchar(255)
if exists(select jobid from inserted where ID = 24 and GroupID = 7)
select @JobID = jobid from inserted
select @tmpFile =  'C:\template.tpl'
select @outputfile =  'C:\xml\' + convert(varchar, @JobID) + 
'_' + convert(varchar(12), getdate(), 5) + '.xml'
select @query = 'exec sp_makeXML ' + convert(varchar, @JobID)
exec sp_makewebtask @outputfile, @query, @templatefile = @tmpFile

As you can see, this trigger references a variable called @outputfile . This is the filepath

where the final XML file will be saved. @JobID is of course the ID of the job. The @tmpFile

variable references a template file that dictates how the final XML file will look. The @query

variable contains the SQL query to return the job information that will be written to the file.


is a master database stored procedure that when passed the correct parameters, creates and saves

a file to a specified directory.

The stored procedure referenced in the @query variable is what actually returns the XML stream.

@JobID int
JOB.JobID, JOB.EmployerID, JOB.SendDate, 
JOB.CompanyName, JOB.Contact, JOB.Address1, etc.
from Jobs JOB where JOB.jobad_id = @JobID

By specifying 'FOR XML AUTO, ELEMENTS', the query results will be put into XML format and

fieldnames returned in the recordset will appear as elements in the XML stream. The parent tag will

get its name from the table name in the query. Here is a sample

of what the XML stream would look like:

<JOB><JobID>711186</JobID><EmployerID>2974554</EmployerID><SendDate>05/02/2001</SendDate><CompanyName>ABC Inc.</CompanyName>.....</JOB>

The basic format of the template file is as follows:

<TH>Title</TH> <TH>Price</TH> 

The <%begindetail%> and <%enddetail%> tags dictate the scope of where the data will be inserted. The

<%insert_data_here%> tags indicate the precise location within the scope where the data will be placed. The template file

does not have to appear exactly like this. Here is the template file I used.


So, here are the final results. A file named 71118620010507.xml (assuming today is May 7th 2001) will be saved out to the

'C:\xml\' directory.

<CompanyName>ABC Inc.</CompanyName>, etc.


Hopefully I've given you a good example of how 'FOR XML' can be used for practical applications.

There are many additional features and capabilites not discussed here. But, hopefully this article

will wet your appetite to implement 'FOR XML' in your everyday processes.


5 (1)




5 (1)