Help with Oracle xmltype code

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

flow01Commented:
?
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
sdstuberCommented:
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
j2911Author Commented:
Please see attached output.pdf.  

Also there was a mistake in my xml -  </harmonized-code>
Please ignore that.  Thanks.
Output.pdf
0
sdstuberCommented:
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

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:
You're terrific!
That worked.

Thank you so much!
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.

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.