Solved

Help with Oracle xmltype code

Posted on 2012-03-28
5
605 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 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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