Friday, 9 August 2013

Parse one occurrence of a repeated XML tag value using PL/SQL

Parse one occurrence of a repeated XML tag value using PL/SQL

I have the following XML structure that I have pulled from a web service
and stored in a XMLTYPE variable in PL/SQL:
<results>
<return>
<customerid>127</customerid>
<customername>Test1</customername>
</return>
<return>
<customerid>127</customerid>
<customername>Test1</customername></return>
<return>
<customerid>93</customerid>
<customername>Test2</customername>
</return>
<return>
<customerid>96</customerid>
<customername>Test3</customername>
</return>
</results>
Due to the inflexibility of the web service I am using, there is no way of
specifying that only 1 occurrence of a customerid must be displayed with
it's corresponding value. With that in mind, how can I parse this XML
document using PL/SQL to retrieve only the first occurrence of each
customer id?
For example, parsing the above would return:
127 Test1
93 Test2
96 Test3
My idea was to begin parsing the XML and query against an empty array as
to whether or not the customer id exists (has already been printed to
screen). If not, then print the customer id an matching name to screen and
add the customer id to the array. This would mean that the next time
around that XML tag would be skipped and it would move onto the next one,
at least that is what I could do in PHP but sadly this time around I have
to use PL/SQL.
Any ideas on the most efficient way to do this in PL/SQL?
Cheers,
Jason
Additional: If necessary, I can change the structure of the XML document
as I am generating it myself on the fly using PL/SQL.

No comments:

Post a Comment