Several readers have asked me how to generate a complete XML document or stream from a stored procedure, then return the XML document or stream as a string to the calling application. Let's look at the options.
You can perform this operation by using a stored procedure that creates a cursor and walks through the record set, setting the XML tags and data for each record. This technique works but introduces cursor overhead.
An alternative approach requires using Visual Basic (VB), Visual C++ (VC++), or any language that supports ADO 2.5. The Recordset object's Save method in ADO 2.5 features new functionality that, in one command, lets you save a record set to a string variable. You can then pass this string, for example, to the code that called the method. You can also pass the string to the Recordset object's Open method, which lets you open a record set from an XML stream. If you use this method, ADO properly formats the XML for you, but you lose the ability to define the schema because ADO formats the XML based on your record set.
Another option, if you have a simple relational structure that you want to implement in XML, is to use a Select statement to build the XML. In SQL Server Administration, "Using Stored Procedures to Build HTML Option Lists," I covered how to use a Select statement to build HTML. You can use this same method to build XML. You can then return the Select statement's output as a variable in the stored procedure, prefix and suffix the variable's string with the correct XML tags, and return the XML string as the stored procedure's return variable. With this technique, you don't need a cursor or to return a record set.
Have you found any other ways to generate an XML document from a stored procedure, then return the document as a string? If so, send your solutions to me at [email protected].
