Creating multiple stored procedures with different parameters to complete the same task is always a big burden. Sending parameters to your stored procedures with XML strings can simplify this task; this also makes the design of COM components simpler.
The way to achieve this is to pass your parameters as an XML string, and dissect 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 will provide some examples that will tell you if this is achieved and briefly describe each example.
In this example, in order to update the name field in a Customer table, I will pass several parameters. To obtain the customerid (identity column) and the new name field, the XML is parsed. The XML string I passed to the procedure is 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)) 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 = @customerid
This process first states that the variables we are going to use will save relevant information. After this, the DOM document is opened and a "handle" will be returned to the first parameter of the sp_xml_preparedocument call.
The second parameter to this call is the XML source file for the new DOM document. This "handle" is used to query information from the DOM when making an OPENXML call. The second parameter of the OPENXML call is an Xpath map of the parent nodes, which contain the data to be executed.
The third parameter (2) indicates that the element-centric mapping will be used. The WITH clause provides a rowset format for the analyzed data. 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. Here 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 this:
. . .
EXEC sp_xml_preparedocument @xml_id OUTPUT, @xmldata, ''
DELETE FROM Customer WHERE IN (SELECT customerid FROM
OPENXML(@xmldata_id, '//Customer', 2) WITH (customeridint))
. . .
With this stored procedure, there is no longer a 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's SQL 2000 makes the whole process a little easier. Remember, the disadvantage of this method is that when SQL 2000 performs XML tasks, sending XML as a parameter will be limited to 8,000 characters. As always, don't ignore the benefits of careful planning.
Accessing the MSDN library allows you to get more information about OPENXML, sp_xml_preparedocument, and sp_xml_removedocument.