|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.
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] FOR INSERT AS Declare @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) begin 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 end
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.
CREATE PROCEDURE [sp_MakeXML] @JobID int AS select JOB.JobID, JOB.EmployerID, JOB.SendDate, JOB.CompanyName, JOB.Contact, JOB.Address1, etc. from Jobs JOB where JOB.jobad_id = @JobID FOR XML AUTO, ELEMENTS GO
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:
The basic format of the template file is as follows:
<HTML> <BODY> <TABLE> <TR> <TH>Title</TH> <TH>Price</TH> </TR> <%begindetail%> <TR> <TD><%insert_data_here%></TD> <TD><%insert_data_here%></TD> </TR> <%enddetail%> </TABLE> </BODY> </HTML>
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.
<JOBREQ> <%begindetail%> <%insert_data_here%> <%enddetail%> </JOBREQ>
So, here are the final results. A file named 71118620010507.xml (assuming today is May 7th 2001) will be saved out to the
<JOBREQ> <JOB> <JobID>711186</JobID> <EmployerID>2974554</EmployerID> <SendDate>05/02/2001</SendDate> <CompanyName>ABC Inc.</CompanyName>, etc. </JOB> </JOBREQ>
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.