SQL Stored Procedure Parsing xml

The first type of explanation:

I have read such an article as follows
In SQL Server2005, Microsoft continued with a feature in 2000 (that is, data that supports XML types) and enhanced support for XML data columns, XML variables, and XML indexes.
Storing XML data in a database is an excellent feature. For most data processing requirements, it is important to format the XML data as other associated data. This is why the OPENXML function is introduced. OPENXML is a SQL Server-provided function that receives XML data and provides a rowset view of the XML data in memory.
A OPENXML function use case
In the following example, we will show how to use the OPENXML function. Suppose we are now engaged in an online shopping system development. In this scenario, the customer chooses to sign in to the website to purchase a variety of products. Because according to the company's business model, shopping through the website can save cost. We assume that most customers buy seven or more items. Our goal is to minimize the database calls when customers visit the site frequently. Our idea is to store the purchase requirements submitted on the Web page as XML documents, and send the XML data in the form of a string to the underlying program of the database. This allows us to insert order information from the XML data into the database in a single database call.
How do we solve the problem above? First we look at the page data that will be edited. A simple XML document contains a lot of core information from the site, including: Product name, price, date, and customer profile. List A contains a simple XML data stream.

<ShoppingCart>   <PurchaseProductID= "7" Price= "10.00"saledate= "10/11/2006"Salebatchid= "4523"CustomerID= "2398"/>   <PurchaseProductID= " the" Price= "25.00"saledate= "10/11/2006"Salebatchid= "4523"CustomerID= "2398"/>  <PurchaseProductID= "+" Price= "12.00"saledate= "10/11/2006"Salebatchid= "4523"CustomerID= "2398"/>   <PurchaseProductID= "One" Price= "90.00"saledate= "10/11/2006"Salebatchid= "4523"CustomerID= "2398"/>   <PurchaseProductID= "7" Price= "50.00"saledate= "10/11/2006"Salebatchid= "4523"CustomerID= "2398"/>   <PurchaseProductID= "8" Price= "67.35"saledate= "10/11/2006"Salebatchid= "4523"CustomerID= "2398"/>   <PurchaseProductID= "$" Price= "29.99"saledate= "10/11/2006"Salebatchid= "4523"CustomerID= "2398"/>   <PurchaseProductID= "Si" Price= "49.49"saledate= "10/11/2006"Salebatchid= "4523"CustomerID= "2398"/>   </ShoppingCart> 

List A

Then, we need to design a web interface to provide a call to the store program. The XML data stream is stored to the database by calling this stored program. List B is the implementation of this stored program.

CREATE   PROCEDUREUsp_insertshoppingcartorder (@xmlXML) as   BEGIN               DECLARE   @Pointer   INT               EXECUTEsp_xml_preparedocument@PointerOUTPUT,@xml               INSERT    intowebsales (ProductID, Saleprice, Saledate, Salebatchid, CustomerID)SELECTProductID, Price, saledate, Salebatchid, CustomerID fromOPENXML (@Pointer,'/shoppingcart/purchase')                with(ProductIDINT, Price Money, Saledatesmalldatetime, SalebatchidINT, CustomerIDINT               )               EXECSp_xml_removedocument@Pointer   END  

List B

This stored program supports XML data types (new features in SQL Server 2005) as input parameters (we can also use variable character data types as our input parameters, such as varchar (max) or to define varchar data types).
Next, we call the System program sp_xml_preparedocument, which not only creates an in-memory representation of the XML document, but also allows the XML document to be invoked as a parameter. Once we have a handle to the XML document in memory, we can call the OPENXML function. Using different parameters in a function call, you can also control the return result set of the XML data in detail.
We provide a handle to the XML document and return the nodes in the desired XML data through an XPath query. The WITH clause in the OPENXML function allows you to specify the rowset format for returning data. Once the data is inserted into the Websales table, you can call the System program sp_xml_removedocument Delete the XML data in SQL Server server memory.

This simple example demonstrates the powerful function of reducing the number of database calls by using the OPENXML function. The OPENXML function provides great flexibility in that you can either insert all of the XML data into a SQL Server datasheet or split the XML file into a different SQL Server data table. With this technique, we can insert 8 records in a single program call, rather than 8 operations, as traditional techniques do.
Limitations of OPENXML
Be aware of the use of memory when calling the OPENXML function. The return value of the system program sp_xml_preparedocument is a handle to an in-memory XML document. So, you'd better not use OPENXML to load large XML documents, because that could cause the server's memory to overflow.
In a future article, I'll show you how to use some of the new properties that SQL Server 2005 provides for XML, which not only implements the functionality of the OPENXML function, but also does not need to take into account the memory manipulation problems that arise when OPENXML calls.
About the author
Mr. Tim Chapman, a database administrator at a bank in Louisville, KY, has over 7 years of it work experience. He also passed Microsoft's SQL Server 2000 and SQL Server 2005 certifications.

The second type of explanation:
Creating several stored procedures with different parameters to accomplish the same task is always a burden to us. Passing parameters to your stored procedure by using an XML string can simplify this task, making designing COM components easier.
The way to do this is to pass in your arguments as an XML string and parse the XML to get the data you need, and finally continue to do what you need to do. Not only can you get parameters from XML, you can also run query statements on DOM documents created by XML to complete several stored procedure calls. I will give some examples of how to do this, and each example has a simple explanation.

In this example, I will pass some parameters to update the Name field of the Customer table. The XML is parsed to obtain the CustomerID (identity column) and the new name. The XML string I passed to the procedure is as follows:

<Root>    <Customer>        <CustomerID>3</CustomerID>        <name>Acme INC.</name>    </Customer></Root>

The stored procedures created are as follows:

CREATE PROCEDUREUpdate_customer (@xmldata varchar(8000)) as DECLARE @customerid int DECLARE @customername varchar( -) DECLARE @xmldata_id int EXECsp_xml_preparedocument@xmldata_idOUTPUT,@xmldata,"' SELECT @customerid =CustomerID@customername = [name]  fromOPENXML (@xmldata_id,'//customer',2) with(CustomerIDint,[name] varchar( -)) EXECSp_xml_removedocument@xmldata_id UPDATECustomerSETCustomer.[name] = ISNULL(@customername, Customer.[name]) WHERECustomer.tblid= @customerid 

This process first declares the variables that we use to store the relevant information. After that, the DOM document is opened, calling the procedure sp_xml_preparedocument, whose first argument returns a "handle".

The second parameter of this procedure call is the XML source of the new DOM document. The "handle" returned by the first parameter is used in the OPENXML call to query the DOM document. The second parameter of the OPENXML call is an extension path that maps to the parent node that contains the data to extract.
The third parameter (2) indicates the use of an element-centric mapping. The WITH clause provides the rowset format for the parsed data, and then the sp_xml_removedocument call deletes the DOM document resource.

In another example below, I will delete a few rows of data based on the set of customer IDs that are passed in. The XML string used is as follows:

<Root>    <Customer>        <CustomerID>1</CustomerID>    </Customer>    <Customer>        <CustomerID>2</CustomerID>    </Customer>    <Customer>        <CustomerID>3</CustomerID>    </Customer> </Root> 

The stored procedures used are as follows:

 exec  sp_xml_preparedocument  @xml_id  OUTPUT,  @xmldata ,  " delete  from  Customer where  Customer.tblid in  (select  CustomerID " Span style= "color: #0000ff;" >from   OPENXML (  @xmldata_id ," Span style= "color: #ff0000;" > '  //customer   ' , 

The use of this stored procedure avoids the creation of a long SQL query string to pass through ADO, or multiple calls to the same stored procedure. It also avoids network congestion caused by multiple calls.

As you can see, using Microsoft SQL Server 2000 makes some things easier. But remember that when you use XML in SQL Server 2000, the XML passed in as a parameter cannot exceed 8,000 characters. As is often said, do not overestimate the benefits of a good thing.
Refer to the MSDN Library for more information on OPENXML, sp_xml_preparedocument and sp_xml_removedocument.

SQL Stored Procedure Parsing xml