DynamicXmlBuilder NOSQL

This is a sub-article of DynamicXmlBuilder

 

Using the ~nosql~ Directive

When the DynamicXmlBuilder builds the XmlDocument, it runs each command in the node sql against the database. When you are retrieving data from the database, this is of course necessary. But you may have some node in which all of the values can be determined without needing to go to the database for processing.

An example of this is the “hand off” node described in the Hierarchical Container Node section in the main article.

If you look at the node definition in this example:


The <Interests> node acts as a “bridge” between the <Individual> node and the <interests> node.

The node sql for <Interests> is this: 


SELECT [_cst_key] = {cst_key}

This node sql sets a [_cst_key] column alias (with a leading underscore so it will not output) with the value of {cst_key} of its parent <Individual> node.

Next, the <interest> subnode of <Interests> has this node sql. It uses the value of {_cst_key} which is set in the node sql of <Interests> above it:


Let’s look again at the node sql for <Interests>: 

SELECT [_cst_key] = {cst_key}

When this node sql is parsed and delivered as a command to the SQL Server database, we are simply executing this command: 

SELECT [_cst_key] = '398e04d3-e5b9-4df6-924b-877fa2aa3fd8'

Since the DynamicXmlBuilder already “knows” this information, and there is nothing to truly query from the database, it is not necessary to go to the database. For scenarios such as this, the DynamicXmlBuilder provides the ability to bypass an unnecessary trip to the SQL Server. To bypass an unnecessary call to SQL Server, issue the ~nosql~ directive in your node sql and enter the name and value pair(s) as shown: 


~nosql~_cst_key={cst_key}

The statement in node sql obviously is not valid T-SQL code, but with the ~nosql~ directive, the DynamicXmlBuilder will parse the values and construct the nodes properly.

Once more, here is the conventional way: 

SELECT [_cst_key] = {cst_key}

And here is the improved way: 

~nosql~_cst_key={cst_key}

The XML output by the web method will be identical either way, but the ~nosql~ way will run faster and will be less expensive.

The ~nosql~ command can also be used to output ordinary text if there are no hits to the database. Separate multiple name=value pairs with semicolons. Do not add single quotes, brackets, unnecessary spaces, tabs or carriage returns. AVECTRA’s RSS feed, for example uses this node sql in the <channel> node:

Whenever possible, use the ~nosql~ directive to reduce costly and unnecessary round trips from the web server to the SQL server.

Limitations

At this time, it is not possible to set node or element attributes in conjunction with the ~nosql~ directive. If you need to do this, then you will need to write this node sql in the conventional way.