XML forrest function not working from 16.x

Products

• Clarity PPM On Premise

Issue/Introduction

External SOAP calls based on NSQL with XMLFOREST function is not working after 15.9.3 

Steps to Reproduce: 

1. Create a Lookup : TEST LOOKUP
   Internal Name    Z_TEST_LKP
   Hidden Key    LOOKUP_CODE
   Display attribute : Name
   Add few values: Test1,....Test10
    
2. Create an attribute on Project
   NAME= TEST ATTRIBUTE
   Attribute id =z_test_attr
   Type =  Multi Valued Lookup - String and link it to the lookup Z_TEST_LKP from step 1
    
3. For few Projects populate the lookup values for the attribute TEST ATTRIBUTE
    
4. Create NSQL Query with the following code
   SELECT   @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:query.pk_id:[email protected],       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:query.name:[email protected],       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:query.competency:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:query.code:[email protected]
   from (
   select odf.pk_id,inv.name,xmlelement(Competencies,xmlagg(xmlforest(lkp.name) order by lkp.name)) as competency, inv.code
   from inv_investments inv
   left join ODF_MULTI_VALUED_LOOKUPS odf on odf.pk_id=inv.id
   left join cmn_lookups_v lkp on odf.value=lkp.LOOKUP_CODE
   where lkp.LOOKUP_TYPE='Z_TEST_LKP' and lkp.LANGUAGE_CODE='en'
   and odf.ATTRIBUTE='z_test_attr' and odf.OBJECT='project'
   group by odf.pk_id,inv.name,inv.code) query
   where @[email protected]
    
5. Create a TEST Portlet and link it to the NSQL code from step 4
    
6. Consume the portlet by linking it to a portlet page or default General Menu

Expected Results: 
The portlet to display values where the competency field uses xmlforest to represent data with XML tree format using the specified column names as element names and the column values as the element values. 

Actual Results:
The portlet fails with an error 500 - Internal server Error. 

Environment

Release : 16.1.1

Cause

DE68805

Resolution

DE68805, in review by engineering. 

Workaround: 
Modifying NSQL to use XML element that contains a comma-separated list of the ordered "name" values.i.e. replacing the xmlelement for competency from

'xmlelement(Competencies,xmlagg(xmlforest(lkp.name) order by lkp.name)) as competency'
 

to

'RTRIM (XMLAGG (XMLELEMENT (Competencies, lkp.name|| ',') order by lkp.name).EXTRACT('//text()').getclobval(),',') as competency'