Solved

Help with Oracle xmltype code

Posted on 2012-03-28
5
594 Views
Last Modified: 2012-03-29
I have the following code I am using to extract an Oracle clob out of a field.

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


So here's my issue:

There are 3 fields and for the test data from the table "test_element_compr_field_diff".
 
How could I write the above to include the other two fields, "ID" & "Result ID" in my select statement.  They are key fields and they need to be included.
The above is only showing the results of the clob not the rest of the record.

Here are the field names and the data.

ID = 1792

Result_ID = 1492

Test_elem_diff_related_fld_nm =
(what's in the clob)

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<system>
    <deviceName>DEVICE 1</deviceName>
    <meterNumber>
        <VAL1>1234</VAL1>
        <VAL2>5678</VAL2>
    </meterNumber>
    <revenueUploadIndicatorCode>missing</revenueUploadIndicatorCode>
    </harmonized-code>
    <sourceSystemName>System 7</sourceSystemName>
    <sourceSystemServerName>
        <VAL1>1234</VAL1>
        <VAL2>5678</VAL2>
    </sourceSystemServerName>
    <softwareVersion>
        <VAL1>1</VAL1>
        <VAL2>0</VAL2>
    </softwareVersion>
    <transactionName>
        <VAL1>14</VAL1>
        <VAL2>56</VAL2>
    </transactionName>
</system>
0
Comment
Question by:j2911
  • 2
  • 2
5 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 37779310
?
with myXML as (
select id, result_id,
xmltype(TEST_ELEM_DIFF_RELATED_FLD_NM)
xmlcol from test_element_compr_field_diff
where isvalid(TEST_ELEM_DIFF_RELATED_FLD_NM) = 1)
select  id, result_id,
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37780176
first, your xml is invalid

</harmonized-code>

this is an end-tag but it has no corresponding start-tag

perhaps it was supposed to be

<harmonized-code/>


second,  your use of extractvalue doesn't appear to be correct,  you'll get an ora-19025 error

thirsd, as with one of your previous questions  what do you mean by "include"

what are the results you are looking for?  
are the two fields supposed to end up a elements within the xml?
or as additional columns? or as concatenated strings within something else? or what?

but, don't bother trying to "describe" what you want just show it.
the sample data is quite small,  what are the rows and columns you are expecting

something like this...

a, b, c, d, 1,2,3,4
w,x,y,z, 5,6,7,8
etc

assuming the query was correct, what should the output in sqlplus look like?
0
 

Author Comment

by:j2911
ID: 37781735
Please see attached output.pdf.  

Also there was a mistake in my xml -  </harmonized-code>
Please ignore that.  Thanks.
Output.pdf
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37781851
you had it, except for the transactionName part,  you needed to specify the child nodes to extract


SELECT id,
       result_id,
       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/VAL1') "Transaction Val1",
       EXTRACTVALUE(xmlcol, '/system/transactionName/VAL2') "Transaction Val2"
  FROM myxml
 WHERE EXTRACTVALUE(xmlcol, '/system/deviceName') IS NOT NULL
0
 

Author Closing Comment

by:j2911
ID: 37781912
You're terrific!
That worked.

Thank you so much!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 71
oracle rollup query 3 50
EXECUTE IMMEDIATE 5 53
case statement in where clause with not exist 15 46
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now