Solved

An Oracle xmltype question

Posted on 2013-12-08
3
587 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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