Product Highlight – PrimalSQL and PrimalXML Example

In this blog, I will demonstrate how to use the features of PrimalSQL and PrimalXML to accomplish sample tasks.

In this example, I will need to gather product information from a database and produce an XML file that will be used for offline processing. In addition, I would like to create a static drop down list for a webpage using the information I exported.

I begin by running a basic query which displays a list of products available in the Northwind database. To do this, I will be using PrimalSQL 2009.

PrimalSQL is a powerful tool that allows you to query a variety of databases. PrimalSQL’s option to export query results allows you to present the data in multiple formats:

  • CSV: View the query results in a spreadsheet.
  • HTML: View the query results in a formatted presentation.
  • Text: View the results in any text editor.
  • XML: Export to a human readable standard that can be used by other processes.

As mentioned above, I’m interested in exporting the product information to an XML file which will be used for an offline process. For those who aren’t familiar with XML, it is a hierarchal structure that is human readable and makes a great standard for inter-process information sharing. XML is tag based and is very similar to HTML, but it is stricter with structure and formatting rules.

I run PrimalSQL 2009 and open a new query. Since I already have a connection to the Northwind database configured, I just need to set it as the active connection. Setting the active connection can be done a number of ways:

1. By using the drop down list connection panel in the ribbon menu to select the appropriate connection.

Fig 1

Fig 1: PrimalSQL: Connect by Ribbon menu

2. Right click on a connection in the Database Browser and select the “Set as active connection” option on the context menu.

Fig 2

Fig 2: PrimalSQL: Connect by context menu

3. Drag and drop a connection onto the query builder from the Database Browser.

4. When the current query is empty and not connected, the active connection is automatically set to the corresponding database when a table / view is double clicked or dragged and dropped onto the query builder, thus saving a step.

I expand the Northwind Connection in the Database Browser and drag and drop the “Products” table into the Query Builder. PrimalSQL automatically sets the active connection to the Northwind database.

Fig 3

Fig 3: PrimalSQL – Run Query

If needed, I can use either the query builder or the “SQL Edit” panel to edit the query. Since I want to view all the information from the “Products” table offline, I don’t need to alter the current query. Pressing the “Execute” button on the Ribbon menu, PrimalSQL displays the results in the “Output” grid.

Fig 4

Fig 4: PrimalSQL – Query Results

Now that I have the query results, I go to the “Export Output “menu on the ribbon bar and export the product results to an XML file, by selecting the “Export to XML” option.

Fig 5

Fig 5: PrimalSQL – Export Menu

I saved the results to a file named “Products.xml” and open the file with PrimalXML 2009. I will show you how easy it is to use PrimalXML to alter the exported query results to fit your needs as well as search and filter the results.

Fig 6

Fig 6: PrimalXML – Editing the exported XML file

Now that I have the data in XML format, I need to change the structure to better suit my needs. For example, I want to rename the “row” elements to “product” and change the “rows” root element to “products”. By making these changes, the XML file’s elements will be self explanatory. I will begin by clicking on the “row” element which will save me some typing in the next step. I then press the “Find Object” button on the ribbon menu bar and the “Find and Replace Object” Dialog appears.

Fig 7

Fig 7: PrimalXML – “Find Object” Ribbon button

The “Find and Replace Object” dialog allows me to find and replace element names, attribute names, or attribute values. In this case, the “Element Name” option is preselected along with the name of the “row” element I selected earlier. In the case where I didn’t select the right element, the dropdown list provides a list of all the elements in the document. I type the replacement name “product” in the “Replace With” field. Next I press the “Replace All” and the elements in the document will be updated by replacing “row” elements with “product” elements.

Fig 8

Fig 8: PrimalXML – Find and Replace Dialog: Replace Element Name

I do the same for the root element “rows”, but this time I rename it “products” to yield the following results:

Fig 9

Fig 9: PrimalXML – Replace Element Name Results

Now I would like to rename the “column” elements, but each “column” element pertains to a different associated value. I want to use a descriptive element name for each of the “column” elements; in fact the best name for the elements would be the value of the name attribute. PrimalXML provides an easy solution for this by allowing me to rename the element using an associated attribute value.

I select the first “column” element, to save typing, and go back to the “Find and Replace Object“ dialog. As in the case before, the “Element Name” option should be selected and “column” name value prefilled but this time I do not enter a “Replace With” value. Instead I check the “Replace with Attribute Value” check box, which disables the “Replace With” field. Now I specify the attribute from which it will use the value to name the element. I choose to use the value of the “name” attribute. This value and other filters can be specified in the Filter grid in the bottom half of the dialog.

Fig 10

Fig 10: PrimalXML – Find and Replace Object: Replace Element Name with Attribute Value

I simply type “name” in the value field next to the Attribute Name grid item. The “(Ignore)” combo automatically change to “=” as soon as I type into the field. I have the option to specify an Attribute Value which will limit the elements that are renamed to the ones that have the specified attribute whose value matches the specified value. In this case I want to keep the Attribute Value on “Ignore” so that all the “column” elements are renamed. I press the “Replace All” and get the following results:

Fig 11

Fig 11: PrimalXML – Replace Element Name with Attribute Value Results

It is worth noting that the original attribute value will be formatted in order to make it a valid element name. For example: “@The Value@” will become “TheValue”, thus removing any spaces and illegal characters.

Now I almost have the XML structure I want except for one issue: The new element names make the name attribute redundant. I can resolve this by removing the name attribute from all the elements. To do this, I will go back to the “Find and Replace Object” but first I click on the “name” attribute to save time once again. This time the “Attribute Name” option is selected with “name” as the value. Since I want to remove this attribute, I simply leave the “Replace With” value empty.

Fig 12

Fig 12: PrimalXML – Find and Replace Object: Remove Attribute

I press the “Replace All” button and the name attributes are removed from all the elements. By specifying an “Element Name” in the filters, I can remove the attribute from the elements that only match the specified element name.

Fig 13

Fig 13: PrimalXML – Remove Attribute Results

Now I have the document in the format I like; however, I still need to create a product dropdown list for my webpage. Instead of manually extracting the data and typing the list and html tags, I can use PrimalXML to filter the data I need and use the “Find and Replace Object” to assist me with the html tags.

To create the product list, I need to isolate the “ProduceName” elements. In order to do this, I use the “Simple XPath Search” panel. This panel will use basic XPath queries to search and filter elements and/or attributes and present the results in a convenient grid format. If you are not sure what the XPath of a particular element or attribute is, you can simply hover the cursor over the object you are interested in and a tooltip will appear with the object’s XPath.

Fig 14

Fig 14: PrimalXML – XPath Tooltip

As you may notice the paths look similar to a folder path in the Window Explorer. Since XML is a hierarchal format, I can use XPath in a similar fashion to find the elements I am looking for.

By right clicking on the element and selecting the “Copy XPath” menu item, I can copy the object’s XPath to the clipboard.

Fig 15

Fig 15: PrimalXML – Context Menu

In this case I right click on the “ProductName” element, copy its XPath to the clipboard and paste it in the “Simple XPath Search” panel. Now I press the “enter” key and PrimalXML lists all the objects that match the specified XPath.

Fig 16

Fig 16: PrimalXML – XPath Query Results

You can quickly go to and select any object in the results by left clicking on the item in the grid.

Similar to PrimalSQL, PrimalXML allows you to export the query results to the different formats. I right click on the column headers and select “Export to XML…” menu item and save the file to “ProductsList.xml”. By using the export feature, I effectively filtered out the information I need.

Fig 17

Fig 17: PrimalXML – XPath Grid Export Menu

I open the newly created XML with the filtered results:

Fig 18

Fig 18: PrimalXML -Exported XML File with filtered results

By replacing elements names and using the same procedures as described earlier, I can quickly create the correct HTML tags for the products drop down list:

Fig 19

Fig 19: PrimalXML – HTML Ready Result

In XML files certain characters are encoded. I want to replace the encoded characters back to their original forms. This can be accomplished by using a simple text replace.

Fig 20

Fig 20: PrimalXML – Text Replace

After replacing the special characters I simply copy and paste the document into the appropriate place in my webpage to produce the following results:

Fig 21

Fig 21: Combo box in webpage.

This completes the examples and it demonstrated how PrimalSQL and PrimalXML applications can be used in accomplishing your tasks. In review, this example covered the following features:

PrimalSQL:

1. Various ways to set the Active Connection (including a shortcut)

2. Run and export the results of a query

PrimaXML:

1. Replace element names

2. Replace element names with an attribute’s value

3. Remove attributes

4. Find the XPath of objects in the document

5. Search the XML document using the “Simple XPath Search” panel

6. Export the XPath search results

If you wish to try out these products, a 45 day trial of PrimalSQL and PrimalXML are available at http://www.primaltools.com.

Technorati Tags: ,,,,