Solved

An Oracle xmltype question

Posted on 2013-12-08
3
584 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

829 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