XMLUpdateGram()

XMLUpdateGram(), available starting in VFP 7, is a way to capture changes to the data in a set of tables in an XML format. SQL Server can use XML updategrams to update data, eliminating the need for tedious coding techniques to update SQL Server databases.

Usage

cUpdateGram = XMLUpdateGram( [ cTableList [, nFlags ] ] )

Parameter	Value	Meaning
cTableList	Character	A list of tables and cursors to include in the updategram.
cTableList	Omitted	Uses all open tables and cursors in the current work session that have buffered changes.
nFlags Note: All flags are additive.	0 or omitted	Unicode (UTF-8) formatted XML.
	1	Generate unformatted XML, meaning that there are no extra tabs or carriage returns (no pretty-printing).
	2	Generate an opening and closing tag when the element is empty. If specified, an empty element reads: <region></region>; if omitted, only a <region/> tag is generated.
	4	Preserves white space in fields. If not specified, it trims trailing spaces.
	8	Wraps memo fields in CDATA sections. CDATA sections are useful for memo fields that contain characters used in the markup language, such as <, >, &, and ". A CDATA session starts with "<![CDATA[" and ends with "]]>". All characters are interpreted as character data instead of markup commands.
Bits 4 and 5 are combined to set output encoding. Select only one value between 16 and 48.	16	Sets the output encoding attribute to the code page of the cursor. (Default)
	32	Uses Unicode (UTF-8) output encoding; no character translation occurs and no "encoding=" attribute is contained in the XML declaration.
	48	Uses Unicode (UTF-8) output encoding; double-byte characters are translated to Unicode, and no "encoding=" attribute is contained in the XML declaration.
cUpdateGram	Character	The resulting XML updategram string. Use STRTOFILE() to save the contents of this variable to a file.

First, some ground rules. The cursor(s) on which you're tracking changes must be buffered, and MULTILOCKS must be set ON for XMLUpdateGram() to work. You will probably want to use the View Designer or CURSORSETPROP() to set the key field list, which limits the fields in the before and after representations to the key fields and only those fields that changed. If you don't specify key fields, all fields are listed in the before and after representations, and you'll end up with a huge XML file. Warning: If you have memo or general fields containing characters used in the markup language (<, >, &, and "), you could generate errors unless you include nFlags 8.So, what does an XML updategram look like? Here's the output from the example code:

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync>
    <updg:before>
      <customer>
        <cust_id>ALFKI</cust_id>
        <contact>Maria Anders</contact>
      </customer>
    </updg:before>
    <updg:after>
      <customer>
        <cust_id>ALFKI</cust_id>
        <contact>none</contact>
      </customer>
    </updg:after>
  </updg:sync>
</root>

The first line is the declaration line, which defines some specifics about the file. Then comes the root element, required in XML; it defines this as an updategram. Then come <updg:sync> elements, each of which is a transaction that can be committed, or is rolled back in the event of a failure of any one of the elements contained. Each sync element contains at least one pair of <updg:before> and <updg:after> elements, which corresponds to an insert, update, or delete operation. Within each before and after element is an element corresponding to each table to be updated. It's conveniently named the same as the table to update. And within these table elements are field elements, also conveniently named identically to the fields they reference. In the before element, these fields must identify one and only one record to update. If you've used CURSORSETPROP() to specify a key fields list, the key fields as well as the original values for those that fields that changed are listed. If you did not specify a key fields list, all fields in the table are listed. The after element contains the same fields, with the changed values. An element with an empty before element represents an insert operation, while an empty after element represents a delete operation.Note that this function returns a character string. You must save the character string to a file (STRTOFILE() is a good function to use), which is the most common way to use updategrams.Visual FoxPro can write updategrams, but it has no native capacity to read them. You're entirely on your own if you want to incorporate this into an application that uses only FoxPro tables. This is specifically designed to update SQL Server tables. For example, your application may use SQLEXEC() to execute a SELECT statement or stored procedure, which puts SQL Server data into a FoxPro cursor. Your application allows the user to edit any or all of the records in the cursor (after setting MULTILOCKS and buffering on, and specifying a key fields list), then writes out the updategram when the user is finished editing. Then, in SQL Server, execute the updategram file like any other XML query template file.Note that SQL Server 2000 supports updategrams only after you have installed the XML for SQL Web Release, which can be freely downloaded from msdn.microsoft.com.

Example

LOCAL cUpdateGram 
USE _SAMPLES + "Data\customer.dbf"
* Turn on MULTILOCKS and buffering.
SET MULTILOCKS ON
CURSORSETPROP("Buffering",5,"customer")
CURSORSETPROP("KeyFieldList","cust_id","customer") 
* Update some data.
REPLACE Customer.Contact WITH "none"
* Create updategram.
lcUpdateGram = XMLUPDATEGRAM("customer")
STRTOFILE(lcUpdateGram, "NewUpdateGram.XML")
* Restore the record to its original form.
TABLEREVERT()