XML forrest function not working from 16.x
search cancel

XML forrest function not working from 16.x


Article ID: 259951


Updated On:


Clarity PPM On Premise


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
    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. 


Release : 16.1.1




DE68805, in review by engineering. 

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'


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