More data needed on "How do I query an XML file in a Clob field"

Attn Author:  sdstuber

This is a related question but my answer only allowed me to return one line.  (see http#Q_27518237)  However, I need to return all of the following.  

select extractvalue(xmltype(TEST_FIELD_NM),
'/system/deviceName', '/system/meterNumber/VAL1', '/system/meterNumber/VAL2', '/system/revenueUploadIndicatorCode', '/system/sourceSystemName', '/system/sourceSystemServerName/VAL1', '/system/sourceSystemServerName/VAL2', '/system/softwareVersion/VAL1', '/system/softwareVersion/VAL2', '/system/transactionName',) from TEST_DB_DIFF where isvalid(TEST_FIELD_NM) = 1;
rie_Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>Attn Author:  sdstuber

You should not call for a specific Expert.  Many can answer your questions.

>>I need to return all of the following.

Multiple calls to extractvalue.  One for each value.

I would also suggest using WITH:

with myXML as (
select xmltype(TEST_FIELD_NM) xmlcol from TEST_DB_DIFF)
select
extractvalue(xmlcol,'/system/deviceName'),
extractvalue(xmlcol,'/system/meterNumber/VAL1'),
...
from myXML;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rie_Author Commented:
Ok - thank you.  
0
rie_Author Commented:
I'm not sure if this is all correct but I am getting that error again.  Any suggestions?

SQL> with myXML as (
  2  select xmltype(TEST_FIELD_NM) xmlcol from TEST_DB_DIFF)
  3  select
  4  extractvalue(xmlcol,'/system/deviceName'),
  5  extractvalue(xmlcol,'/system/meterNumber/VAL1'),
  6  extractvalue(xmlcol,'/system/meterNumber/VAL2'),
  7  extractvalue(xmlcol,'/system/revenueUploadIndicatorCode'),
  8  extractvalue(xmlcol,'/system/sourceSystemName'),
  9  extractvalue(xmlcol,'/system/sourceSystemServerName/VAL1'),
 10  extractvalue(xmlcol,'/system/sourceSystemServerName/VAL2'),
 11  extractvalue(xmlcol,'/system/softwareVersion/VAL1'),
 12  extractvalue(xmlcol,'/system/softwareVersion/VAL2'),
 13  extractvalue(xmlcol,'/system/transactionName')
 14  from myXML;
ERROR:
ORA-19032: Expected XML tag , got no content
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
Same error is probably due to same cause.

You have invalid XML in some of the columns,  put your

isvalid(TEST_FIELD_NM) = 1


on the WITH clause query to ensure you're only parsing valid xml
0
rie_Author Commented:
Thank you, I wasn't sure how to incorporate that.

So both are correct - please tell me what I should do?

By the way - thank you so much to both of you.  
There is nothing out there in print that explains as well you both have.
I am very happy to have found this site.  
0
rie_Author Commented:
I chose Multiple Solutions.  Does that work for both of you?
0
sdstuberCommented:
glad we could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.