Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Using XML in Stored Procedures

Now that you have seen several ways to produce XML documents from queries, it's time to take a look at how to deal with XML where you are most likely to use it: inside stored procedures. To talk to ActiveX Data Objects (ADO), URL queries, templates, and other data sources, your procedures need to know how to read and write XML. If you are not familiar with stored procedures, please refer to Chapter 28, “Creating and Managing Stored Procedures in SQL Server,” before proceeding. Listing 41.10 provides the basis for our discussion.

Listing 41.10. A Stored Procedure That Reads XML Input and Writes XML Output

CREATE PROCEDURE dbo.S_ORDERS_BY_CUSTOMER_AND_EMPLOYEE_XML (@xml varchar(1000)) as declare @ixml int, @CustomerID ...