Sample HTML Form to Insert Records Using OPENXML

XML and Internet Support

Sample HTML Form to Insert Records Using OPENXML

This sample HTML form prompts a user to enter an employee ID, first name, and last name. After the user has entered the data in the form, an XML document containing the employee element to be inserted in the database is created. The XML document is passed as a parameter to the template.

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

This example shows:

How to create a simple HTML form.
How to create an XML document from the data entered in the HTML form.
How to pass the XML document to the template.
How to use the OPENXML clause in an INSERT statement to add the record in the database.

The template executes a stored procedure. 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 INSERT statement to insert the employee record in the Employee table. The record to be inserted is provided by OPENXML, which creates a rowset view of the XML document.

These are the steps to create a working sample:

Create this table:

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

Create these stored procedure in the database:

CREATE PROC sp_insert_employee @empdata ntext 
AS 
       DECLARE @hDoc int 
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @empdata 
    INSERT INTO Employee 
      SELECT * 
      FROM OPENXML(@hDoc, '/Employee') 
            WITH Employee 
     EXEC sp_xml_removedocument @hDoc

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 MyTemplate.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_insert_employee @empdata
</sql:query>
</root>
```

This is the HTML form:

<html>
<body>
  <form action="http://IISServer/nwind/template
            /MyTemplate.xml" method="post">
    <input type="hidden" id="e" name="empdata">
    <input type="hidden" name="contenttype" value="text/xml">
       EmployeeID: <input type=text id=eid value="1"><br>
       First Name: <input type=text id=fname value="Harry"><br>
       Last Name: <input type=text id=lname value="Smith"><br>
     <input type=submit onclick="Insert_Employee(e, eid,
       lname, fname)" value="Insert Employee"><br><br>

<script>
    function Insert_Employee(e, eid, lname, fname)
     {
      e.value = '<Employee eid="' + eid.value +
      '" lname="' + lname.value + '" fname="' +
      fname.value + '"/>';
     }
</script>
</form>
</body>
</html>

previous page start next page