—ORA-19025: EXTRACTVALUE returns value of only one node

with t as
(
select XMLTYPE(‘<Root> <DataEmp>
<Status>1</Status>
<UserGroup>2</UserGroup>
<ShiftType>1</ShiftType>
<SurveyType>1</SurveyType>
<SurveyData />
<Skills>
<Skill>
<SkillID>1</SkillID>
<Priority>1</Priority>
</Skill>
<Skill>
<SkillID>2</SkillID>
<Priority>2</Priority>
</Skill>
</Skills>
</DataEmp>
<DataEmp>
<Status>2</Status>
<UserGroup>2</UserGroup>
<ShiftType>1</ShiftType>
<SurveyType>2</SurveyType>
<SurveyData />
<Skills>
<Skill>
<SkillID>3</SkillID>
<Priority>1</Priority>
</Skill>
<Skill>
<SkillID>4</SkillID>
<Priority>2</Priority>
</Skill>
</Skills>
</DataEmp>
</Root>’) as OBJECT_VALUE from dual)
select q.Status
, s.SkillID

from t
left join xmltable(‘Root/DataEmp’
passing t.OBJECT_VALUE
columns Status varchar2(21) path ‘Status’,
empskill XmlType path ‘Skills/Skill/SkillID’

) q
on (1=1)
left join xmltable(‘/SkillID’
passing q.empskill
columns SkillID varchar2(21) path ‘.’) s

on (1=1)

——————-OUTPUT—————–

Status    SkillID 

   1           1
   1           2
   2           3
   2           4

 

regards

Mohammad Talat

–Write comments in XMLQuery

(:Your Comment HERE:)

Example:

SELECT XMLQuery(

‘for $i in ora:view(“SCOTT”, “DEPT”)/ROW
(:Dept comment:)
return <DEPT id=”{$i/DEPTNO}” DNAME =”{$i/DNAME}” >
{for $j in ora:view(“SCOTT”, “EMP”)/ROW
where $j/DEPTNO = $i/DEPTNO
return <EMP ID=”{$j/EMPNO}” Test=”{if ($param eq “EN”) then “True” else “False”}” ></EMP>
(: Employee Comment:)
}
</DEPT>’
passing cast(‘EN’ as varchar2(10)) as “param”

RETURNING CONTENT).getstringval() a FROM DUAL;

Regards

Mohammad Talat

–Using IF Condition in XMLQUERY

SELECT XMLQuery(
‘for $i in ora:view(“SCOTT”, “DEPT”)/ROW

return <DEPT id=”{$i/DEPTNO}” DNAME =”{$i/DNAME}” >
{for $j in ora:view(“SCOTT”, “EMP”)/ROW
where $j/DEPTNO = $i/DEPTNO
return <EMP ID=”{$j/EMPNO}” Test=”{if ($param eq “EN”) then “True” else “False”}” ></EMP>
}
</DEPT>’
passing cast(‘EN’ as varchar2(10)) as “param”

RETURNING CONTENT).getstringval() a FROM DUAL;

Regards

Mohmmad Talat