XMLdb is an extension of the DDN core module XML/XSL that allows the user to access data from a database utilitizing the “for XML” Sql extension available in MS-SQL.
The module allow the user to define the database connection and appropriate select statement. The connection statement is optional and will assume the current DNN attachted database.
The users.xsl file is included in the Private Assembly. If you want to try this example, you will need to upload the file to the portal using the File Manager. The XSL file will be located in \DesktopModules\XMLdb. Note: The XSL is case-sensitive so you must maintain the case shown in the example above.
Since the “for XML” extension returns a XML fragment, a Prefix and Suffix must be applied to the results so that a complete XML document is returned. If none is supplied, then a prefix of "" along with the corresponding suffix of "" is applied.
An optional connection string is available to allow connecting to another database. If the connection string is empty, then the current database is used.
Three special Meta-strings will be replaced with the current value PRIOR to execution of the SQL command:
[PortalID] = is replaced with the current portalID (see example above)
[ModuleID] = is replaced with the current ModuleID
[UserID] = is replaced with the current UserID
A Test Command button exists on the Edit Screen. This allows the user to see the resultant XML document. Additionally, you may add the SQL command. Lastly, if required, the user can add CR/LFs to the document. This is helpful when copying the document to a flat file for testing.
QueryString Substitution is possible as of Version 1.1. If the SQL command contains a meta-string of the same name as the querystring name, the value will be substituted in the SQL command. The QueryString name must be surrounded by ‘[‘ and ‘]’. For example, the if the QueryString contained “Username=Admin” then the SQL command requires “[Username]” to perform the substitution.
Note: XMLdb does a exact substitution, so if the resulting value is a string, the SQL command must supply the needed quotes. The above SQL command example may look like:
... where Username = '[Username]' ....
and would result in
... where Username = 'Admin' ...
QueryString Substitution will not work correctly with Test Command as no Query Values are available for substitution.
Cautions:
The initial use of the XMLdb module is for a Intranet site where all uses of the module are under control of a single Admin. This leads to several issues if not used in such an environment.
- The SQL command is NOT checked against command injections
- The SQL command has complete access to the current database tables (all portals)
Acknowledgements:
I based this module on the original Core module from DNN v2.0.3. All original functionality of the XML/XSL control still exists. Since this module is largely based on the DNN Core module, DNN copyright and permission remain. You are free to distribute and enhance this module as long as you follow the DotNetNuke distribution rules.
Source for this module will be available shortly, once I have had time to review any feedback.