Using a Custom XML Utility - XML File Example

Versions: 8.0+

The purpose of this example is to show how to set up a custom function for an XML Connection. Here we will be creating a Java function that merges multiple XML files into a single file, so that it can be queried more easily within QuerySurge. In this example, we have a folder with different XML files where each contains a record that we want to combine into one large XML file with all the records. The goal is to be able to query the final XML file for all required data.

Note: The JDK level used to compile the jar containing your custom function file must match the JDK level used by QuerySurge. You can find your QuerySurge Java Version in Administration > Server Properties > Java Version.

1. Create an external Java function to handle the creation of the merged XML file, with the signature: merge_xml_files(String dirPath, String mergedFilePath, String replacement). The attached reference Java classes show the example source code (MergeXMLTasks.java). The compiled code is contained in the included stels_xml_extension.jar file.
   
    See Resources section: XMLFunctions.java, MergeXMLTasks.java
   
   
2. Compile the Java to create the jar file and copy it, along with any dependencies to the //../<QuerySurge Install Dir>/QuerySurge/agent/jdbc folder
   
   Note: Do not seal the jar file.
   
    See Resources section: stels_xml_extension.jar, commons-io-2.4.jar
   
   
3. Stop your Agent(s).
   
   
4. Locate the agentconfig.xml file for your Agent(s), and make a copy of the file before editing it:
   //../<QuerySurge Install Dir>/QuerySurge/agent/config/agentconfig.xml
   
   Add the following driver property to the original file, which specifies the name of the function that you will use and the Java class it maps to. You may need Admin rights to edit this file.
   

   <connectionProps>
   ...
   

   <driverProp driver="jstels.jdbc.xml.XMLDriver2" prop="function:merge_xml_files" type="String" value="com.rttsweb.stels_xml_extension.XMLFunctions.merge_xml_files" />
   

   ...
   </connectionProps>

5. Restart the QuerySurgeAgent service for each Agent modified.
   
   
6. Extract the sample XML files to a folder by themselves (These files will be used as the data for your QueryPair)
   
    See Resources section: sample_XML_files.zip
   
   
7. Create an XML Connection schema file that defines where the merged file path will be and how the merged file will be processed. See this article for general guidelines on setting up your schema.xml file for XML Connections. 

    See Resources section: merge-xml-schema.xml

8. Create an XML Connection using the Connection Extensibility Connection type, that points to the schema file that you created above.
   
   
   

   Note: For the basics of setting up an XML Connection in QuerySurge, see this article.
   
   

9. Create a QueryPair and add a call to the function:
   
   The call syntax in your QueryPair is:

   CALL merge_xml_files('<path_to_XML_folder>', '<path_to_create_merged_XML_file>', '');

   
   A sample query in a QueryPair looks like this: 

   
   
   During execution, the merge_xml_files() method will merge all of the XML files in the XML folder (specified by the first argument) to a single XML file (specified by the second argument). Optionally, if any invalid XML characters are found, it will replace them with the replacement String (specified by the third, optional, argument). The XML driver will then execute the query against the merged XML file and QuerySurge will complete QueryPair execution as usual.

   
   Additional Notes:

   The example above is just one example of how the custom function mechanism can be used. Besides the use case above, you can create a custom function to do whatever you need. The custom function feature is pretty versatile and can be used in many different ways to help test your data.
   
   
   Resources