with readata AS (
SELECT
act.spec_id,
act.version_no,
act.display_name,
act.effective_date,
act.expiration_date,
act.implementationvalue,
act.implpayload
FROM
dbuser.ref_data readata,
XMLTABLE('/ProfileSpec/ProfileSpecVersions/ProfileSpecVersion' PASSING(CAST(readata.payload AS XMLTYPE))
COLUMNS spec_id VARCHAR(50) PATH './ID/@value', version_no INTEGER PATH './@versionId', display_name VARCHAR(100) PATH './DisplayName/Value/@value',
effective_date VARCHAR(100) PATH './@effective', expiration_date VARCHAR(100) PATH './@expiration', implementationvalue VARCHAR(
100) PATH './Implementation/@value', implpayload xml PATH './ImplementationParameter')
AS act
where readata.entity_type = 'ProfileSpec' )
SELECT
d.spec_id,
d.version_no,
d.display_name,
d.effective_date,
d.expiration_date,
d.implementationvalue,
imp.parametername,
imp.activityaction,
imp.ismandatory,
imp.parametersource,
imp.parametervalue
FROM
readatadata d,
XMLTABLE ( '/ImplementationParameter/ProfileSpecImplementationParameter' PASSING ( d.implpayload ) COLUMNS parametername
VARCHAR(50) PATH './ParameterName/@value', activityaction VARCHAR(50) PATH './ActivityAction/@value', ismandatory VARCHAR(50)
PATH './IsMandatory/@value', parametersource VARCHAR(50) PATH './ParameterSource/@value', parametervalue VARCHAR(256) PATH './ParameterValue/@value' ) as imp
WHERE
d.expiration_date = 'NONE'
ORDER BY
d.spec_id,
d.version_no,
d.display_name,
d.effective_date,
d.expiration_date,
d.implementationvalue;
-----------------------------------------------------------------------
Getting Error:
*Cause: Attempted to CAST to a type that is not a nested table or
VARRAY
*Action: Re-specify CAST to a nested table or VARRAY type.
Error at Line: 13 Column: 128