XmlQuery TWiki Plugin

• TWiki topics (data from tables, actions, forms and other metadata)
• TWiki attachments (where these are XML files),
• External XML documents (e.g. http://example.com/doc.xml)
• Database sources (any datasource with a DBD driver)

• HTML or XML embedded within a topic
• XML written to an attachment e.g. RSS, SVG, Excel or Word XML etc.
• Database data written to a DBI source

Table of Contents

XmlQueryPlugin Settings

• Short Description of the XmlQuery Plugin
  • Set SHORTDESCRIPTION = Apply XSLT queries to Topic Data, DBI data, XML attachments & URLs

• Debug this plugin: (See output in data/debug.txt)
  • Set DEBUG = 0

Syntax Rules

XSLTSTART tag: defining a stylesheet

The basic format of an XSLT query is as follows

%XSLTSTART{topic="^StartsWith.*"}%

%XSLTEND%

XSLTSTART Parameters

/twiki/url[id="x"]

XML Input Format

The XML that forms the input to the XSLT engine has the following outline structure. To see exactly what XML is being passed to an XSLT script set the debug parameter to either "on" or "full".

<twiki>
    <web name="Bla">
        <topic name="BlaBla">
            <data topic="BlaBla" web="Bla" version="Bla">
                <tables>
                    <table bla="bla">
                        <row>
                            <field type="title">Bla</field>
                            <field type="data">Bla</field>
                            ....
                        </row>
                        ....
                     </table>
                     ....
                 </tables>
                 <actions>
                     <action bla="bla" ..../>
                     ....
                 </actions>
                 <metadata>
                     <? bla="bla"..../>
                 </metadata>
                 <xmldata>
                     ....   
                 </xmldata>
             </data>
         <attachments>
             <attachment name="bla">
                 ....
             </attachment>
             ....
         </attachments>
         </topic>
     </web>
  <url>
     ...
  </url>
  <url>
     ...
  </url>
</twiki>

Attachment data is only available if the attach parameter is used within the XSLTSTART tag. The table and action tag each contain the same arguments as specified within the TWiki text. This is a very useful way to pass metadata into the XML. The argument doesn't have to mean anything to the TWiki macro for it to be available. One use for this could be to create id and class parameters on these data sets.

Metadata is handled in a generic fashion where the metadata name/type becomes the tag name and every argument to the metadata becomes an argument to the XML element.

To view the actual structure of any TWiki page reference it in a XSLTSTART tag and set debug = "on" or "full".

XSLT DBI Extensions

3 XSLT DBI functions are available to connect database data to the XSLT engine. These provide means to both read and write data.

• dbiselect('dbd','userid','password','sql statement')
• dbido('dbd','userid','password','sql statement')
• dbiblock('dbd','userid','password',$xml)

  
xmlns:xq="http://twiki.org/xmlquery" 

Each of these functions use the same first 3 parameters to specify the database connection. However the available set of connections is by default locked down to a limited set specified within the XmlQueryPlugin module. The reason for this is security, the DBI interface allows both reading and writing of database data. To amend the available set of connections the server admin will have to edit the plugin twiki/lib/TWiki/Plugins/XmlQueryPlugin.pm and amend the contents of the $dbi_connections variable. This variable and the $allow_user_to_specify_dbi_connection variable are both located near the start of the plugin.

    $dbi_connections = {
        'example1' => {
            'DBD'      => 'DBI:Pg:dbname=example;host=example.com',
            'user'     => 'exampleuser',
            'password' => 'examplepassword'
        },
    }

After the plugin text has been amended the web server may have to be restarted for your changes to take affect.

Once a local connection has been specified within the plugin it can then be referred to by it's identifier. In the above example that would be 'example1' . Locally specified connections overwrite the contents of the userid and password fields.

The available identifiers are listed in an error message returned from these dbi functions when an incorrect connection name has been specified.

dbiselect function

This Function takes an sql select statement, its arguments and returns the result of the select as an XML nodeset.

 <xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:exslt="http://exslt.org/common"
  xmlns:xq="http://twiki.org/xmlquery">

 <xsl:template match="/">
 <table>

<xsl:variable name="query" select="xq:dbiselect('example1','','','select * from testtable where fieldname=?',$value)/>

  <xsl:for-each select="exslt:node-set($query)/row">
      <tr>
          <td><xsl:value-of select="fieldname"/></td>
          <td><xsl:value-of select="other_fieldname"/></td>
      </tr>>
  </xsl:for-each>
  </table>
  </xsl:template>
</xsl:stylesheet>

The XML returned is in the following format with "fieldname" being replaced with the actual column name

<result>
    <row>
        <fieldname></fieldname>
        ...
    </row>
    ...
<result>

dbido function

This Function takes an sql statement, executes it with any sql_arguments and returns the number of rows updated (or an error message).

<xsl:variable name="value1" select="'1'"/>
<xsl:variable name="value2" select="'Joe'"/>
<xsl:variable name="result" select="xq:dbido('example1','','','insert into test
 VALUES(?,?)',$value1,$value2)"/>

<xsl:if test="name(exslt:node-set($result)) = 'error'">
    <xsl:text>Error during processing</xsl:text>
    <xsl:value-of select="xsl:node-set($result)"/>
</xsl:if>

<xsl:if test="exslt:node-set($result)/updates">
    <xsl:text>Rows Updated</xsl:text>
    <xsl:value-of select="exsl:node-set($result)/updates"/>
</xsl:if>

dbiblock function

This function takes a block of sql statements, some associated data and executes these within a transaction block. In the following example the variable $sql is assigned an XML fragment with 2 embedded sql statements. The second insert statement has 4 rows of data each with 2 fields. These fields are mapped onto the sql statement at the positions of the "?" characters. For more info on

 <xsl:variable name="sql">
     <statement sql="delete from test"/>
     <statement sql="insert into test VALUES(?,?)">
         <row>
             <field>1</field>
             <field>fred</field>
         </row>
         <row>
             <field>2</field>
             <field>Joe</field>
         </row>
         <row>
             <field>3</field>
             <field>Annie</field>
         </row>
         <row>
             <field>4</field>
             <field>Jane</field>
         </row>
     </statement>
</xsl:variable>

<xsl:variable name="result" select="xq:dbiblock('example1','','',$sql)"/>

<xsl:if test="name(exslt:node-set($result)) = 'error'">
    <xsl:text>Error during processing</xsl:text>
    <xsl:value-of select="exslt:node-set($result)"/>
</xsl:if>

<xsl:if test="exslt:node-set($result)/updates">
    <xsl:text>Rows Updated</xsl:text>
    <xsl:value-of select="exslt:node-set($result)/updates"/>
</xsl:if>

XSLT TWiki Functions

3 XSLT functions are available for interaction with Twiki.

• readtopic('web name','topic name')
• readattachment('web name','topic name','attachment name')
• cgiparam('parameter name' …)

  
xmlns:xq="http://twiki.org/xmlquery" 

readtopic function

This Function takes the name of a web and the name of a topic and returns the XML for that topic.

 <xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:exslt="http://exslt.org/common"
  xmlns:xq="http://twiki.org/xmlquery">

 <xsl:template match="/">
 <table>

<xsl:variable name="topic" select="xq:readtopic('TWiki','WebHome')"/>

  <xsl:for-each select="exslt:node-set($topic)//table">
      <tr>
          <td><xsl:value-of select="@name"/></td>
          <td><xsl:value-of select="count(row)"/></td>
      </tr>>
  </xsl:for-each>
  </table>
  </xsl:template>
</xsl:stylesheet>

 <xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:exslt="http://exslt.org/common"
  xmlns:xq="http://twiki.org/xmlquery">

 <xsl:template match="/">
 <table>

<xsl:variable name="attach" select="xq:readattachment('TWiki','WebHome','attached.xml')"/>

  <xsl:for-each select="exslt:node-set($attach)//surname">
      <tr>
          <td><xsl:value-of select="."/></td>
      </tr>>
  </xsl:for-each>
  </table>
  </xsl:template>
</xsl:stylesheet>

 <xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:exslt="http://exslt.org/common"
  xmlns:xq="http://twiki.org/xmlquery">

 <xsl:template match="/">
 <table>

  <xsl:variable name="params" select="xq:cgiparam()" />

  <xsl:for-each select="exslt:node-set($params)/*">
      <tr>
          <td><xsl:value-of select="name()"/></td>
          <td><xsl:value-of select="text()"/></td>
      </tr>>
  </xsl:for-each>

  </table>
  </xsl:template>
</xsl:stylesheet>