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;
Who is Participating?
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)
from myXML;
rie_Author Commented:
Ok - thank you.  
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;
ORA-19032: Expected XML tag , got no content
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
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.  
rie_Author Commented:
I chose Multiple Solutions.  Does that work for both of you?
glad we could help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.