Passing parameters using simple T-SQL XML parsing

Describes an alternative way of passing a varying number of input parameters from one T-SQL stored procedure to another by using the built-in OPENXML T-SQL row set statement.

This article describes an alternative way of passing a varying number of input parameters from one T-SQL stored procedure to another by using the built-in OPENXML T-SQL row set statement in order to parse a given XML string. The XML string representation for parameter passings will be in the following format:

 
<MSG  P1="V1"  P2="V2" P3="V3" .......  Pn="Vn"/>

where P1,P2...Pn are the parameter names and V1,V2,V3...Vn are their values.

The main advantage of this method is that all input parameters are passed via one XML string. This can create a standard way of passing parameters throughout the entire application, from one procedure to another inside the database or from the client application written in any language (VB, PowerBuilder, C, etc). This is possible because XML strings are a standard method for communicating between different platforms and technologies.

Another advantage of this method is that the parameters can be written and read in any order since the fetching and writing is done by giving the XML tag name. This eliminates the need to worry about parameter order in the calling program. The disadvantage of the method is that it incurs a little overhead in the constructing and parsing of the XML parameter string.

I designed a simple but effective T-SQL stored procedure (sp_get_xml_parameter_value) that gets the XML parameter string value (xmlString) and the name of the tag to be fetched (xmlTag). The result is entered into xmlValue as output. If the tag name does not exist, a NULL value is returned. The procedure creates a temporary table from the OPENXML inner table representation. It then links the ID of the given tag element with its value by doing a self-join on the temporary created OPENXML results, as shown in the procedure.

Here is the procedure code:

Create procedure sp_get_xml_parameter_value (@xmlString varchar(2000),
                @xmlTag         varchar(400),
                @xmlValue  varchar(400) OUTPUT)
AS
BEGIN  
                declare @Idoc int 
                set nocount on 
                set @xmlValue = NULL
                EXEC sp_xml_preparedocument @Idoc OUTPUT, @xmlString

SELECT    [id],parentid,nodetype,localname,[text]
                into #xmlTable
                FROM   OPENXML (@Idoc, '/MSG',1)
                select @xmlValue = CONVERT (varchar(400),b.[text])
                from #xmlTable a, #xmlTable b
                where a.localname               = @xmlTag and 
                      a.nodetype       = 2 and 
                      a.id                    = b.parentid and 
                      b.nodetype       = 3 
                set nocount off
END 
go        

Here is a T-SQL script example for calling the procedure:

declare @xmlString           varchar(2000)
declare @xmlTag              varchar(400)
declare @xmlValue            varchar(400)

set @xmlString = '<MSG ID="ELI LEIBA"  COMPANY="IEC" DESCRIPTION="APPLICATION DBA IN COMPANY"/>'

set @xmlTag = 'DESCRIPTION'
exec sp_get_xml_parameter_value @xmlString ,@xmlTag, @xmlValue OUTPUT
print @xmlValue

set @xmlTag = 'COMPANY'
exec sp_get_xml_parameter_value @xmlString ,@xmlTag, @xmlValue OUTPUT
print @xmlValue

set @xmlTag = 'ID'
exec sp_get_xml_parameter_value @xmlString ,@xmlTag, @xmlValue OUTPUT
print @xmlValue

After running the script, we get:

APPLICATION DBA IN COMPANY
IEC
ELI LEIBA

In conclusion, the procedure I showed here can implement a method of input parameter passing in applications. All input parameters are assembled into a XML string and then sent to the called procedure. It's up to the called procedure to extract whatever parameter it needs by using the sp_get_xml_parameter_value procedure. This procedure can create a standard of parameter passing and also eliminates the need to know the parameters' order.

About the author

Eli Leiba (e-mail: iecdba@hotmail.com) works at the Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the database field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies.

This was first published in December 2004

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close