Shredding an XML document in SQL Server

I need some help in understanding how OPENXML works when shredding a XML document into a normal T-SQL rowset.
Please examine the query below and explain to me the meaning of the '../@Customerid' part of it.
This gives the results as they should be, but I notice that '@ProductID' does not have '../' before it.
I think that this has got to do with the path  '/ROOT/Customer/Order/OrderDetail'.
But if I insert '../' before '@ProductID' and '@Quantity' then those values appear as NULL, which does not make sense to me.

 DECLARE @DocHandle 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 @DocHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@DocHandle, '/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 @DocHandle
Philip van GassDatabase Developer/AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
because you are telling it to be at the orderdetail level

FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail',2)   thats the rowpattern....

The "../" is like referring back up levels and then down to the <order> level.  Think of the fullstops as a wildcard of sorts. So you request data from /root/customer (the '..') and then the / puts us at the <order> level to get @orderid, @customerid, @orderdate

And using @ is to do with flag value of 2 which is elements rather than attributes....
But because you are spelling out the elements (colpattern) using WITH then you are taking over control with your own schemadeclaration.

So far, you are not referring to the <customer> level at all.

It is a bit confusing to start with... I will try to use <easy to understand> explanations. So, ask away. But be patient :)
0
ste5anSenior DeveloperCommented:
E.g. like this:

DECLARE @XmlDocument XML = 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>';

SELECT C.Customer.value('@CustomerID', 'NVARCHAR(255)') AS CustomerID ,
       C.Customer.value('@ContactName', 'NVARCHAR(255)') AS ContactName ,
       O.[Order].value('@OrderID', 'INT') AS OrderID ,
       O.[Order].value('@OrderDate', 'DATE') AS OrderDate ,
       O.[Order].value('@CustomerID', 'NVARCHAR(255)') AS OrderCustomerID,
       O.[Order].value('@EmployeeID', 'INT') AS EmployeeID,
       OD.OrderDetail.value('@ProductID', 'INT') AS ProductID,
       OD.OrderDetail.value('@Quantity', 'INT') AS Quantity
FROM   @XmlDocument.nodes('/ROOT/Customer') C(Customer)
       CROSS APPLY C.Customer.nodes('Order') O([Order])
       CROSS APPLY O.[Order].nodes('OrderDetail') OD(OrderDetail);

Open in new window


But CustomerID seems to be redundant in Order. At least it can add confusion, cause it could be different than the outer CustomerID.
1
Mark WillsTopic AdvisorCommented:
@ste5an,

As always your XML skills are good....

Now, please explain what you are doing - Philip appears to be working through the example in https://docs.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql and you havent really addressed his concerns.

His question is not "show me how to shred xml" it is "I need some help in understanding how OPENXML works"...

Cheers,
Mark
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
The row pattern and col pattern in the schema declaration are XPath expressions.

The row pattern determines from which nodes the rows of the result set are constructed. These nodes are the current node (.) for the col pattern in the schema declaration.

.. selects the parent node, thus ../@attributeName retrieves the value of an attribute in the parent node.
CustomerID and ProductD are attributes of different elements, thus nodes on different axes (self and parent).


p.s. maybe an native speaker understands the subject, but I didn't read explain XPath.
0
Mark WillsTopic AdvisorCommented:
*laughing* no, it was explain OPENXML, read the opening line of the question :

I need some help in understanding how OPENXML works when shredding a XML document into a normal T-SQL rowset.
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Yes Mark you are correct. I was working through that example.
So it seems that if I specify the path  '/ROOT/Customer/Order/OrderDetail' then it puts me straight at the OrderDetail level hence the absence of
'../' before @ProductID and @Quantity.
And if I want the values from the Order level then I have to prefix with '../'.
But what if I want the values from the Customer level. What do I do then ?
0
ste5anSenior DeveloperCommented:
So it seems that if I specify the path  '/ROOT/Customer/Order/OrderDetail' then it puts me straight at the OrderDetail level [..]
Not exactly. It means that each row returned by OPENXML is a node having the path /ROOT/Customer/Order/OrderDetail as current node.

E.g. in

DECLARE @XmlDocument XML = N'
<ROOT>
    <Customer CustomerID="VINET" ContactName="Paul Henriot">
        <Order OrderID="10248">
            <OrderDetail ProductID="11" Quantity="12"/>
            <OrderDetail ProductID="42" Quantity="10"/>
        </Order>
    </Customer>
    <Vendor VendorID="LILAS" ContactName="Carlos Gonzlez">
        <Order OrderID="10283">
            <OrderDetail ProductID="72" Quantity="3"/>
        </Order>
    </Customer>
</ROOT>';

Open in new window


Here <OrderDetail ProductID="72" Quantity="3"/> would be "straight at the OrderDetail level", But this row would not be in the result set, cause the path is different (/ROOT/Vendor/Order/OrderDetail instead of /ROOT/Customer/Order/OrderDetail).

And if I want the values from the Order level then I have to prefix with '../'.
Not exactly. ../ means to start navigation at the parent node.

But what if I want the values from the Customer level. What do I do then ?
As Customer is the parent of Order, which is parent of the current node, it's simply ../../.

Caveat: axes traversals are slow operations. When loading much XML data, using the .nodes() and {CROSS|OUTER} APPLY can be much faster.
0
Mark WillsTopic AdvisorCommented:
You have to go back two levels to get <Customer> data e.g. to get contact name
DECLARE @DocHandle 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="LILAS2" 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 @DocHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail',2)  
      WITH (OrderID     int         '../@OrderID',  
      CustomerID  varchar(10) '../@CustomerID',  
      ContactName varchar(50) '../../@ContactName',
      OrderDate   datetime    '../@OrderDate',  
      ProdID      int         '@ProductID',  
      Qty         int         '@Quantity')  
EXEC sp_xml_removedocument @DocHandle

Open in new window

0
Mark WillsTopic AdvisorCommented:
Use your rowpath as a template, it shows you what level you are at, and to get access to parent levels, use "../"

here is our pattern :  /ROOT/Customer/Order/OrderDetail

things available at orderdetail level are Product and Quantity

go up a level  "../" and you are at /ROOT/Customer/Order and can get OrderDate and OderID  (etc)

go up another level "../../" and you are at /ROOT/Customer and can get ContactName etc

I know my explanation is probably making ste5an's head spin, but as long as you are understanding, then apply ste5an's more technically correct explanation, then you should be in a good place.
0
ste5anSenior DeveloperCommented:
Only upsetting the pedant in me ;)
0
Mark WillsTopic AdvisorCommented:
*laughing* be patient ste5an - you know me well enough by now....


>> So it seems that if I specify the path  '/ROOT/Customer/Order/OrderDetail' then it puts me straight at the OrderDetail level hence the absence of '../' before @ProductID and @Quantity.

Yes.  More accurately : It means that each row returned by OPENXML is a node having the path
 /ROOT/Customer/Order/OrderDetail as current node.

>> And if I want the values from the Order level then I have to prefix with '../'.

Yes. More Accurately :  ../ means to start navigation at the parent of the current node.

>> But what if I want the values from the Customer level. What do I do then ?

Use ../../    parent  of the parent of current node. rowpath gives us current node ie OrderDetail. Parent of OrderDetail is Order. Parent of Order is Customer

So, where I have used the word 'level' it is really 'node'.
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Actually it was Ste5an's solution that made my head spin, as my brain is not ready for assimilating CROSS APPLY being applied to OPENXML, when I am still trying to get to grips with the latter. Enjoying the friendly banter between the two of you though.
So now check this out. I removed the 'OrderDetail' level and tried to access the values at the 'Order' and 'Customer' levels and it did not like it.
Whatever value I give to Flags it always returns a NULL for all the columns.

 DECLARE @docHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order EmployeeID="5" > 
      <OrderID>10248</OrderID>  
      <CustomerID>VINET</CustomerID>  
      <OrderDate>1996-07-04T00:00:00</OrderDate>  
      <OrderDetail ProductID="11" Quantity="12"/>  
      <OrderDetail ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order  EmployeeID="3" > 
      <OrderID>10283</OrderID>  
      <CustomerID>LILAS</CustomerID>  
      <OrderDate>1996-08-16T00:00:00</OrderDate>  
      <OrderDetail ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>'  
-- Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer/Order',2)  
  WITH (CustomerID   varchar(10)   '../CustomerID',
        ContactName  varchar(20)   '../ContactName',
        OrderID  int  '@OrderID',
            OrderDate date   '@OrderDate');  
EXEC sp_xml_removedocument @docHandle
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Okay so I worked that problem out on my own by doing the following:

DECLARE @docHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order EmployeeID="5" > 
      <OrderID>10248</OrderID>  
      <CustomerID>VINET</CustomerID>  
      <OrderDate>1996-07-04T00:00:00</OrderDate>  
      <OrderDetail ProductID="11" Quantity="12"/>  
      <OrderDetail ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order  EmployeeID="3" > 
      <OrderID>10283</OrderID>  
      <CustomerID>LILAS</CustomerID>  
      <OrderDate>1996-08-16T00:00:00</OrderDate>  
      <OrderDetail ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>'  
-- Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer/Order',2)  
  WITH (CustomerID   varchar(10)   '../@CustomerID',
        ContactName  varchar(20)   '../@ContactName',
        OrderID  int  ,
            OrderDate date   );  
EXEC sp_xml_removedocument @docHandle
1
Mark WillsTopic AdvisorCommented:
It would be worthwhile working through these examples : https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-openxml

And seeing that you can now solve your own, I think our job is done :)


P.S. ste5an and I go way back, and always enjoy his posts :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Use in this case forward references (faster):

DECLARE @docHandle INT;
DECLARE @XmlDocument XML = N'
<ROOT>
    <Customer CustomerID="VINET" ContactName="Paul Henriot">
        <Order EmployeeID="5" >
            <OrderID>10248</OrderID>
            <CustomerID>VINET</CustomerID>
            <OrderDate>1996-07-04T00:00:00</OrderDate>
            <OrderDetail ProductID="11" Quantity="12"/>
            <OrderDetail ProductID="42" Quantity="10"/>
        </Order>
    </Customer>
    <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
        <Order EmployeeID="3" >
            <OrderID>10283</OrderID>
            <CustomerID>LILAS</CustomerID>
            <OrderDate>1996-08-16T00:00:00</OrderDate>
            <OrderDetail ProductID="72" Quantity="3"/>
        </Order>
    </Customer>
</ROOT>';

EXEC sys.sp_xml_preparedocument @docHandle OUTPUT ,
                                @XmlDocument;

SELECT *
FROM
       OPENXML(@docHandle, '/ROOT/Customer', 2)
           WITH ( CustomerID NVARCHAR(255) '@CustomerID' ,
                  ContactName NVARCHAR(255) '@ContactName' ,
                  OrderID INT 'Order/OrderID',
                  OrderDate DATE 'Order/OrderDate');

EXEC sys.sp_xml_removedocument @docHandle;

Open in new window


p.s. use the CODE button, this increases readability.
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Okay thanks Mark. Just give me some time to work through the examples in that link before I close this, in case I have any more questions.
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Nice to interact with both of you. Thanks for responding so promptly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.