SQL Help using XMLTable function and XQuery with local-name() and parent
577510May 14 2007 — edited Jun 21 2007I am writing a sql to grab information from XMLType column for a report. My db is 10gR2. I am using XMLTable to build a table in SQL and can grab some of the columns successfully but am having trouble going outside of the defined context to grab the parent nodes. I am also having more trouble just grabbing the name of an element using the local-name() function defined in XQuery which I belive is fully supported in this Oracle release. I am getting an unsupported function error and do not know how else to grab the element name.
Here is a sample XML, I am querying as defined in the db column ic_object.objectxml.
<DataView xmlns:csml="http://phaseforward.com/CSML/2007-01-26" RefName="AdverseEvents">
<Title>Adverse Events</Title>
<Rule Name="OnsetEndCheck" Type="ExpressionRule">
<Description>DescripText</Description>
<TriggeringEvent>FormSubmission</TriggeringEvent>
<Switch>SwitchText</Switch>
</Rule>
<Rule Name="DurationCalc" Type="ExpressionRule">
<Description>DescripText</Description>
<TriggeringEvent>FormSubmission</TriggeringEvent>
<Switch>SwitchText</Switch>
</Rule>
</DataView>
And here is my SQL...
SELECT
i.RuleName, i.RuleType, i.RuleDescrip, i.RuleTrigger, i.RuleSwitch, i.ObjTitle, i.ObjType
FROM ic_object,
XMLTABLE (XMLNAMESPACES('http://phaseforward.com/CSML/2007-01-26' AS "csml"),
'*/csml:Rule'
PASSING ic_object.objectxml
COLUMNS
RuleName varchar2(20) PATH '@Name',
RuleType varchar2(20) PATH '@Type',
RuleDescrip varchar2(20) PATH 'csml:Description',
RuleTrigger varchar2(20) PATH 'csml:TriggeringEvent',
RuleSwitch varchar2(20) PATH 'csml:Switch',
ObjTitle varchar2(10) PATH '../csml:Title',
ObjType varchar2(10) PATH 'parent::local-name()'
) i
The following columns are working fine - i.RuleName, i.RuleType, i.RuleDescrip, i.RuleTrigger, i.RuleSwitch
However, the ObjTitle is always blank even though the parent node DataView does have a child Title.
More troubling is that I get the following error on the ObjType column (which should just display the text 'DataView')
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '/*/parent::local-name()'
I tried to isolate the use of the local-name() function and get the following error on a simple example
select
EXTRACT(ic_object.objectxml,'local-name()') rulename
from ic_object
where OBJECTID = '4be53780-b59c-4ef4-a049-bab2aecac9d4'
ORA-31012: Given XPATH expression not supported
PLEASE ADVISE. I am pretty new to XPath and have already been wading through the Oracle XML documentation library for 10gR2 without success. Thanks!