Using XML and SQL 2000来 to manage stored procedure calls _xml/rss

Creating multiple stored procedures with different parameters (stored procedure) is always a huge burden to accomplish the same task. Using XML strings to send parameters to your stored procedures simplifies this task, and makes COM components simpler to design.

The way to do this is to pass your parameters as an XML string, parse the XML to retrieve the data you need, and then continue to implement the functionality you need to integrate. Not only can you get some parameters from XML, you can also encapsulate multiple stored procedures by running queries on DOM documents created by XML. I will provide some examples to tell you if this is achieved and briefly describe each example.

In this example, to update the Name field in a Customer table, I pass a few arguments. XML is parsed to get the CustomerID (identity column) and the new name field. The XML string I pass to the process is like this:

<root><customer><customerid>3</customerid><name>acme
Inc.</name></customer></root>

The storage field to be created is as follows:

CREATE PROCEDURE Update_customer (@xmldatavarchar (8000)) as
DECLARE @customeridint
DECLARE @customernamevarchar (50)
DECLARE @xmldata_idint

EXEC sp_xml_preparedocument @xmldata_id OUTPUT, @xmldata, '

SELECT @customerid = CustomerID, @customername = [name] From
OPENXML (@xmldata_id, '//customer ', 2) with (Customeridint, [name]
varchar (50))

EXEC sp_xml_removedocument @xmldata_id

UPDATE Customer SET Customer. [Name] = ISNULL (@customername, customer.[ Name])
WHERE Customer.tblid = @customerid

This process first declares that the variable we are going to use will hold the relevant information. After that, the DOM document is opened, and a "handle" (handle) is returned to the first parameter of the sp_xml_preparedocument call.

The second parameter of this call is the XML source file for the new DOM document. This "handle" is used to query information from the DOM while making a OPENXML call. The second parameter of the OPENXML invocation is an XPath mapping of the parent node that contains the data to be executed.

The third argument (2) indicates that an element-centric mapping is used. The WITH clause provides the data column set (rowset) format for the parsed data, and the sp_xml_removedocument call deletes the source file for the DOM document.

In the following example, I pass a series of user IDs to delete multiple data columns. The following is the contents of the XML string:

<root><Customer><customerid>1</customerid></Customer><Customer>< Customerid>
2</customerid></customer><customer><customerid>3</customerid></customer>
</root>

The corresponding stored procedure looks like this:
. . .

EXEC sp_xml_preparedocument @xml_id OUTPUT, @xmldata, '

DELETE from the Customer WHERE customer.tblid in (SELECT CustomerID from
OPENXML (@xmldata_id, '//customer ', 2) with (Customeridint))

. . .

With this stored procedure, you no longer need to create a lengthy SQL query string to pass in ADO or call a stored procedure multiple times. This also eliminates the impact of multiple calls on network traffic.

As you can see, Microsoft's SQL 2000 makes the whole process a little bit simpler. Keep in mind that the disadvantage of this approach is that when SQL 2000 is doing XML tasks, sending XML as a parameter is limited to 8,000 characters. As always, don't overlook the benefits of careful planning.

Access to the MSDN Library for more information about OPENXML, sp_xml_preparedocument, and sp_xml_removedocument.