Passing parameters using simple T-SQL XML parsing

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

    Requires Free Membership to View

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)
                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

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

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


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:


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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.