Using XML and SQL 2000 to manage stored procedure calls

Creating multiple stored procedures (stored PRocedure) with different parameters to complete the same task is always a big burden. This task can be simplified by using xml strings to send parameters to your stored procedures; this also makes the design of COM components easier.

The way to achieve 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 functions you need to integrate. Not only can you get some parameters through XML, you can also run queries on the DOM document created by XML to encapsulate multiple stored procedures. I'll provide some examples of how to accomplish this and briefly describe each example.

In this example, in order to update the name field in a Customer table, I will pass several parameters. The XML is parsed in order to obtain the customerid (identity column) and the new name field. The XML string I pass to the procedure looks like this:

<root><Customer><customerid>3</customerid><name>Acme
 Inc.</name></Customer></root>

The storage field to be created looks like this:

CREATE PROCEDURE update_Customer (@xmldatavarchar(8000)) ASDECLARE @customeridintDECLARE @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

The procedure first Declare the variables we will use to hold relevant information. After this, the DOM document is opened and a "handle" is returned as 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 when making OPENXML calls. The second parameter of the OPENXML call is an Xpath map of parent nodes that contain the data to be executed.

The third parameter (2) specifies that element-centered mapping will be used. The WITH clause provides a rowset format for the parsed data, and the sp_xml_removedocument call will delete the source file of the DOM document.

In the following example, I will pass a series of user IDs to delete multiple data columns. The following is the content 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 the following:

. . .
EXEC sp_xml_preparedocument @xml_id OUTPUT, @xmldata, ''
DELETE FROM Customer WHERE Customer.tblID IN (SELECT customerid FROM OPENXML(@xmldata_id, '//Customer', 2) WITH (customeridint))
. . .

With this stored procedure There is no 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 easier. Keep in mind that the downside to this approach is that when SQL 2000 does XML tasks, sending XML as a parameter is limited to 8,000 characters. As always, don’t overlook the benefits of careful planning.

Visit the MSDN library to get more information about OPENXML, sp_xml_preparedocument and sp_xml_removedocument.

The above is the content of using XML and SQL 2000 to manage stored procedure calls. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!