<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="13. XML Support" />
<meta name="dc.subject" content="13. XML Support" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="webandxml.html" title="Chapter Contents" />
<link rel="prev" href="queryingxmldata.html" title="Querying Stored XML Data" />
<link rel="next" href="xmltemplates.html" title="XML Templates" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>13. XML Support</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="updategrams" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>13. XML Support</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="webandxml.html">Chapter Contents</a> | <a class="link" href="queryingxmldata.html" title="Querying Stored XML Data">Prev</a> | <a class="link" href="xmltemplates.html" title="XML Templates">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="webandxml.html">XML Support</a>
</div>
<br />
<div>
<a href="forxmlforsql.html">Rendering SQL Queries as XML (FOR XML Clause)</a>
</div>
<div>
<a href="composingxmlinsql.html">XML Composing Functions in SQL Statements (SQLX)</a>
</div>
<div>
<a href="xmlservices.html">Virtuoso XML Services</a>
</div>
<div>
<a href="queryingxmldata.html">Querying Stored XML Data</a>
</div>
<div class="selected">
<a href="updategrams.html">Using UpdateGrams to Modify Data</a>
<div>
<a href="#updategrambasics" title="Updategrams Basics">Updategrams Basics</a>
<a href="#elementsdesc" title="Elements Description">Elements Description</a>
<a href="#determiningactions" title="Determining Actions">Determining Actions</a>
<a href="#usinginparams" title="Using Input Parameters">Using Input Parameters</a>
<a href="#examples" title="Examples">Examples</a>
</div>
</div>
<div>
<a href="xmltemplates.html">XML Templates</a>
</div>
<div>
<a href="xmlschema.html">XML DTD and XML Schemas</a>
</div>
<div>
<a href="xq.html">XQuery 1.0 Support</a>
</div>
<div>
<a href="xslttrans.html">XSLT Transformation</a>
</div>
<div>
<a href="xmltype.html">XMLType</a>
</div>
<div>
<a href="xmldom.html">Changing XML entities in DOM style</a>
</div>
<br />
</div>
<div id="text">
<a name="updategrams" />
<h2>13.5. Using UpdateGrams to Modify Data</h2>
<p>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.</p>
<p>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.</p>
<p>The <span class="computeroutput">xmlsql_update()</span> function supports
XML-based insert, update, and delete operations performed on an
existing table in the database.</p>
<a href="fn_xmlsql_update.html">xmlsql_update()</a>
<a name="updategrambasics" />
<h3>13.5.1. Updategrams Basics</h3>
<p>
The general format of an updategram is:
</p>
<div>
<pre class="programlisting">
<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>
</pre>
</div>
<p>
or
</p>
<div>
<pre class="programlisting">
<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>
</pre>
</div>
<br />
<a name="elementsdesc" />
<h3>13.5.2. Elements Description</h3>
<p>
The <span class="computeroutput"><sync></span> tag of the updategram
signifies the beginning of an operation(s) The rows specified in the
<span class="computeroutput"><before></span> refer to existing records in the
database. The rows specified in the
<span class="computeroutput"><after></span> block refer to what the user
wants in the database. <span class="computeroutput"><TABLENAME.../></span>
identifies target table.
</p>
<p>
The <span class="computeroutput">sql:at-identity</span> attribute stores the last
identity value added by the system (if possible). This identity value
can then be used in subsequent operations.
</p>
<p>
The <span class="computeroutput">sql:id</span> attribute is used to mark rows. This
forces an association between the record specified in the
<span class="computeroutput"><before></span> and
<span class="computeroutput"><after></span> block in the update gram. When
there are multiple instances specified, it is recommended that
<span class="computeroutput">sql:id</span> attribute be used for all the instances.
</p>
<p>
Each <span class="computeroutput"><TABLENAME.../></span> refers to a single
table. Multiple <span class="computeroutput"><TABLENAME.../></span> entries
are allowed in the same <span class="computeroutput"><before></span> or
<span class="computeroutput"><after></span> tags, or in both
<span class="computeroutput"><before></span> and
<span class="computeroutput"><after></span> tags; however, nesting is not
allowed. The <span class="computeroutput"><before></span> and
<span class="computeroutput"><after></span> tags are optional. A missing tag
is the same as having a tag with no content.
</p>
<br />
<a name="determiningactions" />
<h3>13.5.3. Determining Actions</h3>
<p>
If only the <span class="computeroutput"><after></span> block is specified,
the rows specified in the <span class="computeroutput"><after></span> block
are inserted in the table(s). If both the
<span class="computeroutput"><before></span> and
<span class="computeroutput"><after></span> blocks are specified, then rows
specified in the <span class="computeroutput"><after></span> block for which
there are no corresponding rows in the
<span class="computeroutput"><before></span> block are inserted in the
table(s).
</p>
<p>
In an update operation, the rows specified in the
<span class="computeroutput"><before></span> block refer to existing rows in
the database. The corresponding rows in the
<span class="computeroutput"><after></span> block reflect what the user wants
in the database. A row update operation is performed if there is a row
in both the <span class="computeroutput"><before></span> and
<span class="computeroutput"><after></span> sections with the same set of
values for the attributes that uniquely identify a row in a
table. Rows specified in the <span class="computeroutput"><before></span>
block must be valid in the database for the updategram to successfully
update the rows.
</p>
<p>
In a delete operation, if only the
<span class="computeroutput"><before></span> block is specified in the update
gram, the rows specified in the <span class="computeroutput"><before></span>
block are deleted from the table(s). If both the
<span class="computeroutput"><before></span> and
<span class="computeroutput"><after></span> blocks are specified, the rows
for which there are no corresponding rows in the
<span class="computeroutput"><after></span> block are deleted from the
table(s).
</p>
<br />
<a name="usinginparams" />
<h3>13.5.4. Using Input Parameters</h3>
<p>
Parameters declarations should be described in the <header>
section of the updategram. There should be one
<span class="computeroutput"><param></span> row for each parameter.
</p>
<p>
General syntax:
</p>
<div>
<pre class="programlisting">
<sql:header xmlns:sql="xml-sql">
<sql:param name="PARAM_NAME" [default="DEFAULT_VALUE"]/>
...
</sql:header>
</pre>
</div>
<p>
Where <span class="computeroutput">PARAM_NAME</span> is the name of the parameter
and <span class="computeroutput">DEFAULT_VALUE</span> is optional default of
parameter Parameters in updategram should have
<span class="computeroutput">$PARAM_NAME</span> instead of a value. On processing,
Virtuoso replaces <span class="computeroutput">$PARAM_NAME</span> with the
corresponding value from the
<span class="computeroutput"><input_parameters></span> given to the function
<span class="computeroutput">xmlsql_update()</span>.
</p>
<br />
<a name="examples" />
<h3>13.5.5. Examples</h3>
<p>
Given the following tables:
</p>
<div>
<pre class="programlisting">
CREATE TABLE Orders (
OrderID int identity,
CustomerID varchar(10),
EmpID int,
PRIMARY KEY (OrderID));
CREATE TABLE OrderDetails (
OrderID int,
ProductID int,
Quantity int);
</pre>
</div>
<p>
A. Update Gram to Insert a Record
</p>
<div>
<pre class="programlisting">
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>')));
</pre>
</div>
<p>
B. Updategram with an <span class="computeroutput">at-identity</span> Attribute
</p>
<div>
<pre class="programlisting">
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>')));
</pre>
</div>
<p>
C. Updategram to Delete a Record
</p>
<div>
<pre class="programlisting">
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>')));
</pre>
</div>
<p>
D. Updategram to Update a Record
</p>
<div>
<pre class="programlisting">
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>')));
</pre>
</div>
<p>
E: Using a different syntax for updategrams - entities in place
of attributes - example D can be transformed to:
</p>
<div>
<pre class="programlisting">
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>')));
</pre>
</div>
<p>
Note that two syntaxes cannot be mixed in one document.
</p>
<p>
F: Using input parameters
</p>
<p>
Assume the following table:
</p>
<div>
<pre class="programlisting">
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
</pre>
</div>
<p>
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
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="queryingxmldata.html" title="Querying Stored XML Data">Previous</a>
<br />Querying Stored XML Data</td>
<td align="center" width="34%">
<a href="webandxml.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="xmltemplates.html" title="XML Templates">Next</a>
<br />XML Templates</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
908
