What I am trying to achieve is to create a table containing separate rows for all the multi item child nodes for this particular xml :
<ABCD>
<EMPLOYEE id="11" date="25-Apr-1983">
<NameDetails>
<Name NameType="a">
<NameValue>
<FirstName>ABCD</FirstName>
<Surname>PQR</Surname>
<OriginalName>TEST1</OriginalName>
<OriginalName>TEST2</OriginalName>
</NameValue>
</Name>
<Name NameType="b">
<NameValue>
<FirstName>TEST3</FirstName>
<Surname>TEST3</Surname>
</NameValue>
<NameValue>
<FirstName>TEST5</FirstName>
<MiddleName>TEST6</MiddleName>
<Surname>TEST7</Surname>
<OriginalName>JAB1</OriginalName>
</NameValue>
<NameValue>
<FirstName>HER</FirstName>
<MiddleName>HIS</MiddleName>
<Surname>LOO</Surname>
</NameValue>
</Name> <Name NameType="c">
<NameValue>
<FirstName>CDS</FirstName>
<MiddleName>DRE</MiddleName>
<Surname>QWE</Surname>
</NameValue>
<NameValue>
<FirstName>CCD</FirstName>
<MiddleName>YTD</MiddleName>
<Surname>QQA</Surname>
</NameValue>
<NameValue>
<FirstName>DS</FirstName>
<Surname>AzDFz</Surname>
</NameValue>
</Name>
</NameDetails>
</EMPLOYEE >
</ABCD>
I tried using the query :
SELECT t.personid,n.nametypeid,t.firstname,t.middlename,t.surname,t.maidenname,t.originalName
FROM xml_files p,master_nametypes n,
XMLTable(
'for $i in ADCD/Employee/NameDetails/Name/NameValue
return <row>
{
$i/../../../@id,
$i/../@NameType,
$i/FirstName,
$i/MiddleName,
$i/OriginalName
$i/Surname,
$i/MaidenName,
$i/Suffix,
$i/SingleStringName,
$i/EntityName
}
</row>'
PASSING p.filecontent
COLUMNS
personid number PATH '@id',
nametypeid VARCHAR2(255) PATH '@NameType',
firstname VARCHAR2(4000) PATH 'FirstName',
middlename VARCHAR2(4000) PATH 'MiddleName',
surname VARCHAR2(4000) PATH 'Surname',
maidenname VARCHAR2(4000) PATH 'MaidenName',
originalName VARCHAR2(4000) PATH '.'
) t where t.nametypeid = n.nametype and n.recordtype = 'Employee'
;
But this will throw error when there is multiple child nodes like 'ORIGINALNAME' under 'NAMEVALUE' node. How can I retreive these values as well in separate rows based on their parent nodes. Can somebody help me to correct this query. Any help would be appreciated.
Try this:
SQL> SELECT t.personid, t.firstname, t.middlename,
2 t.surname,t.maidenname,
3 replace(replace(t.originalName, '<OriginalName>'),
4 '</OriginalName>', ' ') originalName
5 FROM xml_files p,
6 XMLTABLE (
7 --'ABCD/EMPLOYEE/NameDetails/Name/NameValue'
8 'for $i in ABCD/EMPLOYEE/NameDetails/Name/NameValue
9 return <row>
10 {$i/../../../@id}
11 {$i/../@NameType}
12 {$i/FirstName}{$i/MiddleName}{$i/OriginalName}
13 {$i/Surname}{$i/MaidenName}
14 </row>'
15 PASSING p.filecontent
16 COLUMNS
17 personid NUMBER PATH '@id',
18 nametypeid VARCHAR2(255) PATH '@NameType',
19 firstname VARCHAR2(4000) PATH 'FirstName',
20 middlename VARCHAR2(4000) PATH 'MiddleName',
21 surname VARCHAR2(4000) PATH 'Surname',
22 maidenname VARCHAR2(4000) PATH 'MaidenName',
23 originalName XMLTYPE PATH 'OriginalName'
24 ) t;
PERSONID FIRSTNAME MIDDLENAME SURNAME MAIDENNAME ORIGINALNAME
---------- ---------- ----------- -------- ----------- ------------
11 ABCD PQR TEST1 TEST2
11 TEST3 TEST3
11 TEST5 TEST6 TEST7 JAB1
11 HER HIS LOO
11 CDS DRE QWE
11 CCD YTD QQA