—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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s