Solved

Dynamic XML

Posted on 2012-03-28
8
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 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