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

<%
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
%>

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), '&')
SET @safeXML = REPLACE(@safeXML, CHAR(96), ''')
SET @safeXML = REPLACE(@safeXML, CHAR(34), '"')
SET @safeXML = REPLACE(@safeXML, CHAR(60), '<')
SET @safeXML = REPLACE(@safeXML, CHAR(62), '<')
-- Now @safeXML has no illegal character
--     s

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), '&')
    	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

<%
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