| 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. Criteria:
|
Background: 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: <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: |
<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 'C:\xml\' directory. |
<JOBREQ> <JOB> <JobID>711186</JobID> <EmployerID>2974554</EmployerID> <SendDate>05/02/2001</SendDate> <CompanyName>ABC Inc.</CompanyName>, etc. </JOB> </JOBREQ> |
Summary: 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. |
Turn XML into relational data with OPENXML
XML support has been significantly extended for SQL Server 2005. In this article, Tim Chapman takes a look at how you can shred XML data into a relational format with SQL Server's OPENXML function.
2007-02-22
4,779 reads