Sample HTML Form to Update Records Using OPENXML

This sample application shows how data in an XML documents can be used to update records in a database table. The application shows the process of:

Executing a template from an HTML form.
Passing an XML document as a parameter to the template.
Executing SQL statements (stored procedures) in a template.
Using the OPENXML rowset provider with UPDATE to apply the updates.

The application assumes that the client has an XML document that is created using another application. The application uses OPENXML to shred the XML document and creates the rowset that is passed to UPDATE statement.

Before executing this example, you must create a virtual root. For more information, see Creating the nwind Virtual Directory.

The template executes a stored procedure (sp_update_employee). The XML document is passed to the stored procedure as a text parameter.

The stored procedure:

Calls sp_xml_preparedocument to create an internal representation of the XML document passed as a text parameter.
Calls the UPDATE statement to update the employee records in the Employee table. OPENXML provides the rowset view of the XML, which is used in the UPDATE statement.

These are the steps to create a working sample:

Create this table:

CREATE TABLE Employee(eid int, fname varchar(20), lname varchar(20))

Add sample data:

INSERT INTO Employee VALUES (1, 'Nancy', 'Davolio')
INSERT INTO Employee VALUES (2, 'Andrew', 'Fuller')

Create this stored procedure in the database:

CREATE PROC sp_update_employee @empdata ntext     
AS     
DECLARE @hDoc int       
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata  
UPDATE Employee     
SET     
      Employee.fname = XMLEmployee.fname,    
      Employee.lname = XMLEmployee.lname      
FROM OPENXML(@hDoc, '/root/Employee')   
             WITH Employee XMLEmployee    
WHERE  Employee.eid = XMLEmployee.eid    
EXEC sp_xml_removedocument @hDoc    
SELECT   *    
from      Employee 
FOR XML AUTO

Create the following template. The template must be stored in the directory associated with the virtual name of template type (if you have created the sample nwind virtual directory, the template is stored in the template subdirectory of the virtual root directory). Save the template as UpdateEmployee.xml.
```
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:header>
        <sql:param name="empdata"><Employee/></sql:param>
    </sql:header>
    <sql:query>
         exec sp_update_employee @empdata
    </sql:query>
</root>
```

This is the HTML form:

<html>
<body>
<form name="Employee" 
      action="http://localhost/nwind/Template/UpdateEmployee.XML" 
      method="POST">
This app assumes that client has this simple
XML document created using some other application and you want to
update the tables based on the data in this document.<br>
---------------------------------------<br>
<input type=hidden name="contenttype" value="text/xml">
<textarea name="empdata" cols=50 rows=5>
   &lt;root&gt;
     &lt;Employee eid=&quot;1&quot; lname=&quot;Leverling&quot; fname=&quot;Janet&quot;/&gt;
     &lt;Employee eid=&quot;2&quot; lname=&quot;Peacock&quot; fname=&quot;Margaret&quot;/&gt;
   &lt;/root&gt;
</textarea>
<br><input type=Submit value="Submit">
</form>
</body>
</html>

Sample HTML Form to Update Records Using OPENXML

See Also