www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

XML Support

Rendering SQL Queries as XML (FOR XML Clause)
XML Composing Functions in SQL Statements (SQLX)
Virtuoso XML Services
Querying Stored XML Data
Using UpdateGrams to Modify Data
Updategrams Basics Elements Description Determining Actions Using Input Parameters Examples
XML Templates
XML DTD and XML Schemas
XQuery 1.0 Support
XSLT Transformation
XMLType
Changing XML entities in DOM style

14.5. Using UpdateGrams to Modify Data

Updategrams allow database updates to be defined as XML. This is ultimately achieved by mapping the XML nodes against corresponding database columns. Updategrams can be used to replace existing data access components in a middle tier. A typical application will include a middle tier consisting of business logic and data access code. The data access code will interact with the database using disconnected recordsets and command objects calling stored procedures. Most of the data access section of the middle tier can be replaced with updategrams.

Most data access tiers (both middle tier code and stored procedures) will deal individually with specific database tables or groups of related tables. This can inhibit performance and often several round trips to the database are required to complete a transaction. Updategrams solve this problem by including all the data in an XML document that is then mapped to database tables and columns. The entire database update can then be accomplished at once. This update can include inserting, updating and deleting data.

The xmlsql_update() function supports XML-based insert, update, and delete operations performed on an existing table in the database.

xmlsql_update()

14.5.1. Updategrams Basics

The general format of an updategram is:

<sql:sync xmlns:sql="xml-sql">
 <sql:before>
    <TABLENAME [sql:id="value"] col="value" col="value"?../>
 </sql:before>
 <sql:after>
    <TABLENAME [sql:id="value"] [sql:at-identity="value"]
      col="value" col="value"?../>
 </sql:after>
</sql:sync>

or

<sql:sync xmlns:sql="xml-sql">
        <sql:before>
                <TABLENAME [sql:id="value"]>
		   <col>"value"</col>
		   <col>"value"</col>
		   ...
		</TABLENAME>
		...
        </sql:before>
        <sql:after>
                <TABLENAME [sql:id="value"] [sql:at-identity="value"]>
		   <col>"value"</col>
		   <col>"value"</col>
		   ...
		</TABLENAME>
		...
        </sql:after>
</sql:sync>

14.5.2. Elements Description

The <sync> tag of the updategram signifies the beginning of an operation(s) The rows specified in the <before> refer to existing records in the database. The rows specified in the <after> block refer to what the user wants in the database. <TABLENAME.../> identifies target table.

The sql:at-identity attribute stores the last identity value added by the system (if possible). This identity value can then be used in subsequent operations.

The sql:id attribute is used to mark rows. This forces an association between the record specified in the <before> and <after> block in the update gram. When there are multiple instances specified, it is recommended that sql:id attribute be used for all the instances.

Each <TABLENAME.../> refers to a single table. Multiple <TABLENAME.../> entries are allowed in the same <before> or <after> tags, or in both <before> and <after> tags; however, nesting is not allowed. The <before> and <after> tags are optional. A missing tag is the same as having a tag with no content.


14.5.3. Determining Actions

If only the <after> block is specified, the rows specified in the <after> block are inserted in the table(s). If both the <before> and <after> blocks are specified, then rows specified in the <after> block for which there are no corresponding rows in the <before> block are inserted in the table(s).

In an update operation, the rows specified in the <before> block refer to existing rows in the database. The corresponding rows in the <after> block reflect what the user wants in the database. A row update operation is performed if there is a row in both the <before> and <after> sections with the same set of values for the attributes that uniquely identify a row in a table. Rows specified in the <before> block must be valid in the database for the updategram to successfully update the rows.

In a delete operation, if only the <before> block is specified in the update gram, the rows specified in the <before> block are deleted from the table(s). If both the <before> and <after> blocks are specified, the rows for which there are no corresponding rows in the <after> block are deleted from the table(s).


14.5.4. Using Input Parameters

Parameters declarations should be described in the <header> section of the updategram. There should be one <param> row for each parameter.

General syntax:

<sql:header xmlns:sql="xml-sql">
  <sql:param name="PARAM_NAME" [default="DEFAULT_VALUE"]/>
  ...
</sql:header>

Where PARAM_NAME is the name of the parameter and DEFAULT_VALUE is optional default of parameter Parameters in updategram should have $PARAM_NAME instead of a value. On processing, Virtuoso replaces $PARAM_NAME with the corresponding value from the <input_parameters> given to the function xmlsql_update().


14.5.5. Examples

Given the following tables:

CREATE TABLE Orders (
    OrderID int identity,
    CustomerID varchar(10),
    EmpID int,
    PRIMARY KEY (OrderID));

CREATE TABLE OrderDetails (
    OrderID int,
    ProductID int,
    Quantity int);

A. Update Gram to Insert a Record

xmlsql_update (xml_tree_doc (xml_tree (
'<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:sync>
  <sql:after>
    <Orders CustomerID="TEST" EmpID="99"/>
  </sql:after>
</sql:sync>
</ROOT>')));

B. Updategram with an at-identity Attribute

xmlsql_update (xml_tree_doc (xml_tree (
'<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:sync>
  <sql:after>
    <Orders sql:at-identity="x" CustomerID="VINET" EmpID="10"/>
      <OrderDetails OrderID="x" ProductID="1" Quantity="50"/>
      <OrderDetails OrderID="x" ProductID="2" Quantity="20"/>
    <Orders sql:at-identity="x" CustomerID="HANAR" EmpID="11"/>
      <OrderDetails OrderID="x" ProductID="1" Quantity="30"/>
      <OrderDetails OrderID="x" ProductID="4" Quantity="25"/>
  </sql:after>
</sql:sync>
</ROOT>')));

C. Updategram to Delete a Record

xmlsql_update (xml_tree_doc (xml_tree (
'<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:sync>
  <sql:before>
    <Orders CustomerID="HANAR" EmpID="11"/>
  </sql:before>
</sql:sync>
</ROOT>')));

D. Updategram to Update a Record

xmlsql_update (xml_tree_doc (xml_tree (
'<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:sync>
  <sql:before>
    <Orders sql:id="1" CustomerID="VINET" EmpID="10"/>
  </sql:before>
  <sql:after>
    <Orders sql:id="1" CustomerID="VINET_NEW" EmpID="11"/>
  </sql:after>
</sql:sync>
</ROOT>')));

E: Using a different syntax for updategrams - entities in place of attributes - example D can be transformed to:

xmlsql_update (xml_tree_doc (xml_tree (
'<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:sync>
  <sql:before>
    <Orders sql:id="1">
      <CustomerID>VINET</CustomerID>
      <EmpID>10</EmpID>
    </Orders>
  </sql:before>
  <sql:after>
    <Orders sql:id="1">
      <CustomerID>VINET_NEW</CustomerID>
      <EmpID>11</EmpID>
    </Orders>
  </sql:after>
</sql:sync>
</ROOT>')));

Note that two syntaxes cannot be mixed in one document.

F: Using input parameters

Assume the following table:

CREATE TABLE Shippers(
  ShipperID INTEGER,
  CompanyName VARCHAR(40),
  Phone VARCHAR(24),
  PRIMARY KEY (ShipperID));

xmlsql_update (xml_tree_doc (xml_tree (
'<DocumentElement xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:header>
      <sql:param name="ShipperID" default="2"/>
      <sql:param name="CompanyName" default="United Package New"/>
      <sql:param name="Phone" default="(503) 555-3199 (new)"/>
    </sql:header>
    <sql:sync>
        <sql:before>
        </sql:before>
        <sql:after>
            <Shippers sql:id="1" ShipperID="\$ShipperID"
	    CompanyName="\$CompanyName" Phone="\$Phone"/>
        </sql:after>
    </sql:sync>
</DocumentElement>')),
    vector ('ShipperID','10','CompanyName','DHL','Phone','+359 32 144'));
		-- <- this is a array with input parameters

This will add one record to the Shippers table with the data in the array. Note that the slash/dollar sign pair '\$' transforms to dollar sign '$' only