Solved

An Oracle xmltype question

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

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

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…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

628 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