Download source code - 603 KB

Contents

Introduction
Introduction to XSLT
Report generation

SQL -> XML
XML -> XML schema
XML schema -> WordML (WordML template creation based on an XML schema)
WordML -> XSLT (WordML template to XSLT transformation)
Applying XSLT transformation to XML data

Conclusion

Introduction

There are many tools which allow creating documents based on data stored in a database: Crystal Reports, Fast Reports etc. Most of them do not allow a user to edit a template. The task I’ve tried to solve is to create a tool based on XML/XSLT, which allows a user to create a template from scratch, namely:

Construct a SQL query in order to obtain XML and XML schema from SQL server.
Construct a WordML template.
Generate document, which could be virtually represented as a WordML template filled with data obtained from a SQL Server.

The advantage of the “XML/XSLT” approach is that a user has the opportunity to create trees with rather complex topology. Word has been chosen as a tool for constructing the WordML template due to its universal usage and ability to display XML structure. The idea to realize the generator based on XML/XSLT appeared after reading the article Generating Word Reports/Documents. I strongly advise you to read this article.

Introduction to XSLT/XSL

With XSL, you can freely modify any source text (XML) and produce different output from the same source file. An XSL processor parses an XML source and tries to find a matching template rule. If it does, instructions inside the matching template are evaluated. Parts of the XML document to which the template should be applied are determined by location paths. The required syntax is specified in the XPath specification. Simple cases look very similar to file system addressing. Processing always starts with the template match ="/". This matches the root node (the node, its only element child, is the document element, in our case "root"). Many style sheets do not contain this element explicitly. When this template is not explicitly given, the implicit template is used (it contains the only instruction). This instruction means: process all children of the current node, including text nodes. When a template for the node exists, there is no default processing invoked. If you want to include descendants of the node, you have to explicitly request their templates.

Report generation

Report generation could be virtually divided into four steps.

Create a SQL query in order to obtain XML data and XML schema,
Construct a WordML template,
Transform a WordML template into XSLT transformation,
Generate a report by means of applying XSLT transformation to XML data.

SQL - > XML

SQL query is an initial point for all transformations. It determines the data which should be requested from the server. I utilize a MSSQL 2005 (Yukon) server, which provides the opportunity to the requested data in XML format. The typical SQL query looks like the fallowing:

WITH XMLNAMESPACES(DEFAULT 
    'http://wrg/kpd_types.xsd')
SELECT *
FROM kpd_types
FOR XML PATH('kpd_types'), ROOT('root')

The response from the server:

<root xmlns="http://wrg/kpd_types.xsd">
<kpd_types>
    <oid>1</oid>
    <category>cargo</category>
    <label>paper</label>
</kpd_types>
<kpd_types>
    <oid>2</oid>
    <category>cargo</category>
    <label>food</label>
</kpd_types>
<kpd_types>
    <oid>3</oid>
    <category>cargo</category>
    <label>metal</label>
</kpd_types>
</root>

XML->XML schema

What is an XML schema? The XML schema is a description of the XML structure. In short, it contains n enumeration of all the elements and attributes presented in an XML document. We will need it when we start to construct our WordML template. The set of elements we could place inside the WordML document will be determined by the XML schema.

Let’s consider the DataSet class. It has two useful methods: ReadXml() and WriteXmlSchema(). When the ReadXml() method is called, the XML structure is built automatically behind the scene. We can read or store it by means of the WriteXmlSchema() method.

XmlDataDocument doc = new XmlDataDocument();
XmlReader xmlData;
xmlData = XmlReader.Create(new StringReader(xml));
doc.DataSet.ReadXml(xmlData);
doc.DataSet.WriteXmlSchema(WordReportGenerator._path + "//" + 
                           _xmlSchemaFileName + ".xsd");

XML schema -> WordML

When we have the XML schema, we are ready to create a template for our report. First of all, we have to attach it to a Word document.

object schema = _xmlSchemaName;
object alias = _xmlSchemaAlias;
object schemafilename = _path + _xmlSchemaFileName + ".xsd";
App.ActiveDocument.XMLSchemaReferences.Add(ref schema, 
                   ref alias, ref schemafilename, true);

Then, do some tuning: make XML tags and the task pane visible, and allow saving the document without validation.

App.ActiveWindow.View.ShowXMLMarkup =true;
App.TaskPanes[Word.WdTaskPanes.wdTaskPaneXMLStructure].Visible =true;
App.ActiveDocument.XMLSchemaReferences.AllowSaveAsXMLWithoutValidation = true;

WordML ->XSLT

Let’s consider how a template transformation into XSLT occurs. The initial WordML template could be represented in an abbreviated form as:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?mso-application progid="Word.Document"?>
<w:wordDocument 
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:ns0="http://wrg/kpd_types.xsd">
…
<w:body><wx:sect>
…
<ns0:root>
    <ns0:kpd_types>
    <w:p>
        <ns0:oid/>
        <w:r><w:t>,</w:t></w:r>
        <ns0:category/>
        <w:r><w:t>,</w:t></w:r>
        <ns0:label/>
    </w:p>
    </ns0:kpd_types>
</ns0:root>
…
</wx:sect></w:body>
</w:wordDocument>

Where the tags between <ns0:root>…</ns0:root> are per se the form for filling with XML data. The XSTL transformation provides the mechanism for such a filling. Let’s see how it looks like.

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:ns0="http://wrg/kpd_types.xsd">

<xsl:output method="xml" encoding="UTF-8" standalone="yes"/>

<xsl:template match="/">
<xsl:processing-instruction name="mso-application">
<xsl:text>progid="Word.Document"</xsl:text>
</xsl:processing-instruction>

<w:wordDocument 
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint" 
xmlns:o="urn:schemas-microsoft-com:office:office" 
xmlns:ns0="http://wrg/kpd_types.xsd">
…
<w:body><wx:sect>
…
<xsl:apply-templates select="ns0:root" />
…
</wx:sect></w:body>
…
</w:wordDocument>
</xsl:template>

<xsl:template match="/ns0:root">
<ns0:root>
    <xsl:apply-templates select="ns0:kpd_types" />
</ns0:root>
</xsl:template>

<xsl:template match="/ns0:root/ns0:kpd_types">
<ns0:kpd_types>
<w:p>
    <xsl:apply-templates select="ns0:category"/>
        <w:r><w:t><xsl:text>,</xsl:text></w:t></w:r>
    <xsl:apply-templates select="ns0:label"/>
        <w:r><w:t><xsl:text>,</xsl:text></w:t></w:r>
    <xsl:apply-templates select="ns0:oid" />
</w:p>
</ns0:kpd_types>
</xsl:template>

<xsl:template match="/ns0:root/ns0:kpd_types/ns0:label">
<ns0:label>
    <w:r><w:t><xsl:value-of select="." /></w:t></w:r>
</ns0:label>
</xsl:template>

<xsl:template match="/ns0:root/ns0:kpd_types/ns0:oid">
<ns0:oid>
    <w:r><w:t><xsl:value-of select="." /></w:t></w:r>
</ns0:oid>
</xsl:template>

<xsl:template match="/ns0:root/ns0:kpd_types/ns0:category">
<ns0:category>
    <w:r><w:t><xsl:value-of select="." /></w:t></w:r>
</ns0:category>
</xsl:template>

</xsl:stylesheet>

Applying XSLT transformation to XML data

Let’s get as a source XML, the following set of data:

<root xmlns="http://wrg/kpd_types.xsd">
<kpd_types>
    <oid>1</oid>
    <category>cargo</category>
    <label>paper</label>
</kpd_types>
<kpd_types>
    <oid>2</oid>
    <category>cargo</category>
    <label>food</label>
</kpd_types>
<kpd_types>
    <oid>3</oid>
    <category>cargo</category>
    <label>metal</label>
</kpd_types>
</root>

Applying the XSLT transformation to this XML, we get the final document:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<?mso-application progid="Word.Document"?>
<w:wordDocument 
  xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml" 
  xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint" 
  xmlns:o="urn:schemas-microsoft-com:office:office" 
  xmlns:ns0="http://wrg/kpd_types.xsd">
  …
<w:body><wx:sect>
…
<ns0:root>
    <ns0:kpd_types>
    <w:p>
        <ns0:category><w:r><w:t>cargo</w:t></w:r></ns0:category><w:r><w:t>,</w:t></w:r>
        <ns0:label><w:r><w:t>paper</w:t></w:r></ns0:label><w:r><w:t>,</w:t></w:r>
        <ns0:oid><w:r><w:t>1</w:t></w:r></ns0:oid>
    </w:p>
    </ns0:kpd_types>
    <ns0:kpd_types>
    <w:p>
        <ns0:category><w:r><w:t>cargo</w:t></w:r></ns0:category><w:r><w:t>,</w:t></w:r>
        <ns0:label><w:r><w:t>food</w:t></w:r></ns0:label><w:r><w:t>,</w:t></w:r>
        <ns0:oid><w:r><w:t>2</w:t></w:r></ns0:oid>
    </w:p>
    </ns0:kpd_types>
    <ns0:kpd_types>
    <w:p>
        <ns0:category><w:r><w:t>cargo</w:t></w:r></ns0:category><w:r><w:t>,</w:t></w:r>
        <ns0:label><w:r><w:t>metal</w:t></w:r></ns0:label><w:r><w:t>,</w:t></w:r>
        <ns0:oid><w:r><w:t>3</w:t></w:r></ns0:oid>
    </w:p>
    </ns0:kpd_types>
</ns0:root>
…
</wx:sect></w:body>
…
</w:wordDocument>

Conclusion

Thus, we get the Word document filled with data from the database. I have to mention that I have utilised SqlWrapper - a very suitable Data Access Layer. I will be glad to answer all your questions.

XML/XSLT Word Report Generator