Solved

Dynamic XML

Posted on 2012-03-28
8
487 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 77

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 77

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

820 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