Dynamic XML

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?
j2911Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
sdstuberCommented:
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
j2911Author Commented:
We don't know what the structure will be in the xml file coming in.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Again, sample data and expected results help a lot.
0
sdstuberCommented:
>>> 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
j2911Author Commented:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
j2911Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.