OpenXML and depth issue

Question

i was reading a write up on openxml from here https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-openxml?view=sql-server-ver15

but one thing confused me.

see this example code

DECLARE @XmlDocumentHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"   
           OrderDate="1996-07-04T00:00:00">  
      <OrderDetail ProductID="11" Quantity="12"/>  
      <OrderDetail ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"   
           OrderDate="1996-08-16T00:00:00">  
      <OrderDetail ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>'  
-- Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument  
-- Execute a SELECT stmt using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)  
WITH (OrderID     int         '../@OrderID',  
      CustomerID  varchar(10) '../@CustomerID',  
      OrderDate   datetime    '../@OrderDate',  
      ProdID      int         '@ProductID',  
      Qty         int         '@Quantity')  
EXEC sp_xml_removedocument @XmlDocumentHandle  

This line confuse me FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)

here they pass 2 but i think it should be 4. there root then customer then order and then orderdetail...so total 4

so how it can be 2 rather it should be 4.

please tell me why they wrote 2 instead of 4 ?

please discuss this. thanks

Answer 1

Hi sudip_inn,

Starting from SQL Server 2005 onwards, it is better to use XQuery language, based on the w3c standards, while dealing with the XML data type. Microsoft proprietary OPENXML and its companions sp_xml_preparedocument  and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000.
It is strongly recommended to re-write your SQL and switch it to XQuery.

Back to your question.

That last parameter is optional, and it is about elements vs. attributes, as documented here:

https://docs.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql?view=sql-server-ver15

P.S. Still waiting for you to up-vote the suggestion:

https://feedback.azure.com/forums/908035-sql-server/suggestions/38142115-sql-server-vnext-post-2019-and-nosql-functionali

Answer 2

I am trying to know why they use 2 instead of 4....please explain if you understand the reason. thanks

This line confuse me FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)

Answer 3

Hi Sudip_inn,

MS documentation doesn't specify optional parameter as 4. Its enumerator: 0, 1, 2, and 8.

That parameter has nothing to do with the 'depth' of XML.

That last parameter is optional, and plays a role just when the explicit mapping via WITH (...) clause is NOT provided.

Here is an example for you.

• When the parameter is 2, i.e. element-centric, it will go after whatever elements that are children of the XPath expression '/root/authors'. And it will retrieve elements and convert them to a rectangular shape.

• When the parameter is 1 (modify it manually to 1), i.e. attribute-centric, it will go after whatever attributes that are children of the XPath expression '/root/authors'. And it will retrieve NULLs because there are no attributes and convert to a rectangular shape.

SQL:

USE pubs;
GO
DECLARE @i INT
	,@xml XML = N'
<root><authors>
    <au_id>172-32-1176</au_id>
    <au_lname>White</au_lname>
    <au_fname>Johnson</au_fname>
    <phone>408 496-7223</phone>
    <address>10932 Bigge Rd.</address>
    <city>Menlo Park</city>
    <state>CA</state>
    <zip>94025</zip>
    <contract>1</contract>
</authors>
<authors>
    <au_id>213-46-8915</au_id>
    <au_lname>Green</au_lname>
    <au_fname>Marjorie</au_fname>
    <phone>415 986-7020</phone>
    <address>309 63rd St. #411</address>
    <city>Oakland</city>
    <state>CA</state>
    <zip>94618</zip>
    <contract>1</contract>
</authors>
<authors>
    <au_id>238-95-7766</au_id>
    <au_lname>Carson</au_lname>
    <au_fname>Cheryl</au_fname>
    <phone>415 548-7723</phone>
    <address>589 Darwin Ln.</address>
    <city>Berkeley</city>
    <state>CA</state>
    <zip>94705</zip>
    <contract>1</contract>
</authors></root>'

EXEC sp_xml_preparedocument @i OUTPUT, @xml

SELECT * 
FROM OPENXML(@i, '/root/authors', 2) 
        WITH authors; -- mapped to a table 'dbo.authors' in the PUBS database

EXEC sp_xml_removedocument @i

Answer 4

Thanks for your answer.

why they use 2

OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)

there are 4 element in xpath but they mention 2. this is not clear

in openxml() what last argument means where we write 1 or 2 etc?

please discuss sir. thanks

Answer 5

Hi Sudip_inn,

The last parameter has nothing to do with the "depth" of the '/ROOT/Customer/Order/OrderDetail' XPath expression. 

Just run the code example that I provided. Once with 1, and once with 2.

And try to understand my previous explanation.