Binaryworld - Update SQL Server records via HTTP ... [ SQL Server -> SQL-XML ]

Thanks to the SQL Server extensions for the Web and XML, it is now possible to query a SQL Server database (and get the result as XML) as well as insert, update, and delete records in a database. In this tip I'll show a few examples of this technique. Here's a first example that shows how you can send an SQL command to delete a record:

Click here to copy the following block

http://srv/app?delete%20from%20employees%20where%20employeeId=1

Needless to say, this technique makes your database prone to all sort of malicious attacks. You can limit the risk by using command templates that you have configured. These templates work a bit like stored procedures that embed the data manipulation commands. Here is a template that deletes one record from the Employees table.

Click here to copy the following block

<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header >
  <sql:param name="employeeId">0</sql:param>
</sql:header>
<sql:query >
  delete from employees where employeeId=@employeeId
</sql:query>
</root>

Notice that the template can take arguments, exactly as a stored procedure does. In the above example, the only parameter is employeeId and is declared in the sql:header section. If this field is omitted when the template is used, its default value is zero. The sql:query section contains the actual SQL command and uses the argument, which appears here as @employeeId. Assuming that you've save the template in a file named DeleteEmployee.xml, here's how you can invoke the template via HTTP:

Click here to copy the following block

http://srv/app/template/DeleteEmployee.xml?employeeId=101