Hello,
I am new in XML processing and I need to parse a CLOB column that has XML content.
XML content can differ from row to row but part I have to parse is the same - Department and Employees.
CREATE TABLE company(id NUMBER,
dept CLOB)
/
INSERT INTO company VALUES(1,
'<Company>
<Departments>
<Department>
<DeptId>10</DeptId>
<DeptName>Sales</DeptName>
<Employees>
<Employee>
<EmpId>11</EmpId>
<EmpName>Smith</EmpName>
</Employee>
<Employee>
<EmpId>12</EmpId>
<EmpName>Jones</EmpName>
</Employee>
</Employees>
</Department>
<Department>
<DeptId>20</DeptId>
<DeptName>HR</DeptName>
<Employees>
<Employee>
<EmpId>21</EmpId>
<EmpName>Harris</EmpName>
</Employee>
<Employee>
<EmpId>22</EmpId>
<EmpName>Arnold</EmpName>
</Employee>
</Employees>
</Department>
<Department>
<DeptId>30</DeptId>
<DeptName>Admin</DeptName>
<Employees />
</Department>
</Departments>
</Company>')
/
COMMIT
/
I need data in format:
DeptId DeptName EmpId EmpName
10 Sales 11 Smith
10 Sales 12 Jones
20 HR 21 Harris
20 HR 22 Arnold
30 Admin
Please help.
Thank you.