Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using OpenXML Part II

By Vasant Raj,

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.
Total article views: 8327 | Views in the last 30 days: 12
 
Related Articles
FORUM

OPENXML

Retreiving data from OPENXML

ARTICLE

Documenting Stored Procedures

Regular columnist Robert Marda discusses a few ideas on stored procedure documentation. How much doc...

ARTICLE

Using OpenXML

SQL Server 2000 added XML support awhile back, though it was limited in what is offered and can be a...

FORUM

OPENXML issue

Preparing Document for INSERT, SQL Server 2008

FORUM

Documention

Instance and DB documentation

Tags
openxml    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones