Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

An Oracle xmltype question

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
FelineConspiracy
Asked:
FelineConspiracy
  • 2
1 Solution
 
Robert SchuttSoftware EngineerCommented:
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
 
FelineConspiracyAuthor Commented:
Thank you! That looks right, I will give a try in the AM.
0
 
FelineConspiracyAuthor Commented:
Defeated again by the Oracle documentation, saved again by someone who can provide clear, complete examples. Thanks again.
0

Featured Post

Independent Software Vendors: 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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now