ADOAdapter2.BuildSQLFromXMLNodes method (IXMLDOMNode)

Article
07/28/2014

Returns a SQL command text fragment using the specified XML node.

Namespace: Microsoft.Office.Interop.InfoPath
Assembly: Microsoft.Office.Interop.InfoPath (in Microsoft.Office.Interop.InfoPath.dll)

Syntax

'Declaration
Function BuildSQLFromXMLNodes ( _
    pXmlNode As IXMLDOMNode _
) As String
'Usage
Dim instance As ADOAdapter2
Dim pXmlNode As IXMLDOMNode
Dim returnValue As String

returnValue = instance.BuildSQLFromXMLNodes(pXmlNode)

string BuildSQLFromXMLNodes(
    IXMLDOMNode pXmlNode
)

Parameters

pXmlNode
Type: Microsoft.Office.Interop.InfoPath.Xml.IXMLDOMNode

The XML node to be converted to an SQL fragment.

Return value

Type: System.String
A SQL command text fragment.

Implements

ADOAdapter.BuildSQLFromXMLNodes(IXMLDOMNode)

Remarks

The fragment of SQL that the BuildSQLFromXMLNodes method generates is an SQL WHERE clause in the form of field = value. The XML node that you use for the pXmlNode argument should be a descendant of the dfs:queryFields node; when you have the SQL command text fragment, you can add it to the existing SQL command string of the ADOAdapterObject object using the Command property.

Examples

In the following example, the BuildSQLFromXMLNodes method is used to query the Orders table of the Northwind database. The data returned is filtered for items greater than or equal to the value of the queryFieldNode:

private void QueryGreaterThan()
{    
 string oldCommand;
 string whereClause;
 IXMLDOMNode queryFieldNode;
 IXMLDOMNode curQueryFieldAttribute;
 IXMLDOMNamedNodeMap queryFieldAttributes;
 ADOAdapter adapter = (ADOAdapter)thisXDocument.QueryAdapter;

 // Build the WHERE clause from the QueryFields in the form's
 // underlying XML DOM.
 queryFieldNode = thisXDocument.DOM.selectSingleNode("dfs:myFields/dfs:queryFields/q:Orders");
 whereClause = adapter.BuildSQLFromXMLNodes(queryFieldNode);

// The QueryFields are empty.
 if (whereClause == null)
 {
  whereClause = String.Empty;
 }
 // Replace the '=' signs with '>=', and append the clause to 
 // the SQL command text.
 whereClause = whereClause.Replace(@"=", @">=");
 oldCommand = adapter.Command;
 
 if (whereClause != "")
 {
  adapter.Command = oldCommand + " where " + whereClause;
 }
 
 // Clear the QueryFields so the WHERE clause isn't 
 // automatically generated.
 queryFieldAttributes = queryFieldNode.attributes;
 while ((curQueryFieldAttribute = queryFieldAttributes.nextNode()) != null)
 {
  curQueryFieldAttribute.text = "";
 }

  // Perform the query.
 try
 {
  thisXDocument.Query();
 }
 catch (Exception ex)
 {
  thisXDocument.UI.Alert("Failed to query.\n\n" + ex.Message);
 }

 // Reset the command so that subsequent queries are based on 
 // the correct SQL command text string.
 adapter.Command = oldCommand;
}

Share via