default xmlns declartion breaks OPENXML ?

Question

Why does this work :

DECLARE @idoc int
declare @xml XML
SET @xml ='
<ROOT  xmlns:mstns="http://tempuri.org/XMLSchema.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" cardtype="3">
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml,'<ROOT xmlns:mstns="http://tempuri.org/XMLSchema.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" />'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT',1)
            WITH (cardtype  int)

 

but adding the default namespace declaration break this

 

DECLARE @idoc int
declare @xml XML

SET @xml ='
<ROOT xmlns="http://tempuri.org/XMLSchema.xsd" xmlns:mstns="http://tempuri.org/XMLSchema.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" cardtype="3">
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml,'<ROOT xmlns="http://tempuri.org/XMLSchema.xsd" xmlns:mstns="http://tempuri.org/XMLSchema.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" />'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT',1)
            WITH (cardtype  int)

Answer 1

Don't think OPENXML 'gets' default namespaces.  Try this:

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml,'<ROOT xmlns:ns="http://tempuri.org/XMLSchema.xsd" xmlns:mstns="http://tempuri.org/XMLSchema.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" />'

SELECT *
FROM OPENXML (@idoc, '/ns:ROOT',1)
WITH (cardtype int)

Answer 2

Also, as you're using the XML data-type you must be using SQL 2005 or 2008 in which case you can also do it like this:

;WITH XMLNAMESPACES( DEFAULT 'http://tempuri.org/XMLSchema.xsd' )
SELECT @xml.value('(ROOT/@cardtype)[1]', 'INT')

See these articles for more info:

Stop Using OPENXML (Please...)
https://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

xml Data Type Methods
http://msdn.microsoft.com/en-us/library/ms190798(SQL.90).aspx

XML Support in Microsoft SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

Answer 3

hi,

 check the below link:

http://nomisit.wordpress.com/2009/01/09/default-namespace-usage-for-openxml-in-sql-server-2005/

Hope you may get an idea of namespaces in OPENXML command.

---

Kiran