Click here to Skip to main content
Click here to Skip to main content

Solving problems while passing XML into a Stored Procedure

, 23 Sep 2004
Rate this:
Please Sign up or sign in to vote.
Having problems while passing XML into a Stored Procedure because of Namespaces? This script would probably solve it.

Introduction

Generally, when XML data is being passed to a stored procedure, it would contain the XML version and encoding information along with namespace declarations. This sometimes interferes with the XPath queries. Here is one way we can solve the problem.

Suppose the XML is like this:

<?xml version="1.0" encoding="utf-8" ?> 
<MESSAGE xmlns="https://tempuri.org"> 
<HEADER> 
<TAG1>test</TAG1>
<TAG2></TAG2>
</HEADER>
<DETAILS>
<TAG1></TAG1>
<TAG2></TAG2>
</DETAILS>
</MESSAGE>

and the stored procedure:

create PROCEDURE ProcessXMLDocument

@xmlDoc varchar(8000)
AS
DECLARE @hDoc int
/*Load the document*/
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlDoc
DECLARE @message varchar(10)

SELECT @message= [TAG1]
FROM OPENXML
(@hdoc,'//HEADER',2)
WITH 
(TAG1 varchar(10))

When this XML is passed into the stored procedure, XPath queries would not always return the correct result. This query would not return anything.

Following code would solve the problem by removing the namespace tags:

create PROCEDURE ProcessXMLDocument
@xmlDoc varchar(8000)
AS
-------------------------------------------------------------------------
DECLARE @hDoc int,
@NSEndPos int
-------------------------------------------------------------------------
/*Remove the xml declaration as it messes up the
  document use the first end tag (>) as the marker for text removal*/
-------------------------------------------------------------------------
Select @NSEndPos = PATINDEX('%>%', @xmldoc) +1
Select @xmldoc = Substring(@xmldoc,@NSEndPos,Len(@xmldoc) - @NSEndPos +1)
-------------------------------------------------------------------------
/*Remove the Namespace as it messes up the document use
  the first end tag (>) as the marker for text removal*/
-------------------------------------------------------------------------
Select @NSEndPos = PATINDEX('%>%', @xmldoc) +1
Select @xmldoc = '<MESSAGE>' + Substring(@xmldoc, 
                  @NSEndPos,Len(@xmldoc) - @NSEndPos +1) 
-------------------------------------------------------------------------
/*Load the document*/
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlDoc
DECLARE @message varchar(10)

SELECT @message= [TAG1]
FROM OPENXML
(@hdoc,'//HEADER',2)
WITH 
(TAG1 varchar(10))

This would now return 'test'.

Enjoy!!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Ashutosh Singhal

United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
Generalpassing XML Pinmembergeorge ivanov17-Oct-05 10:37 
GeneralRe: passing XML PinmemberAshutosh Singhal18-Oct-05 0:03 
QuestionWhat are the parameters Pinmemberjayakumar peketi29-Apr-05 0:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411023.1 | Last Updated 23 Sep 2004
Article Copyright 2004 by Ashutosh Singhal
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid