I had a previous post where I wasn't able to grab attributes from child elements because there were multiple attributes. The provided solution worked great for the immediate issue but when testing deeper levels of the XML that had multiple attributes, I get a Cartesian join Re: Unable to pull XML attributes in different elements with XMLTABLE function). I would expect this based on how the query is written but I'm looking for help on best approaches/examples, and how to get the data I'm looking for.
Basically, I have a nested elements with one or more attributes where we could also have multiple child elements. I need to repeat the parent elements when there is multiple child elements with multiple attributes. Also, how would I handle this in the case where one of the child elements may not exist for the different users. The test XML may help explain.
I'm working on Oracle database version 11.2.0.3.0.
Here are the create/insert statements:
CREATE TABLE XML_TBL_TEST
(
id NUMBER,
data XMLTYPE
);
INSERT INTO XML_TBL_TEST
VALUES (1, xmltype ('<Users>
<User Name="NAME1">
<Projects>
<Project OpId="100" QId="101" PName="PName1">
<Groups>
<Group GName="GName1">
<Items>
<Item OName="OName1" Type="Type1">
<Ops>
<Op PType="PType1" Material="Mat1" Series="Series1" PStyle="PStyle1">
<Options>
<Option Name="Name1" Value="V1" />
<Option Name="Name2" Value="V2" />
<Option Name="Name3" Value="V3" />
</Options>
</Op>
<Op PType="PType2" Material="Mat2" Series="Series2" PStyle="PStyle2">
<Options>
<Option Name="Name4" Value="V4" />
<Option Name="Name5" Value="V5" />
<Option Name="Name6" Value="V6" />
</Options>
</Op>
</Ops>
</Item>
<Item OName="OName2" Type="Type2">
</Item>
</Items>
</Group>
<Group GName="GName2">
<Item OName="OName3" Type="Type3">
</Item>
</Group>
</Groups>
</Project>
<Project OpId="200" QId="201" PName="PName2">
</Project>
</Projects>
</User>
<User Name="NAME2">
</User>
<User Name="NAME3">
</User>
</Users>'));
Below is the query getting all the data I need but also creating a Cartesian join.
SELECT /*+ no_merge(usrname) */
usrname.UserName
, proj.*
, grp.*
, item.*
, opening.*
, options.*
FROM XML_TBL_TEST t,
XMLTABLE (--xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as "xsi"),
'/Users/User'
PASSING t.data
COLUMNS UserName VARCHAR2(30) PATH '@Name',
Projects XMLType PATH 'Projects/Project',
Groupings XMLType PATH 'Projects/Project/Groups/Group',
Items XMLType PATH 'Projects/Project/Groups/Group/Items/Item',
Openings XMLType PATH 'Projects/Project/Groups/Group/Items/Item/Ops/Op',
Options XMLType PATH 'Projects/Project/Groups/Group/Items/Item/Ops/Op/Options/Option') usrname
, XMLTABLE('/Project'
PASSING usrname.Projects
COLUMNS QId NUMBER PATH '@QId',
OpId NUMBER PATH '@OpId',
PName VARCHAR2(30) PATH '@PName') proj
, XMLTABLE('/Group'
PASSING usrname.Groupings
COLUMNS GName VARCHAR2(30) PATH '@GName') grp
, XMLTABLE('/Item'
PASSING usrname.Items
COLUMNS OName VARCHAR2(30) PATH '@OName',
OType VARCHAR2(30) PATH '@Type') item
, XMLTABLE('/Op'
PASSING usrname.Openings
COLUMNS PType VARCHAR2(30) PATH '@PType',
Material VARCHAR2(30) PATH '@Material',
ProductSeries VARCHAR2(30) PATH '@Series',
PStyle VARCHAR2(30) PATH '@PStyle') opening
, XMLTABLE('/Option'
PASSING usrname.Options
COLUMNS OptName VARCHAR2(30) PATH '@Name',
OptValue VARCHAR2(30) PATH '@Value') options
WHERE t.id = 1;
Below is another attempt but this puts all attributes into the same virtual column.
SELECT x.*
FROM xml_tbl_test t
, XMLTABLE('for $i in /Users/User
return element r {$i/@Name
, $i/Projects/Project/@OpId
, $i/Projects/Project/@QId
, $i/Projects/Project/Groups/Group/@GName
, $i/Projects/Project/Groups/Group/Items/Item/@OName
, $i/Projects/Project/Groups/Group/Items/Item/@Type
, $i/Projects/Project/Groups/Group/Items/Item/Ops/Op/@PType
, $i/Projects/Project/Groups/Group/Items/Item/Ops/Op/@Material
, $i/Projects/Project/Groups/Group/Items/Item/Ops/Op/@Series
, $i/Projects/Project/Groups/Group/Items/Item/Ops/Op/@PStyle
, $i/Projects/Project/Groups/Group/Items/Item/Ops/Op/Options/Option/@Value}'
PASSING t.data
COLUMNS UserName VARCHAR2(15) PATH '@Name'
, OpId VARCHAR2(15) PATH '@OpId'
, QId VARCHAR2(15) PATH '@QId'
, GName VARCHAR2(15) PATH '@GName'
, OName VARCHAR2(15) PATH '@OName'
, IType VARCHAR2(15) PATH '@Type'
, PType VARCHAR2(15) PATH '@PType'
, Material VARCHAR2(15) PATH '@Material'
, Series VARCHAR2(15) PATH '@Series'
, PStyle VARCHAR2(15) PATH '@PStyle'
, OptValue VARCHAR2(15) PATH '@Value'
) x
WHERE t.id = 1;
Result from above query:
| USERNAME | OPID | QID | GNAME | ONAME | ITYPE | PTYPE | MATERIAL | SERIES | PSTYLE | OPTVALUE |
| NAME1 | 100200 | 101201 | GName1GName2 | OName1OName2 | Type1Type2 | PType1PType2 | Mat1Mat2 | Series1Series2 | PStyle1PStyle2 | V1V2V3V4V5V6 |
| NAME2 | | | | | | | | | | |
| NAME3 | | | | | | | | | | |
Expected result:
| USERNAME | OPID | QID | GNAME | ONAME | ITYPE | PTYPE | MATERIAL | SERIES | PSTYLE | OPTVALUE |
| NAME1 | 100 | 101 | GName1 | OName1 | Type1 | PType1 | Mat1 | Series1 | PStyle1 | V1 |
| NAME1 | 100 | 101 | GName1 | OName1 | Type1 | PType1 | Mat1 | Series1 | PStyle1 | V2 |
| NAME1 | 100 | 101 | GName1 | OName1 | Type1 | PType1 | Mat1 | Series1 | PStyle1 | V3 |
| NAME1 | 200 | 201 | GName2 | OName2 | Type2 | PType2 | Mat2 | Series2 | PStyle2 | V4 |
| NAME1 | 200 | 201 | GName2 | OName2 | Type2 | PType2 | Mat2 | Series2 | PStyle2 | V5 |
| NAME1 | 200 | 201 | GName2 | OName2 | Type2 | PType2 | Mat2 | Series2 | PStyle2 | V6 |
| NAME2 | | | | | | | | | | |
| NAME3 | | | | | | | | | | |
Any help would be appreciated. I've scoured the internet, read many docs but haven't came across this level of complexity thus far.