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
