Solving problems while passing XML into a Stored Procedure

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