Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Help using XMLTable function and XQuery with local-name() and parent

577510May 14 2007 — edited Jun 21 2007
I 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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 19 2007
Added on May 14 2007
3 comments
11,738 views