mdo:sql ( sql, entity-names, [ parameter1, value1 [ ,...n ] ] )

mdo:sql allows you to execute free sql statements. mdo:sql is also capable of putting results into cache for specified period of time or until data in database is changed. Both polling and notification methods are supported via SqlCacheDependency class. See Caching Database article for details.

Attention! mdo:sql requires module to be a Super Module - Check this option above XSLT editor before saving configuration.
  • sql - sql statement. tags {databaseOwner} and {objectQualifier} are supported to provide safe access to the DNN database objects.
  • entity-names can be one of the following:
    • list of entity names, one per each result set returned by the sql statement.
    • $xml - in this case XsltDb reads the first column of first row of first result set, creates an XML document and returns it to the caller.
    • $scalar - in this case XsltDb reads the first column of first row of first result set and returns it as a simple value (not XML).
    • $script - this indicates that query does not return any results. This token is mapped to ExecuteNonQuery.
    • $json [entity names] Returns results as a JSON string. entity names part is required only when SQL statement returns 2 or more resultsets.
    • $jsonrow Returns first row of the first resultset. $json creates an array of rows [{...}] when $jsonrow creates a single object {...}

Example 1. Show result of query as is (in XML format):

<xsl:variable name="sql">

  select name, object_id from sys.tables
  where name like '%' + @filter + '%';
  select name, column_id, object_id from sys.columns
  where name like '%' + @filter + '%';


<xsl:variable name="data" select="mdo:sql($sql, 'table, column', '@filter', 'x')"/>


Example 2. Show result of query in table:
<xsl:variable name="sql">
   select * from {databaseOwner}[{objectQualifier}EventLogTypes];
<xsl:variable name="items" select="mdo:sql($sql, 'item')"/>

<table style="border-collapse:collapse;" border="1">
  <tr style="font-weight:bold;">
    <td>Log Type Key</td>
    <td>Friendly Name</td>
    <td>CSS Class</td>
  <xsl:for-each select="$items//item">
  • Tags {databaseOwner} and {objectQualifier} are DotNetNuke tags that were setup during DotNetNuke installation process.

Example 3. Create JSON string from database
<xsl:variable name="sql">

  <!-- Here we emulate one row with fields of different types -->
    getdate() [date],
    1.77 [number],
    cast(1 as bit) [bit],
    null [null];
{{mdo:sql($sql, '$jsonrow')}}

See also:

Last edited Jan 28, 2011 at 7:03 PM by findy, version 16


No comments yet.