Parsing XML in SQL Server using Procedure "sp_xml_preparedocument" with xml has default na-CodePudding

Consider the following SQL run on SQL Server. The only difference between these two blocks is that one has the Default Namespace Set. Why does the one with the default namespace fail to parse?

I realize there are other ways to parse xml within sql server. I am just trying to understand the oddity in these two examples explicitly. Any help is appreciated.

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc ='  
<ROOT xmlns="Test" xmlns:B="anotherTest" >  
<Customers CustomerID="VINET" ContactName="Paul Henriot">  
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=  
           "1996-07-04T00:00:00">  
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>  
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>  
   </Orders>  
</Customers>  
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=  
           "1996-08-16T00:00:00">  
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>  
   </Orders>  
</Customers>  
</ROOT>';  
  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   
  
-- SELECT statement as written returns 0 records.  
SELECT    *  
FROM       OPENXML (@idoc, '/ROOT/Customers')   
EXEC sp_xml_removedocument @idoc;   
GO 


DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc ='  
<ROOT xmlns:A="Test" xmlns:B="anotherTest" >  
<Customers CustomerID="VINET" ContactName="Paul Henriot">  
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=  
           "1996-07-04T00:00:00">  
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>  
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>  
   </Orders>  
</Customers>  
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=  
           "1996-08-16T00:00:00">  
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>  
   </Orders>  
</Customers>  
</ROOT>';  
  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   
  
-- SELECT statement that uses the OPENXML rowset provider.  
SELECT    *  
FROM       OPENXML (@idoc, '/ROOT/Customers')   
EXEC sp_xml_removedocument @idoc;   

GO

Relevant Link: Proc sp_xml_preparedocument Documentation

SQL Server 2014

CodePudding user response：

The first doc has a default namespace so the names, so ROOT and Customers are in the namespace "Test", and the XPath expression "/ROOT/Customers" doesn't match them. You need to introduce a namespace alias for "Test" to use in your XPath expression.

You do this by providing a dummy XML doc with the alaised namespace declarations as the third argument to sp_xml_preparedocument, like this:

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc ='  
<ROOT xmlns="Test" xmlns:B="anotherTest" >  
<Customers CustomerID="VINET" ContactName="Paul Henriot">  
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=  
           "1996-07-04T00:00:00">  
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>  
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>  
   </Orders>  
</Customers>  
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=  
           "1996-08-16T00:00:00">  
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>  
   </Orders>  
</Customers>  
</ROOT>';  
  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, N'<dummy xmlns:d="Test"/>';   
  
-- SELECT statement that uses the OPENXML rowset provider.  
SELECT    *  
FROM       OPENXML (@idoc, '/d:ROOT/d:Customers')   
EXEC sp_xml_removedocument @idoc;   
GO