Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Solving problems while passing XML into a Stored Procedure

0.00/5 (No votes)
23 Sep 2004 1  
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