Pass XML Instead of Arrays To SQL Server Procedure

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
In an article here on NullSkull.com Pass Arrays To SQL Server ( http://www.nullskull.com/articles/20001002.asp ), I discussed passing a delimited string of values to a SQL Server stored procedure.  Of course the procedure would then parse the string and insert records into various tables in the ecommerce order example.  If you are familiar with basic XML, SQL Server now offers a much cleaner way of parsing the data into a format that is easy to work with.  The Microsoft XML Parser (MSXML) is used by SQL Server to work with XML.
SQL Server is capable of using varchar, nvarchar, nchar, char, text, and ntext as the source for the XmlDocument.  However, since ntext and text data types cannot be local variables, they are much more difficult to work with.  If you absolutely need this functionality, pick up the book entitled The Guru's Guide To SQL Server, Stored Procedures, XML, and HTML.  It provides a pretty powerful custom procedure for breaking up ntext values into chunks of nvarchar values.
Today, we'll just work with a small XML string.  In your procedure, this will most likely be a nvarchar or varchar input parameter.  For simplicity sake, we'll just hard code the XML.  You can stick this example in SQL Server Query Analyzer and run it to view the results:


Sample Code
  
 /* 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

Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.