Default namespace usage for OPENXML in SQL Server 2005

A little different this time but came across a problem the other day where I had a block of XML as text in SQL Server and I wanted to shred it using OPENXML. The document contained a default namespace, as per the sample given below:-

<launchparam xmlns=”uk.co.alliance-leicester.mortgages”>
   <dipconv>
       <callvisitid>2</callvisitid>
       <options>trigger=1</options>
   </dipconv>
</launchparam>’
 
I had not particularly noticed that “sp_xml_preparedocument” had a third parameter for namespaces and even when I did, various combinations I tried to no avail. The final combination that worked involved defining the namespace in “sp_xml_preparedocument” but also remembering to include the namespace tag prefix in the OPENXML command and the associated WITH portion as per the example below. Pay particular attention to all the places where the xml namespave tag prefix(“tag” in this case”) is used to get the query to work properly.

DECLARE @hdoc INT
DECLARE @xmlText NVARCHAR(4000)
SET @xmlText = ‘<launchparam xmlns=”uk.co.alliance-leicester.mortgages”>
<dipconv><callvisitid>2</callvisitid><triggeroption>trigger=1</triggeroption></dipconv></launchparam>’

 EXEC sp_xml_preparedocument @hdoc OUT, @xmltext, ‘<launchparam xmlns:tag=”uk.co.alliance-leicester.mortgages” />’

SELECT * FROM OPENXML(@hdoc, ‘/tag:launchparam/tag:dipconv’,2)
WITH ([tag:callvisitid] INT, [tag:triggeroption] NVARCHAR(100))

EXEC sp_xml_removedocument @hdoc

tag:callvisitid tag:triggeroption
—————— —————————————————————————————————-
2                            trigger=1

 

(1 row(s) affected)