Returning an XML Document as a String - 17 Apr 2000

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 KenSpencer@32x.com.

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More