Using get_mds_data in order to get composite list (SOA11G)

In the previous post I give the details of get_mds_data function to you. Now let's use it in an useful way;

 

select "COMPOSITEXML","DN","STATE","CMODE","DEPLOYEDTIME" from  (

select

'/deployed-composites/'||replace(extractvalue(a.column_value,'/composite-revision/@dn'),'!','_rev')||'/composite.xml' compositexml,

extractvalue(a.column_value,'/composite-revision/@dn') dn,

extractvalue(a.column_value,'/composite-revision/@state') state,

extractvalue(a.column_value,'/composite-revision/@mode') cmode,

extractvalue(a.column_value,'/composite-revision/composite/@deployedTime') deployedtime

 from

table(xmlsequence(extract(xmltype(get_mds_data('/deployed-composites/deployed-composites.xml')),'/deployed-composites/composite-series/composite-revision'))) a

);

you can extract other elements or attributes of deployed-composite.xml depending on your needs.

ps: for my deployed-composite.xml I had a interesting case where a composite had two deployedTime so I had to add [1] to last column ; extractvalue(a.column_value,'/composite-revision/composite[1]/@deployedTime')