Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with Oracle xmltype code

Posted on 2012-03-28
5
Medium Priority
?
634 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 21

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 74

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 74

Accepted Solution

by:
sdstuber earned 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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 how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

927 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