Dynamic XML

Posted on 2012-03-28
Medium Priority
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?
Question by:j2911
  • 3
  • 3
  • 2
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:
LVL 74

Expert Comment

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.

Author Comment

ID: 37778531
We don't know what the structure will be in the xml file coming in.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Expert Comment

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

Author Comment

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 (
xmlcol from test_element_compr_field_diff
where isvalid(TEST_ELEM_DIFF_RELATED_FLD_NM) = 1)
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?
LVL 74

Accepted Solution

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'

Author Closing Comment

ID: 37778815

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

624 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