Use XML and SQL 2000 to manage stored procedure calls

Creating multiple stored procedures with different parameters to complete the same task is always a great burden. Sending parameters to your stored procedure using an XML string can simplify this task. This also simplifies the design of the COM component.

The method 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. You can not only obtain some parameters through XML, but also run queries on the DOM documents created in XML to encapsulate multiple stored procedures. I will provide some examples to show you how to implement this purpose and briefly describe each example.

In this example, I will pass several parameters to update the name field in a customer table. To obtain customerid (ID column) and new name fields, XML will be parsed. The XML string passed to the process is as follows:

<Root> <customer> <customerid> 3 </customerid> <Name> Acme
Inc. </Name> </customer> </root>

The storage fields to be created are as follows:

Create procedure update_customer (@ xmldatavarchar (8000)
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 = @ mermerid

This process first declares that the variables we will use will save the relevant information. After that, the DOM document is opened, and a "handle" is returned to the first parameter called by sp_xml_preparedocument.

The second parameter of this call is the XML source file used for the new Dom document. This "handle" is used to query information from the DOM when calling openxml. The second parameter called by openxml is an XPATH ing of the parent node, which contains the data to be executed.

The third parameter (2) indicates that the element-centric ing will be used. The with clause provides the rowset format for the parsed data. When sp_xml_removedocument is called, the source file of the DOM document is deleted.

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> <customerid>
2 </customerid> </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 (mermeridint ))

...

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

As you can see, Microsoft SQL 2000 makes the entire process a little simpler. Remember, the disadvantage of this method is that when SQL 2000 performs an XML task, sending XML as a parameter is limited to 8,000 characters. As in the past, do not ignore the benefits of careful planning.

Visit the msdn library to obtain more information about openxml, sp_xml_preparedocument, and sp_xml_removedocument.