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?
 
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
 
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
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.

 
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
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.