Solved

An Oracle xmltype question

Posted on 2013-12-08
3
567 Views
Last Modified: 2013-12-08
I have a clob column RCRD_CNTNT_TX in an oracle 11g v11.2 table TRNSCN_ERR_RCRD_LOG. The column contains complete xml docs. There are two DealerNumber elements, nested. The outer is in the bar namespace, the inner is in the foo (default) namespace. I want to write a select statement based on the value of the inner DealerNumber.

<?xml version="1.0" encoding="UTF-8"?>
<ns2:FinanceDealList xmlns="foo" xmlns:ns2="bar">
<ns2:FinanceDeal>
<ns2:DealerNumber>
<DealerNumber>5361</DealerNumber>...

I have a query which returns exactly one row, where the clob contains xml.

I tried hardcoding the outer namespace and the path:

SELECT xmltype(ERROR.RCRD_CNTNT_TX).extract('//ns2:FinanceDealList/ns2:FinanceDeal/ns2:DealerNumber/DealerNumber/text()').getStringVal()
FROM TRNSCN_ERR_RCRD_LOG ERROR...

but that complains about an invalid token -- the colon I think, but I don't know what to do about it.

I also tried this:

select xmltype(ERROR.RCRD_CNTNT_TX).extract('//DealerNumber/text()').getStringVal() FROM TRNSCN_ERR_RCRD_LOG ERROR...

I expected this second query to raise an error because there are two DealerNumber elements, the outer one containing an xml fragment, the inner containing the text node I want. But it actually returns null even through the rest of the query, as I mentioned, does return one row with xml in the clob.

Can anyone show me how to write a select based on the value of the the inner, default-namespace DealerNumber?

I also want to avoid hardcoding any namespace.
0
Comment
Question by:FelineConspiracy
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 39705093
This would be the normal way I think:
...extract('//ns2:DealerNumber/DealerNumber/text()', 'xmlns="foo" xmlns:ns2="bar"').getStringVal() from ...

Open in new window

But I guess you would regard that as hard-coding namespaces so try this:
...extract('//*[local-name()="DealerNumber"]/*[local-name()="DealerNumber"]/text()').getStringVal() from ...

Open in new window

0
 

Author Comment

by:FelineConspiracy
ID: 39705235
Thank you! That looks right, I will give a try in the AM.
0
 

Author Closing Comment

by:FelineConspiracy
ID: 39705239
Defeated again by the Oracle documentation, saved again by someone who can provide clear, complete examples. Thanks again.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now