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
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
Select @NSEndPos = PATINDEX('%>%', @xmldoc) +1
Select @xmldoc = Substring(@xmldoc,@NSEndPos,Len(@xmldoc) - @NSEndPos +1)
Select @NSEndPos = PATINDEX('%>%', @xmldoc) +1
Select @xmldoc = '<MESSAGE>' + Substring(@xmldoc,
@NSEndPos,Len(@xmldoc) - @NSEndPos +1)
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!!