Solved

xmltype help

Posted on 2012-03-26
7
494 Views
Last Modified: 2012-03-28
Hi,

I have two sql statements that I need to combine but I am not sure how to do that.  One is an  xmltype statement.  I need to add the scn.test_id as the key field to the xml on the bottom.

select
scn.test_id,
diff.test_elem_diff_related_fld_nm
from
test_set_e left  
test_sceen scn
where
scn.test_id = left.test_id
and diff.test_elem_compr_fld_nm = fn.field_name


==========================================================



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

Thanks
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
  • 4
  • 3
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37767039
can you post sample data and expected results?

are you asking to have the xml results modified with an extra child node? or an extra column or what?
0
 

Author Comment

by:j2911
ID: 37767178
I am looking a way to add the extra column "scn.test_id" field added to the xml query.
0
 

Author Comment

by:j2911
ID: 37767199
I'm not sure how to create a regular query with the xml query and pull in more data.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37767200
what is the correlation  between them?

your xml comes from this table:  test_element_compr_field_diff

your test_id comes from these tables:  test_set_e & test_sceen


your first query references diff, but I don't see a clear connection.

please post sample data and expected results
0
 

Author Comment

by:j2911
ID: 37767910
Sorry for the delay.  I'm having problems gathering data today - we're having problems with the database.
 

The xml is coming off of a blob field in the field diff.test_elem_diff_related_fld_nm that you can see in the second part of the data.  

The following may be better to understand.

select
scn.test_id,
diff.test_elem_diff_related_fld_nm
from
test_set_element left,
compare_result pair,
compr_field_diff diff,
test_sceen scn
where
pair.test_elem_compr_left_side_id = left.test_id
and scn.test_id = left.test_id
and diff.test_elem_compr_rsult_ID = pair.test_elem_compr_rsult_ID
and pair.run_id = cr.run_id
0
 

Author Closing Comment

by:j2911
ID: 37776905
I'm going to close this up because I cannot gather the data for now.  We are in development and the database that I was using is undergoing major changes and I cannot provide you with what is needed.  Thank you for trying.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37776922
I appreciate the points but if I my posts weren't helpful and you're unable to pursue it further at this time then I recommend deleting the question.

My "trying" is sort of irrelevant.

If, I was helpful though, even if you can't implement things quite yet, then we can leave it as is.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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ā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

717 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