Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Putting 'FOR XML' to Use

By Jon Winer,

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:

  • 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

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. sp_makewebtask 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.

Total article views: 6040 | Views in the last 30 days: 3
 
Related Articles
FORUM

Insert query

Bulk Insert query

FORUM

Tricky ...VARCHAR

VARCHAR logics

FORUM

Insert into query

Insert into query

FORUM

I would like to insert from the clipboard text with line breaks in a "varchar" column.

I would like to insert from the clipboard text with line breaks in a "varchar" column.

FORUM

Help me for insert on query please

Help me for insert on query please

Tags
openxml    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones