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 nvarchar(5),        @EmployeeID int EXECUTE sp_xml_preparedocument @ixml OUTPUT, @xml SELECT @CustomerID = CustomerID, @EmployeeID = EmployeeID FROM OPENXML(@ixml, 'sp/row') WITH (        CustomerID nvarchar(5) '@CustomerID',        EmployeeID int '@EmployeeID' ) SELECT Customer.CustomerID, OrderID, LastName + ', ' + FirstName as EmployeeName FROM Customers Customer JOIN Orders [Order] ON Customer.CustomerID = [Order].CustomerID AND [Order].CustomerID = @CustomerID JOIN Employees Employee ON [Order].EmployeeID = Employee.EmployeeID AND [Order].EmployeeID = @EmployeeID FOR XML AUTO IF @@ROWCOUNT > 0        return 0 else        return 1 EXEC sp_xml_removedocument @ixml 

In the CREATE PROCEDURE statement, you declare a single input parameter @xml . This is a convention I use based on a principle of simplicity. Size @xml according to the size of your expected input xml. Your XML input string should contain all the values that, prior to the advent of XML support, would have been found in a complex list of input parameters of varying datatypes.

Next, declare the actual parameters that the procedure will need as local variables . A select statement that uses the OPENXML extension will then populate them.

Listing 41.11 shows the execution of our example using Query Analyzer and its result.

Listing 41.11 The Result of a Call to S_ORDERS_BY_CUSTOMER_AND_EMPLOYEE_XML

 S_ORDERS_BY_CUSTOMER_AND_EMPLOYEE_XML '<sp>         <row CustomerID=''ANTON'' EmployeeID=''7''/> </sp>' go XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------- <Customer CustomerID="ANTON">         <Order OrderID="10507" EmployeeName="King, Robert"/>         <Order OrderID="10573" EmployeeName="King, Robert"/> </Customer> 

You should standardize the structure of your XML input by using a common root element name . Then, format its child elements so they look like they could have been produced by the FOR XML clause. This is because OPENXML works a lot like FOR XML in reverse. Following are some suggested conventions:

• When your stored procedure needs input values that correspond to columns in multiple tables, create a flat input XML structure, resembling what FOR XML RAW might produce (as exemplified in Listing 41.10).

• When input values can be logically mapped to the columns in a single table and are small in terms of character count (say, 100 or less), name the attributes after the column to which they correspond. Then, name the element that holds these attributes after the table to which they belong, such as <CUSTOMERS CUSTOMERID='ANTON'/> . This is known as attribute-centric data mapping and it resembles the output produced by FOR XML AUTO .

• If input values are larger, store them in elements named after the column to which they correspond. (This is also safer in terms of data processing ”XML elements are less restricted than attributes in what characters they can contain.) These elements should then be child elements of an element named after the table to which they belong, such as <CUSTOMERS><CUSTOMERID>ANTON</CUSTOMERID></CUSTOMERS> . This is known as element-centric mapping and it resembles the output produced by FOR XML AUTO, ELEMENTS .

Use these techniques to structure your XML in a logical manner and debugging will be easier because your XML (whenever possible) is both human and machine-readable. In addition, XML is input ready for easy use with OPENXML . The more your input XML resembles table structures, the simpler your OPENXML queries will be.