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:
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.