On my current project we're using PowerBuilder 7.0 to develop components to serve an ASP front end; however, the front-end developers wanted data sent to them in XML format.

As there was no built-in method to do this, I wrote a method called datastoreToXML that utilizes the Microsoft XML Document Object Model (DOM) and the properties of a DataStore to convert the data in a DataStore to XML.

datastoreToXML Method
The first step in this method is to create an instance of an XML DOM object. This is pretty straightforward using PowerBuilder's oleobject datatype and the ConnectToNewObject method.

lole_xml = CREATE oleobject
li_rc = lole_xml.ConnectToNewObject
("MSXML2.DOMDocument.3.0")
if li_rc < 0 then
li_rc = -1
goto finish
end if

On this project we used Microsoft's DOM, but I imagine datastoreToXML would work with any compliant DOM with minor modifications. We now have an XML DOM object that I call lole_xml.

Since an XML document needs a root node, create this next. I let the user of the method name the root node by passing the root node name via the argument asv_rootNodeName. I create the root node by calling lole_xml's createElement method, then set lole_xml's documentElement property to the root node I just created.

rootNode = lole_xml.createElement
(asv_rootNodeName)
lole_xml.documentElement = rootNode

The next step is to create a node for each row of data in the DataStore. I named these nodes "row." The most efficient way to do this is to first create a kind of "template" node (which I call templateNode), then use the cloneNode method to create a clone of this template node for each row of data.

What should each row node look like? There are several ways you could go. I chose to create a separate node under the row node for each column of data. I set the column name as the name of the node and the data of the column as the text of the node. For example, a column LastName with the value Smith would appear as <LastName>Smith</LastName>. Two other ways are to make each column an attribute of the row node or to create a "column" node for each column with attributes or subnodes for the column name and column value.

I found that some ASP front-end developers prefer to have the columns as attributes of the row node. Incidentally, this is the format that results when Visual Basic recordsets are converted to XML with the PersistToXML method. The added advantage is that attributes can have datatypes, and we can get the datatype of a DataStore column with the describe method. For this article, however, I'll stick to the simple method.

To create a node for each column I use a for loop that uses the DataStore property DataWindow.column.count to loop through all the columns. For each column I use the DataStore describe method to get the name of the column. Once I have the column name, I can create the node for the column with the createElement method and append the node to its parent, the row node, with the appendChild method (see Listing 1).

Now that I have a template node to clone, I need to clone it for each row of data and set the values for the columns (see Listing 2). I accomplish this through two for-next loops. The outer loop cycles through the rows in the DataStore. For each row I call the templateNode's cloneNode method to create a copy of itself, which I call rowNode. The TRUE parameter tells the method to clone not only the node but also all its subnodes. In this case the subnodes are the column nodes, so I definitely want them.

Next I get a reference to these column nodes via the childNodes property. Now the inner loop takes over and assigns the value of each column in the row to the corresponding column nodes text property - note the use of the childNodes nextNode method. This method is a Microsoft extension to the W3C DOM and initially points to the first node of the child nodes.

Because I created the column nodes from the DataStore, the nodes and the columns in the DataStore are in the same sequence, so I can reference them positionally. Once the values are assigned, I add the newly created row node to the root node I created earlier with the appendNode method.

The last step is to return the finished XML (see Listing 3). Because I was appending each of the created rowNodes to the root node that I created and because the root node was set as the documentElement of lole_xml, lole_xml contains all the nodes I created. To get the corresponding XML I can simply access lole_xml's XML property. In this method I return the XML to the calling method via a reference variable asr_xml.

There it is, a quick and dirty way to convert a DataStore to XML. With some additional error checking, the example code presented here should be ready for your project.