Introduction

OpenXML is a feature of SQL Server 2000, which allows manipulation of XML data in your database. You can get the basics for OpenXML by going through Books online or my previous article Using OpenXML.

Most of the applications use XML as their data carrier for bulk data processing. For example, you can pass a XML string to your stored procedure containing the list of selected ID’s of the record and return back or do some processing for that set of ID’s. This reduces your database trips.

There might be some instances where one of your procedures is calling another procedure and so on. The procedure is using XML as one of its parameter and some part of that same XML data needs to be processed in other procedures. It is not possible to break your XML data and pass it to other procedure. Surely you can pass the entire XML data again to the other procedure. This way of passing data uses more memory chunk as your XML data will be loaded whenever sp_xml_preparedocument is used in each procedure.

Another way for reusing your XML data is by using the document handle returned from sp_xml_preparedocument in the first procedure, as an argument to the other stored procedures using the same XML data. This document handle points to the internal representation of an XML document.

You do not need to load the XML data again in each procedure as the same document handle is used in each procedure.

Example:

This is the test table used.

CREATE TABLE  Test_table (
Id int IDENTITY (1, 1) NOT NULL ,
MyName VARCHAR(50) NULL ,
City VARCHAR(50) NULL
)
(bare with the NULL’s in the column definition :>)

Code for the main procedure which will have XML data as its parameter:

CREATE PROCEDURE Test_OpenXML
(
@strXML VARCHAR(8000)
)
AS
DECLARE @intPointer INT
SET @strXML =
EXEC sp_xml_preparedocument @intPointer output, @strXML
INSERT into Test_Table (MyName, City)
SELECT MYNAME , CITY FROM OpenXML(@intPointer,'/RECORD',2)
WITH (MYNAME VARCHAR(50), CITY VARCHAR(50))
EXEC Test_OpenXML_Child @intPointer
EXEC sp_xml_removedocument @intPointer
GO

Code for child procedure which is executed from the main procedure and has parameter of INT type, which will be used as document handle for OpenXML:

CREATE PROCEDURE Test_OpenXML_Child
(
@Pointer INT
)AS
INSERT into Test_Table (City)
SELECT CITY FROM OpenXML(@Pointer,'/RECORD',2)
WITH (CITY VARCHAR(50))
GO

In query analyzer use the following:

DECLARE @str VARCHAR(8000)

SET @str = '<RECORD><MYNAME>Vasant Raj</MYNAME><CITY>Vadodara</CITY></RECORD>'
EXEC Test_OpenXML @str

This query will insert two rows in the Test_table. This was a very simple example.

Conclusion

This logic can be implemented in very complex situations where you have all the details in single XML parameter and part of this XML data is used by different procedures.