I'm having trouble pulling attributes from a XML stored in an a XMLTYPE column. The XML primarily uses attributes as opposed to elements with a tag/value pairing. If I set the node in the first argument of the XMLTABLE function, it pulls the attribute at that level. However, when I attempt to pull attributes of child node, I'm not getting the attribute values. 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>'));
The query below will pull the attribute Name in the User element but not the attributes OpId, QId and PName in the child Project element.
SELECT x.*
FROM XML_TBL_TEST t,
XMLTABLE (--xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as "xsi"),
'/Users/User'
PASSING t.data
COLUMNS --ID FOR ORDINALITY NUMBER 'ID',
UserName VARCHAR2(30) PATH '@Name',
PName VARCHAR2(30) PATH '/Projects/Project/@PName',
QId NUMBER PATH '/Users/User/Projects/Project/@QId' DEFAULT 0,
OpId NUMBER PATH '/./Project/@OpId' DEFAULT 0) x;
The next query will pull the attributes OpId, QId and PName in the child Project element.
SELECT x.*
FROM XML_TBL_TEST t,
XMLTABLE (--xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as "xsi"),
'/Users/User/Projects/Project'
PASSING t.data
COLUMNS --UserName VARCHAR2(30) PATH '@Name',
PName VARCHAR2(30) PATH '@PName',
QId NUMBER PATH '@QId' DEFAULT 0,
OpId NUMBER PATH '@OpId' DEFAULT 0) x;
How can I pull all the attributes for any given element in the XML. I've tried a number of options with no success.
Regards, SM