Creating XML in a Stored Procedure

				Creating the Procedure


This procedure runs on an table called "tbl_Scripts" and returns data on ID (int), and Title (varchar) fields in 
an XML string.


This is the simple version, you can use an ADODB.Connection to run this procedure
CREATE PROCEDURE [sp_TestXML]
AS
DECLARE
	@XML 	varchar(8000),		-- output var
	@ID		varchar(18),		-- var to insert the ID field into
	@Title		varchar(62)		-- var to insert the Title field into
DECLARE Script_curs CURSOR FOR			-- We need a cursor to do the looping in
SELECT ('ID="' + CAST([ID] AS varchar(10)) + '"') AS ID, ('Title="' + [Title] + '"') AS Title
	FROM tbl_Scripts
	FOR READ ONLY
SET @XML = '<recordset>'
OPEN Script_curs
FETCH NEXT FROM Script_curs
	INTO @ID, @Title
	WHILE (@@FETCH_STATUS = 0 )
BEGIN
	SET @XML = @XML + '<record ' + @ID + ' ' + @Title + ' />'
	FETCH NEXT FROM Script_curs INTO @ID, @Title
END
SET @XML = (@XML + '</recordset>')
-- Output result as a 1 field record
SELECT XMLOUT = LTRIM(RTRIM(@XML))
CLOSE Script_curs
DEALLOCATE Script_curs

Then on your ASP Page:
<%
Option Explicit
Response.Buffer = True
Dim cn
Set cn = Server.createObject("ADODB.Connection")
cn.open Application("MyDSN")		' MyDSN is some valid connection string
Response.ContentType = "text/xml"	' For browsers that read mime types and if this is the only data on the page
Response.Write cn.execute("sp_TestXML").getString
cn.close
Set cn = Nothing
%>

This is pretty easy, you could make life more interesting by adding parameters, WHERE's etc.


The biggest problem with this example is that you only get 8000 characters (actually not that much!) to play with 
and you may find that you quickly run out of room.


The second problem is that if you use text data you may find illegal characters are present in the data. I tend 
to do a Server.HTMLEncode before I put data into 
servers but you could do a series of replaces on the text column ie
-- Title has illegal characters
SET @safeXML = REPLACE(Title, CHAR(38), '&amp;')
SET @safeXML = REPLACE(@safeXML, CHAR(96), ''')
SET @safeXML = REPLACE(@safeXML, CHAR(34), '&quot;')
SET @safeXML = REPLACE(@safeXML, CHAR(60), '&lt;')
SET @safeXML = REPLACE(@safeXML, CHAR(62), '&lt;')
-- Now @safeXML has no illegal characters

Since SQL is faster than both a compiled VB COM component and ASP if you need short XML strings this 
isn't a bad method and can be faster than the usual alternative of looping through RecordSets.


To make this reallt fly though we should avoid any recordset being created and just use an output parameter so
combining the two bits of SQL we have a new *safer* stored procedure
CREATE PROCEDURE [sp_TestXML2]
	@XMLOUT		varchar(8000)		OUTPUT		-- this time use an output parameter
AS
DECLARE
	@XML 		varchar(8000),	
	@ID		varchar(18),	
	@Title		varchar(62)	
DECLARE Script_curs CURSOR FOR
SELECT [ID], [Title] 
	FROM tbl_Scripts
	FOR READ ONLY
SET @XML = '<recordset>'
OPEN Script_curs
FETCH NEXT FROM Script_curs
	INTO @ID, @Title
	WHILE (@@FETCH_STATUS = 0 )
BEGIN
	-- Create safe text
	SET @Title = REPLACE(@Title, CHAR(38), '&amp;')
	SET @Title = REPLACE(@Title, CHAR(96), ''')
	SET @Title = REPLACE(@Title, CHAR(34), '"quot;')
	SET @Title = REPLACE(@Title, CHAR(60), '<lt;')
	SET @Title = REPLACE(@Title, CHAR(62), '>gt;')
	-- Create XML Node
	SET @XML = @XML + '<record ID="' + CAST(@ID AS varchar(5)) + + '" Title="' + @Title + '" />'
	FETCH NEXT FROM Script_curs INTO @ID, @Title
END
SET @XML = (@XML + '</recordset>')
-- output the xml as a single parameter
SELECT @XMLOUT = LTRIM(RTRIM(@XML))
CLOSE Script_curs
DEALLOCATE Script_curs

And new ASP code
<%
Option Explicit
Response.Buffer = True
Dim cmd
Set cmd = Server.createObject("ADODB.Command")
cmd.activeConnection = Application("MyDSN")
cmd.commandText = "sp_TestXML2"
cmd.commandType = adcmdStoredProc	' assuming you include the TypeLib for ADO
cmd.parameters.refresh
cmd.execute
Response.contentType = "text/xml"
Response.write cmd.parameters(1).value
cmd.ActiveConnection = Nothing		' Nice for connection pooling
Set cmd = Nothing
%>



And there you have it, all you need to create an XML string in a stored Procedure, of course this WILL all become
obselete when we can SELECT FOR ... XML AUTO in SQL2k


Something for the weekend?


I've attached a fairly undocumented VB application that helps you to generate these Stored Procedures.


Just type a valid connection string into the top left box, click Examine DB, select a table, 
the highlight the columns you want in the right hand drop down and click Generate SQL.


Thats the procedure done, then press Test SQL (requires IE5 or above) to view a sample XML file


Note


The EXE isnt guarenteed to work, I use it to generate the procedures then alter them by hand when i need 
more complex functionality, so if you have probs dont blame me!




"That SQL, that SQL, sometimes i wish it'd go to hell,


But you love that xml, even with it's funny smell..."



The Mat in the Hat