Solved

Help with Oracle xmltype code

Posted on 2012-03-28
5
621 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
[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
  • 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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

617 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