How To: Use OpenXML on an Xml document with a default namespace (20670 Requests)

Answer provided by Brian.

Hopefully this will prevent another developer from working until 12:30 AM to solve a mystery. I looked for the answer to this one for 4 hours. None of the documentation, newsgroups or experts out there give a simple example of how to use sp_xml_preparedocument with an xml document that had a single default namespace. They cover namespaces where the tags are prefixed and where multiple namespaces exist. But they don't cover the simplest case.

If you want lots of fun take the prefix off of the field names (change [a:name] to [name])and watch the query return NULL for the fields.

 
/****** run the code below in sql query analizer ********/
 
DECLARE @hDoc int
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, 
   '<request xmlns="http://www.mytest.com">
     <table>
      <name>NameField</name>
      <date>2003-06-14</date>
      <time>10:15</time>
     </table>
    </request>', 
  '<root xmlns:a="http://www.mytest.com"/>'
 
SELECT *
FROM OPENXML(@hDoc, '//a:table', 2)
WITH ([a:name] varchar(50), [a:date] varchar(50), [a:time] varchar(50))
 
EXEC sp_xml_removedocument @hDoc
 

The above code works by supplying the default namespace to the third parameter of the sp_xml_preparedocument stored procedure. Even though there is no prefix used for the default namespace, we still need to provide a prefix in order to reference the namespace.

Feedback

# re: How To: Use OpenXML on an Xml document with a default namespace 5/20/2004 9:29 AM Simon Parsons

Does anyone know whether this is a bug with SQL server. It seems to me that the problem is that the OPENXML command does not recognize default namespaces.

This excellent fix is, i suspect a way around the problem, and not a microsoft intended practice.

# re: How To: Use OpenXML on an Xml document with a default namespace 6/9/2004 5:13 AM Guybrush T.

Thanks a lot for writing this page. We lost a couple of hours before discovering it and solving all of our problems :)

# re: How To: Use OpenXML on an Xml document with a default namespace 10/12/2004 7:21 AM Ed Harper

it's worth noting that the other way round this problem is to modify the namespace declaration int the XML with a suffix the same as the root tag. To use the example above:

<request xmlns:request="http://www.mytest.com">

you can then query the xml as if there were no namespace declared.

# re: How To: Use OpenXML on an Xml document with a default namespace 10/13/2004 4:09 AM Flemming

Thanks a lot for the input. Put in loads of hours well knowing the problem, but not knowing the solution. As it was put you could just put in a prefix for the namespace and blank works just as well, not having to specify any prefix in the WITH block. In my case I just have to convince the creator of the document to put in this prefix (blank or not), which is not likely to be appreciated. I have the same problem parsing the xmldocument with SAX and using the stylesheet functionality.

# re: How To: Use OpenXML on an Xml document with a default namespace 10/13/2004 4:51 AM Flemming

Thanks a lot for the input. Put in loads of hours well knowing the problem, but not knowing the solution. As it was put you could just put in a prefix for the namespace and blank works just as well, not having to specify any prefix in the WITH block. In my case I just have to convince the creator of the document to put in this prefix (blank or not), which is not likely to be appreciated. I have the same problem parsing the xmldocument with SAX and using the stylesheet functionality.

# Problem in inserting and updating table 5/24/2005 5:24 AM rojan

CREATE PROC IDS_UpdateExhibitorCategoryMap
@doc varchar(8000)

AS

BEGIN




--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
OpenXML(@hdoc, '/ROOT/Category')
with(Exhibitorid int '@Exhibitorid',RegionID int '@RegionID') XMLMap
set @Exhibitorid =XMLMap.ExhibitorID
set @RegionID=XMLMap.RegionID


if EXISTS (select CategoryID from CategoryExhibitorMap where Exhibitorid=@ExhibitorID and RegionID=@RegionID and Conferenceid=@Conferenceid)
begin

update CategoryExhibitorMap set CategoryID = XMLMap.CategoryID
FROM OpenXML(@hdoc, '/ROOT/Category')
with(CategoryID int '@CategoryID') XMLMap
where CategoryExhibitorMap.Exhibitorid=XMLMap.ExhibitorID and
CategoryExhibitorMap.RegionID=XMLMap.RegionID and
CategoryExhibitorMap.Conferenceid=XMLMap.Conferenceid

end
else
begin
-- sets featured exhibitors


insert into CategoryExhibitorMap (CategoryID,Exhibitorid,RegionID,Conferenceid)
select CategoryID FROM OpenXML(@hdoc, '/ROOT/Category') with(CategoryID int '@CategoryID')XMLMap
values(@CategoryID,@ExhibitorID,@RegionID,@Conferenceid)

end
-- Remove the internal representation.
exec sp_xml_removedocument @hdoc

END

This kind of procedure is possible with OpenXML?

# Thanks guys 3/3/2006 8:17 AM Rajiv Rajan

Hi guys,

Thanks a bunch there... it really helped us after 2 days (and nights) of hard work. Really appreciate the effort !

# re: How To: Use OpenXML on an Xml document with a default namespace 5/10/2006 11:31 AM xml man

not necessary to match the root name.
<request xmls:whatever="..."/> should work

# re: How To: Use OpenXML on an Xml document with a default namespace 6/28/2006 3:10 PM Nitin

Thanks, truly helpful

# re: How To: Use OpenXML on an Xml document with a default namespace 2/13/2007 8:06 AM Robert

Thank you!

# re: How To: Use OpenXML on an Xml document with a default namespace 6/8/2007 9:10 PM Andrew

This is great. Wish I read this yesterday!
Four years later and you're answer is still helping others!
Thank you for the clear example.

# re: How To: Use OpenXML on an Xml document with a default namespace 6/13/2007 11:26 PM Ramir

Very helpful! Thanks!

# re: How To: Use OpenXML on an Xml document with a default namespace 8/6/2007 11:59 AM Arun

Thanks

# xjgdlbpeo pujsxy 11/9/2007 11:35 AM hsijtacx@mail.com

hezyfcv qmnigj ukosargce urfpnzkyb ehqk tnow iypqz

# re: How To: Use OpenXML on an Xml document with a default namespace 1/10/2008 1:20 AM Pablo

Thanks everybody, because it helped me a lot.

# re: How To: Use OpenXML on an Xml document with a default namespace 6/5/2008 10:34 PM ls

This is great. Thanks a lot.

# Schema validation in sql server 2005 8/22/2008 5:17 AM Raju

Hi,
I want to validate my xml message with schema file registred in schema collections
could anybody plz help me?

Thanks

# re: How To: Use OpenXML on an Xml document with a default namespace 9/2/2008 3:04 PM Michael

this default namespace drove me crazy until I found your post... many thanks!

# re: How To: Use OpenXML on an Xml document with a default namespace 11/4/2008 1:53 PM vinzbee

Thanks a lot Brian!

# re: How To: Use OpenXML on an Xml document with a default namespace 11/27/2008 6:49 AM Adam

God (or gods) bless you, I've wasted only 3 hours of trial-and-mostly-error and lots of msdn search before I found this page, if it weren't for Your help, I would've probably waste a lot more. Thanks!

# re: How To: Use OpenXML on an Xml document with a default namespace 4/17/2009 1:25 PM Andrew

Thank you so much I wasted 5 hours trying to find the solution to this. Amazing how they don't give an example of AN INCREDIBLY COMMON SITUATION in the help. Thanks M$Soft.