How to Parse XML in PowerBuilder
We have been using the PBDOM object to parse XML returned by a REST Service and consume the values in our legacy PowerBuilder application for quite a while now. There have been several documentation on how to do this and many have expressed that they are quite happy with the result.
You can find complete documentation on how to do this here – PBDOM XML Parser.
However, we have been facing several issues with this process like for instance we used to receive a random failure error message saying that the OLE object POST function failed. This was a very tricky error and was very difficult to analyze since it was not reproducible in our test or development environments. To avoid this we recently we moved away from the DOM and started using the Datawindow Import XML function to avoid parsing. This works great without much coding and easy to maintain.
Another issue was with some special characters returned by the REST Service like “UTF-8” coded characters which used to fail when we tried to import the XML into a datawindow. We had to write customized functions to strip out all these special characters before consuming the XML returned.
Yesterday we hit upon another issue where if there are new tags/fields added to the returned XML from the service, the datawindow import failed even though we do not care about the new fields. Immediately we could sense that this wasnt the right way to handle this, it is similar to having an Insert statement without mentioning the column names of the table. So this got us thinking to go back to the traditional XML parsing using the DOM but that whole process would slow down the transaction and would also result in lot of code.
Finally, we found a very useful and scalable method to parse the XML and use it in PowerBuilder using T-SQL. You can see the code used below for your reference:
DECLARE @x XML
SET @x = ‘
<ArrayOfClientPlan xmlns=”http://com.rest/clientservice/2011-05-01/” xmlns:i=”http://www.w3.org/2001/XMLSchema-instance“>
WITH XMLNAMESPACES(DEFAULT ‘http://com.rest/clientservice/2009-05-01/’)
c.value(‘./ClientPlan/ClientPlanId[not(@xsi:nil = “true”)]’, ‘VARCHAR(100)’)
FROM @x.nodes(‘ArrayOfClientPlan’) T(c)
Analysis & Usage:
You can create a stored procedure that accepts a parameter(Varchar) and send it the whole XML string returned from the service. All you need to do inside is the final select statement.
The “With XMLNAMESPACES” keyword before the select will ensure that the Select query will not fail due to the presence of a customized namespace in the XML.
In the Select, you can pick and choose what are the tag values that your PowerBuilder application is ready to consume.
Finally, you can create a datawindow for this stored procedure and have your result set captured in your UI. No need to worry if the service returns a couple of new tags, your Select will not fail and it will be FoolProof!!!!
Hope you liked this solution, please do share your thoughts by leaving a comment below. Do you have any other tips that PB professionals can make use of, then do write to us and we will be happy to publish it in our site. Thanks for reading.