/* Initialize a handle for the XmlDocument */ 

declare @XmlHandle int 

/* 

     Create a table variable to store the extract XmlData into Relational 
     Database Format.    Unlike temporary tables, Table variables are 
     automatically removed from memory by SQL Server when the procedure 
     has completed.    So, there is no need to try and drop them at the 
     end of the procedure.   
     
  */ 

  declare @MyTable TABLE 
         ( 
               OrderID int, 
               ProdID int, 
               Qty int 
         ) 
   
/* 

    Create the XmlDocument using the handle above and the Xml 
     string as parameters.    If your stored procedure has an varchar input 
     parameter named @myxml, it would look like this instead: 

     EXEC sp_xml_preparedocument @XmlHandle output,@myxml 

*/ 

  EXEC sp_xml_preparedocument @XmlHandle output, 
'<cart> 
    <order id="10"> 
        <prod id="1" qty="1"/> 
        <prod id="2" qty="3"/> 
  </order> 
<order id="20"> 
        <prod id="3" qty="1"/> 
        <prod id="4" qty="3"/> 
  </order> 
<order id="30"> 
        <prod id="5" qty="1"/> 
        <prod id="6" qty="3"/> 
  </order> 
</cart>' 

/* 
     
     Use the OPENXML method to query the XmlDocument starting at 
     /cart/order/prod node and work it's way back up the node tree.   

     The first argument of the WITH option takes the order tag's id 
     attribute value and places it in the SELECT clause's OrderID column 
     by using XML's familiar ../ to go up one node from the current node.   
     The second and third arguments work directly with the Prod node's 
     id and qty attributes and places them in the corresponding SELECT clause 
     column name.     
     
  */ 

insert into @MyTable 
SELECT OrderID,ProdID,Qty 
FROM  OPENXML (@XmlHandle, '/cart/order/prod',1) 
            WITH (OrderID int '../@id', 
                      ProdID int '@id', 
                      Qty int '@qty') 
                 
/* 

   Insert the records into the table variable turning the XML structured 
   data into relational data.    We are now free to query the table variable 
   just as if it were a regular table for use with data manipulation, cursors, etc...     
   It could also be used for generated reports and counts in ways that might 
   be simpler to code in SQL Server vs XSL.   

   For now, we'll just query and display the results.   
   
*/ 

select * from @MyTable 

/* 

  Query Results: 

OrderID  ProdID     Qty 

  10 1 1 
  10 2 3 
  20 3 1 
  20 4 3 
  30 5 1 
  30 6 3 

*/ 

/* 
     It should be noted that we are not required to put the Xml structured 
     data into a table variable.    We could load it into a real table or work with 
     the values directly like this: 

       declare @orderid int 

        SELECT @orderid=OrderID   
        FROM  OPENXML (@XmlHandle, '/cart/order/prod',1) 
        WITH (OrderID int '../@id') 
                     
         print cast(@Orderid as varchar(50)) 
*/ 

/* 
         The following shows how to take an XmlDocument and use it to update 
         a table.   This is particularly useful for shopping cart type apps where 
         you want to update multiple records based on the contents of the 
         XmlDocument. 

*/ 

/* 
         Let's change all the quantities in our test table to say 100 for test 
         purposes and display our results. 

*/ 

update @MyTable set Qty = 100 

select * from @MyTable 

/* 

        Now, let's use the XmlDocument to update our table back to the original 
        values in our XmlDocument and display our results.   The OPENXML 
        function is used similar to our queries above.   Notice that in this particular 
        example, we've adjusted the flag argument to 3 instead of 1.   This allows 
        us to map XmlDocument nodes and their attributes where needed.   
        Similar to our queries above, the WITH clause maps the node or 
        node/attribute combination to a column name reference for use with 
        the SET clause and WHERE clause. 

        Naturally, you could update a regular table besides just the TABLE 
        variable used here. 

*/ 

UPDATE @MyTable 
          SET ProdID = XmlProdID, 
              Qty = XmlQty 
      FROM OPENXML (@XmlHandle, '/cart/order/prod',3) 
      WITH (XmlOrderID int '../@id', 
            XmlProdID int '@id', 
            XmlQty int '@qty') 
    WHERE OrderID = XmlOrderID 
      and ProdID = XmlProdID 
     
select * from @MyTable 



/* Remove the document from memory */ 

  EXEC sp_xml_removedocument @XmlHandle