As mentioned above, I\u2019m interested in exporting the product information to an XML file which will be used for an offline process. For those who aren\u2019t 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.<\/p>\n
I run PrimalSQL 2009 and open a new query. Since I\u00a0already 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:<\/p>\n
1. By using the drop down list connection panel in the ribbon menu to select the appropriate connection.<\/p>\n
Fig 1: PrimalSQL: Connect by Ribbon menu<\/p>\n 2. Right click on a connection in the Database Browser and select the \u201cSet as active connection\u201d option on the context menu.<\/p>\n Fig 2: PrimalSQL: Connect by context menu<\/p>\n 3. Drag and drop a connection onto the query builder from the Database Browser.<\/p>\n 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.<\/p>\n I expand the Northwind Connection in the Database Browser and drag and drop the \u201cProducts\u201d table into the Query Builder. PrimalSQL automatically sets the active connection to the Northwind database.<\/p>\n Fig 3: PrimalSQL \u2013 Run Query<\/p>\n If needed, I can use either the query builder or the \u201cSQL Edit\u201d panel to edit the query. Since I want to view all the information from the \u201cProducts\u201d table offline, I don\u2019t need to alter the current query. Pressing the \u201cExecute\u201d button on the Ribbon menu, PrimalSQL displays the results in the \u201cOutput\u201d grid.<\/p>\n Fig 4: PrimalSQL – Query Results<\/p>\n Now that I have the query results, I go to the \u201cExport Output \u201cmenu on the ribbon bar and export the product results to an XML file, by selecting the \u201cExport to XML\u201d option.<\/p>\n Fig 5: PrimalSQL – Export Menu<\/p>\n I saved the results to a file named \u201cProducts.xml\u201d 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.<\/p>\n Fig 6: PrimalXML \u2013 Editing the exported XML file<\/p>\n 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 \u201crow\u201d elements to \u201cproduct\u201d and change the \u201crows\u201d root element to \u201cproducts\u201d. By making these changes, the XML file’s elements will be self explanatory. I will begin by clicking on the \u201crow\u201d element which will save me some typing in the next step. I then press the \u201cFind Object\u201d button on the ribbon menu bar and the \u201cFind and Replace Object\u201d Dialog appears.<\/p>\n Fig 7: PrimalXML \u2013 \u201cFind Object\u201d Ribbon button<\/p>\n The \u201cFind and Replace Object\u201d dialog allows me to find and replace element names, attribute names, or attribute values. In this case, the \u201cElement Name\u201d option is preselected along with the name of the \u201crow\u201d element I selected earlier. In the case where I didn\u2019t select the right element, the dropdown list provides a list of all the elements in the document. I type the replacement name \u201cproduct\u201d in the \u201cReplace With\u201d field. Next I press the \u201cReplace All\u201d and the elements in the document will be updated by replacing \u201crow\u201d elements with \u201cproduct\u201d elements.<\/p>\n Fig 8: PrimalXML – Find and Replace Dialog: Replace Element Name<\/p>\n I do the same for the root element \u201crows\u201d, but this time I rename it \u201cproducts\u201d to yield the following results:<\/p>\n Fig 9: PrimalXML \u2013 Replace Element Name Results<\/p>\n Now I would like to rename the \u201ccolumn\u201d elements, but each \u201ccolumn\u201d element pertains to a different associated value. I want to use a descriptive element name for each of the \u201ccolumn\u201d 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.<\/p>\n I select the first \u201ccolumn\u201d element, to save typing, and go back to the \u201cFind and Replace Object\u201c dialog. As in the case before, the \u201cElement Name\u201d option should be selected and \u201ccolumn\u201d name value prefilled but this time I do not enter a \u201cReplace With\u201d value. Instead I check the \u201cReplace with Attribute Value\u201d check box, which disables the \u201cReplace With\u201d 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 \u201cname\u201d attribute. This value and other filters can be specified in the Filter grid in the bottom half of the dialog.<\/p>\n Fig 10: PrimalXML \u2013 Find and Replace Object: Replace Element Name with Attribute Value<\/p>\n I simply type \u201cname\u201d in the value field next to the Attribute Name grid item. The \u201c(Ignore)\u201d combo automatically change to \u201c=\u201d 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 \u201cIgnore\u201d so that all the \u201ccolumn\u201d elements are renamed. I press the \u201cReplace All\u201d and get the following results:<\/p>\n Fig 11: PrimalXML – Replace Element Name with Attribute Value Results<\/p>\n It is worth noting that the original attribute value will be formatted in order to make it a valid element name. For example: \u201c@The Value@\u201d will become \u201cTheValue\u201d, thus removing any spaces and illegal characters.<\/p>\n 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 \u201cFind and Replace Object\u201d but first I click on the \u201cname\u201d attribute to save time once again. This time the \u201cAttribute Name\u201d option is selected with \u201cname\u201d as the value. Since I want to remove this attribute, I simply leave the \u201cReplace With\u201d value empty.<\/p>\n Fig 12: PrimalXML \u2013 Find and Replace Object: Remove Attribute<\/p>\n I press the \u201cReplace All\u201d button and the name attributes are removed from all the elements. By specifying an \u201cElement Name\u201d in the filters, I can remove the attribute from the elements that only match the specified element name.<\/p>\n Fig 13: PrimalXML \u2013 Remove Attribute Results<\/p>\n 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 \u201cFind and Replace Object\u201d to assist me with the html tags.<\/p>\n To create the product list, I need to isolate the \u201cProduceName\u201d elements. In order to do this, I use the \u201cSimple XPath Search\u201d 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\u2019s XPath.<\/p>\n Fig 14: PrimalXML – XPath Tooltip<\/p>\n 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.<\/p>\n By right clicking on the element and selecting the \u201cCopy XPath\u201d menu item, I can copy the object\u2019s XPath to the clipboard.<\/p>\n Fig 15: PrimalXML \u2013 Context Menu<\/p>\n In this case I right click on the \u201cProductName\u201d element, copy its XPath to the clipboard and paste it in the \u201cSimple XPath Search\u201d panel. Now I press the \u201center\u201d key and PrimalXML lists all the objects that match the specified XPath.<\/p>\n Fig 16: PrimalXML \u2013 XPath Query Results<\/p>\n You can quickly go to and select any object in the results by left clicking on the item in the grid.<\/p>\n Similar to PrimalSQL, PrimalXML allows you to export the query results to the different formats. I right click on the column headers and select \u201cExport to XML\u2026\u201d menu item and save the file to \u201cProductsList.xml\u201d. By using the export feature, I effectively filtered out the information I need.<\/p>\n Fig 17: PrimalXML \u2013 XPath Grid Export Menu<\/p>\n I open the newly created XML with the filtered results:<\/p>\n Fig 18: PrimalXML -Exported XML File with filtered results<\/p>\n 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:<\/p>\n Fig 19: PrimalXML – HTML Ready Result<\/p>\n 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.<\/p>\n Fig 20: PrimalXML \u2013 Text Replace<\/p>\n After replacing the special characters I simply copy and paste the document into the appropriate place in my webpage to produce the following results:<\/p>\n Fig 21: Combo box in webpage.<\/p>\n 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:<\/p>\n PrimalSQL:<\/p>\n 1. Various ways to set the Active Connection (including a shortcut)<\/p>\n 2. Run and export the results of a query<\/p>\n PrimaXML:<\/p>\n 1. Replace element names<\/p>\n 2. Replace element names with an attribute\u2019s value<\/p>\n 3. Remove attributes<\/p>\n 4. Find the XPath of objects in the document<\/p>\n 5. Search the XML document using the \u201cSimple XPath Search\u201d panel<\/p>\n 6. Export the XPath search results<\/p>\n If you wish to try out these products, a 45 day trial of PrimalSQL and PrimalXML are available at http:\/\/www.primaltools.com<\/a>.<\/p>\n Technorati Tags:\u00a0PrimalXML<\/a>,PrimalSQL<\/a>,SQL<\/a>,XML<\/a>,HowTo<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[283],"tags":[1004,1006,38,197],"class_list":["post-1265","post","type-post","status-publish","format-standard","hentry","category-howto","tag-primalsql","tag-primalxml","tag-sql","tag-xml"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/posts\/1265","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/comments?post=1265"}],"version-history":[{"count":23,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/posts\/1265\/revisions"}],"predecessor-version":[{"id":1312,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/posts\/1265\/revisions\/1312"}],"wp:attachment":[{"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/media?parent=1265"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/categories?post=1265"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/tags?post=1265"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"Fig](\"http:\/\/www.sapien.com\/blog\/wp-content\/uploads\/2009\/03\/fig-1.png\")
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n