Microsoft KB Archive/316018

Knowledge Base

HOW TO: Update SQL Server Data by Using XML Updategrams

Article ID: 316018

Article Last Modified on 12/31/2003

---

APPLIES TO

• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 2000 64-bit Edition

---

This article was previously published under Q316018

IN THIS TASK

SUMMARY

• Requirements
• Enable a Database for XML Updategram Templates
• Create the XML Updategram Template
• Run the XML Updategram Template
• Verify That the XML Updategram Template Works
• Other Techniques

SUMMARY

This step-by-step article describes how to update SQL Server data by using XML updategrams.

An updategram is a data structure that you can use to express a change in the data. INSERT, UPDATE, and DELETE commands are represented in an updategram by the difference of the image of the data before and the image of the data after a change.

One way to run an XML updategram is to save the updategram as a file, and then run the file in the URL as a template file.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Windows NT 4.0 Server, Microsoft Windows 2000 Server, or other Microsoft operating systems that can host Microsoft SQL Server 2000.
• SQL Server 2000 with Web Release 1.
• Microsoft Internet Information Services.

This article assumes that you are familiar with the following topics:

• Extensible Markup Language (XML)

back to the top

Enable a Database for XML Updategram Templates

To enable a database for XML updategram templates:

1. Create the two folders C:\MyUpdateGram and C:\MyUpdateGram\Template.
2. Create a new SQL Server database, and then give the test database the name MyDatabase.

3. Create the States table in the MyDatabase database by running the following SQL Statement in SQL Query Analyzer:

   USE MyDatabase
   GO
   CREATE TABLE States (Id INT IDENTITY(1,1), Name NVARCHAR(20))
   GO
   INSERT INTO States (Name) SELECT 'Alabama'
   INSERT INTO States (Name) SELECT 'AK'
   INSERT INTO States (Name) SELECT 'Arizona'
   INSERT INTO States (Name) SELECT 'AR'
   GO
                       

4. Run IIS Virtual Directory Management for SQL Server: Click Start, point to Programs, point to Microsoft SQL Server, and then click Configure SQL XML Support in IIS.
5. In the tree view, expand the node that displays the name of the Web server.
6. Right-click Default Web Site, click New, and then click Virtual Directory.
7. On the General tab of the New Virtual Directory Property dialog box, set the Virtual Directory Name to myupdategram, and then set Local Path to C:\MyUpdateGram.
8. On the Security tab of the New Virtual Directory Property dialog box, type the valid connection credentials for the Internet user who will run the updategram.
9. On the Data Source tab of the New Virtual Directory Property dialog box, set SQL Server to the name of the SQL Server, and then set Database to MyDatabase.
10. On the Settings tab of the New Virtual Directory Property dialog box, click to select Allow template queries.
11. On the Virtual Names tab of the New Virtual Directory Property dialog box, click New to add a new virtual name.
12. On the Virtual Names Configuration dialog box, set Virtual Name to template, set Type to template, and then set Path to C:\MyUpdateGram\Template.
13. Click Save to save the template virtual name.
14. Click OK to save the myupdategram virtual directory.

back to the top

Create the XML Updategram Template

Paste the following XML updategram in Notepad, and then save the file as C:\MyUpdateGram\Template\update.xml:

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync>
      <updg:before>
         <States updg:id="1" Name="AK"/>
         <States updg:id="2" Name="AR"/>
      </updg:before>
      <updg:after>
         <States updg:id="1" Name="Alaska"/>
         <States updg:id="2" Name="Arkansas"/>
      </updg:after>
   </updg:sync>
</ROOT>
                

back to the top

Run the XML Updategram Template

In Microsoft Internet Explorer, type the following URL:

http://localhost/myupdategram/template/update.xml

You may have to substitute "localhost" with the name of your Web server.

back to the top

Verify That the XML Updategram Template Works

In SQL Query Analyzer, switch to the MyDatabase database and then run the query:

SELECT * FROM States
                

The XML updategram replaces the abbreviations AK and AR with the full state names for Alaska and Arkansas.

back to the top

Other Technique

You can use the following additional methods to implement XML updategrams:

• You can configure the virtual directory to allow posted updategrams. When you do so, you can post XML updategrams by using Hypertext Transfer Protocol (HTTP).
  

-or-

• You can submit the updategram by using an ActiveX Data Objects (ADO) or OLE DB command.

back to the top

Keywords: kbhowtomaster KB316018

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.