On the front page of SqlJunkies today is an article about XML Templates in SqlXml.  In “An Introduction to XML Templates with Sql Server 2000”, Denise White gives a brief overview of what XML Templates are and how to access them over HTTP using our ISAPI extension.  There are some good examples in there, mostly using embedded FOR XML queries, and all are download-able from the site.

For anyone who doesn't know, or doesn't want to read the article quite yet, XML Templates are a great little technology for creating dynamic XML documents that have sections populated with data from Sql Server 2000.  Basically, you create a valid XML document however you like, and anywhere you want to have data dynamically filled in, you add a special element within our namespace.  This could be a “sql:query” element which supports the execution of FOR XML queries and stored procedures, or a “sql:xpath-query” element which indicates the contents are a XPath statement to be executed over a Annotated XSD (XML <-> Relational mapping) file.

There are a variety of ways to execute templates.  The article covers executing them through our ISAPI component.  However, they can also be executed within applications through ADO / SqlOleDb.  Support for templates is built into MDAC 2.7 and 2.8 within the provider.  We also ship a set of managed wrappers (SqlXmlCommand) for our native components so the functionality can be accessed within a .Net application.  Finally, in the 3.0 release of SqlXml, we added support for accessing these things over web services via our ISAPI component.

So why is this so useful?  Well, think about it a bit.  Normally if you wanted to do something like this, you'd have to first create the static XML document, walk to the first place to insert dynamic data, execute a query using a different component, fill in the pieces (possibly having to convert the data to XML), and then repeat for any other areas to be filled in.  Instead, we take that burden off of you, allowing you to create these templates that we fill in.  As mentioned in the article, templates can be parameterized so that the same template can be used repeatedly.

One area Denise did not mention was updates with Updategrams. Updategrams are our technology for updating the database via a diff of XML fragments.  Our processor interprets the diff as Inserts / Updates / Deletes against the database depending on elements appearing or not appearing in the before and after images.  Updategrams are in fact a part of templates and another tag (”updg:sync”) that can be included within a template.  So, in a single file, you could make an update to a Sql Server and then select data back out. 

For example. Consider the following template in which I take in several parameters, update the database, and then present the updated data by issuing a XPath against an Annotate schema:

<?xml-stylesheet type="text/xsl" href="updateContact.xsl"?>

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
 
xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <sql:header>
    <sql:param name="contactID"></sql:param>
    <sql:param name="name"></sql:param>
    <sql:param name="title"></sql:param>
    <sql:param name="email"></sql:param>
    <sql:param name="phone"></sql:param>
    <sql:param name="fax"></sql:param>
 
    <sql:param name="bname"></sql:param>
    <sql:param name="btitle"></sql:param>
    <sql:param name="bemail"></sql:param>
    <sql:param name="bphone"></sql:param>
    <sql:param name="bfax"></sql:param>
  </sql:header>

  <updg:sync mapping-schema='Contact.xsd'>
   
<updg:before>
      <Contact contactID="$contactID" name="$bname" title="$btitle" email="$bemail" phone="$bphone" fax="$bfax"/>
    </updg:before>
    <updg:after>
    <Contact contactID="$contactID" name="$name" title="$title" email="$email" phone="$phone" fax="$fax"/>
    </updg:after>
  </updg:sync>

  <UpdatedRecord>
    <sql:xpath-query mapping-schema='Contact.xsd'>
      /Contact[@contactID=$contactID]
    </sql:xpath-query>
  </UpdatedRecord>
</ROOT>

Here is the annotated XSD file I'm using with it:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="
http://www.w3.org/2001/XMLSchema"
  xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
  elementFormDefault="qualified"
  attributeFormDefault="unqualified">
 
 
<xs:annotation>
    <xs:appinfo>
       <sql:relationship name="Contacts_Customer"
                         parent="Contacts"
                         parent-key="customerID"
                         child="Customers"
                         child-key="customerID"/>
    </xs:appinfo>
  </xs:annotation>
 
  <xs:element name="Contact" sql:relation="Contacts">
  <xs:complexType>
    <xs:attribute name="contactID" type="xs:int" use="required" sql:field="contactID"/>
    <xs:attribute name="customerID" type="xs:int" use="optional" sql:field="customerID"/>
    <xs:attribute name="companyName" type="xs:string" use="optional" sql:relationship="Contacts_Customer" sql:relation="Customers" sql:field="companyName"/>
    <xs:attribute name="name" type="xs:string" use="required" sql:field="name"/>
    <xs:attribute name="title" type="xs:string" use="optional" sql:field="title"/>
    <xs:attribute name="email" type="xs:string" use="optional" sql:field="email"/>
    <xs:attribute name="phone" type="xs:string" use="optional" sql:field="phone"/>
    <xs:attribute name="fax" type="xs:string" use="optional" sql:field="fax"/>
  </xs:complexType>
  </xs:element>
</xs:schema>

Sorry for any bad formatting, you may want to copy these into notepad to look at. 

Anyway, in the template I've defined a bunch of parameters within the “sql:header“ block.  Depending on how this template is being executed, the parameters are passed in at run time (In Denise's article, over http as part of the address.  They could, for example, also be passed in via form post.).  The “updg:sync“ tags indicate an updategram.  In this case, because the element will appear in the before and after block with the same key field (contactID), this is an update.  Were I to change that so there was a different contact ID in the before and after, it would appear as two different elements, prompting a delete of the element in the before block and an insert of the one in the after.

After performing the update, I do a simple query against the database to get out the contact I just updated.  Within the XML declaration, I also specified an XSL file to execute.  This will be done after the query results are returned. Using this, I can build a dynamic website with update capabilities with no coding what-so-ever.

There are limitations here of course.  Given that this is not a programming environment, it does not have the richness of ASP.Net.  However, if you're still interested in templates, we do provide a set of managed classes (wrappers around our native code) that could be used within ASP.Net pages to execute templates. This would allow you to use templates for retrieving you XML data from Sql Server while still giving you the full power of ASP.Net and the .Net Framework.