?
Solved

Dynamic XML

Posted on 2012-03-28
8
Medium Priority
?
493 Views
Last Modified: 2012-03-28
I have a Dynamic XML stored in an Oracle Clob and I need to query it.  
What would be the best way to query this file?
Can you point me to some good teaching material on how to do that?
0
Comment
Question by:j2911
  • 3
  • 3
  • 2
8 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37778380
Sample data and expected results would help.

By query do you want to extract node values?  Check out EXTRACTVALUE for XMLTYPE's.  you can CAST the CLOB as an XMLTYPE.

By query, do you want to search inside nodes for specific values?

Oracle Text can index and search an XML document.

Here is the generaal doc link:
http://www.oracle.com/pls/db112/homepage
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37778501
what do you mean by dynamic xml?

once the xml is in a clob it's static.

or do you mean you don't know what the structure will be?

if that's the case, parsing it will be quite difficult, if not impossible to do in a reliable manner through sql.
0
 

Author Comment

by:j2911
ID: 37778531
We don't know what the structure will be in the xml file coming in.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37778543
What are you wanting to 'query' from them?

Again, sample data and expected results help a lot.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37778552
>>> We don't know what the structure will be in the xml file coming in.

ok, that  may or may not be a problem.

do you know what you'll want to query?

are there "any" parameters you can provide?

for instance  <x> will only be a child of <y>  but you don't know where <y> might be or if <y> will even exist?

that is parseable. but if <x> might or might not exist and might or might not be a child of different parent nodes then it will be difficult-to-impossible to parse reliably
0
 

Author Comment

by:j2911
ID: 37778664
I know the names of the fields coming in and will want the data from that field.  But the path statement will be different coming in.  

Do you have to know the path to get to the field?

I've been using:

with myXML as (
select xmltype(TEST_ELEM_DIFF_RELATED_FLD_NM)
xmlcol from test_element_compr_field_diff
where isvalid(TEST_ELEM_DIFF_RELATED_FLD_NM) = 1)
select
extractvalue(xmlcol,'/system/deviceName') "DeviceName",
extractvalue(xmlcol,'/system/meterNumber/VAL1') "Meter No 1",
extractvalue(xmlcol,'/system/meterNumber/VAL2') "Meter No 2",
extractvalue(xmlcol,'/system/revenueUploadIndicatorCode') "Rev Upload",
extractvalue(xmlcol,'/system/sourceSystemName') "Source System",
extractvalue(xmlcol,'/system/sourceSystemServerName/VAL1') "Server Name 1",
extractvalue(xmlcol,'/system/sourceSystemServerName/VAL2') "Server Name 2",
extractvalue(xmlcol,'/system/softwareVersion/VAL1') "Software Ver 1",
extractvalue(xmlcol,'/system/softwareVersion/VAL2') "Software Ver 2",
extractvalue(xmlcol,'/system/transactionName') "Transaction"
from myXML
where extractvalue(xmlcol,'/system/deviceName') IS NOT NULL

But as you can see above - the path would seem to make this static not dynamic.

Any suggestions?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37778687
which paths might change?


if deviceName is unique within a doc you can use  '//deviceName'

similarly you can do that for the other values that are unique.

however you can not do that for 'VAL1' or 'VAL2' because they appear multiple times.


you could dereference them from parent nodes though if those will be known

for instance :   '//softwareVersion/VAL2'
0
 

Author Closing Comment

by:j2911
ID: 37778815
Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month16 days, 20 hours left to enroll

864 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