Using Simple Updategrams

We have seen a number of articles on how we can retrieve data from SQL Server 2000. But how we can send data to insert, update or delete have not been covered. And here we are look at one of the native features that get bundled with SQLXML 3.0, Updategrams. In this article we will explore how we can use updategrams with simple examples to illustrate us the core features.

Syntax

Updategram is an XML structure which defines each of the operations in a specific format. And the namespace you would need to use is urn:schemas-microsoft-com:xml-updategram. The basic template for updategram would look like:

<root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">

  <updg:sync [mapping-schema= "AnnotatedSchema.xml"] >

    <updg:before>

        ...

    </updg:before>

    <updg:after>

        ...

    </updg:after>

  </updg:sync>

</root>

Reviewing the above template we will look at each of the components:

<before> : Identifies the existing state of the record. Often referred to as "the before state".

<after> : Identifies the new state to which data is to be changed.

<sync> : Contains the <before> and <after> blocks. A <sync> block can contain more than one set of <before> and <after> blocks. This can be considered as a transactional boundary. Everything in the sync block commits or else gets aborted.

Inserting Data

Lets move forward and insert a simple data. Create an IIS SQLXML Virtual Directory. And copy paste the below XML as Demo1.xml.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">

<updg:sync >

  <updg:before>

  </updg:before>

  <updg:after>

      <DemoTable>

          <FirstName>Vinod</FirstName>

         <LastName>Kumar</LastName>

      </DemoTable>

  </updg:after>

</updg:sync>

</ROOT>

Assuming you have created a templates Virtual Directory in the SQLXML Virtaul directory management console when you run the URL ( http://IIServerName/Demo/Templates/Demo1.xml)  you can see that the query would insert a record with LastName as Kumar and FirstName as Vinod in the table Named DemoTable. The table script is:

Create table DemoTable (FirstName Varchar(30), LastName Varchar(30))

If you donot create the table then you would get an error saying the table is not present. Now the concept behind this working is that the state before the Insert was nothing and the state after the statement must be a name record. This means that we need to Insert the record. Now using the same concept we can have an Before state and then omit the after state denoting that we are interested in deleteing the particular records. And during update the concept is even more simple the after denotes the state you want after the save.

Conclusion

In this article we have seen a simple implementation of updategrams and how they can be incorporated in conjunction with SQLXML.