Link to home
Start Free TrialLog in
Avatar of j2911
j2911

asked on

myXML code help

Hi Experts,

How do I set this up to work with other tables?  

Also can you point me to some kind of training module that might help me understand this code?


Original that works:

with myXML as (
select TEST_ELEM_COMPR_FLD_DIFF_ID, TEST_ELEM_COMPR_RSULT_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 TEST_ELEM_COMPR_FLD_DIFF_ID, TEST_ELEM_COMPR_RSULT_ID,
extractvalue(xmlcol,'//content-description/VAL2') "content-description"
from myXML
where extractvalue(xmlcol,'//content-description/VAL2')='FURNITURE'



Tried this and it doesn't work:

with myXML as (
select diff.TEST_ELEM_COMPR_FLD_DIFF_ID, diff.TEST_ELEM_COMPR_RSULT_ID, tecr.TEST_ELEM_COMPR_RSULT_ID,  
xmltype(TEST_ELEM_DIFF_RELATED_FLD_NM)
xmlcol from test_element_compr_field_diff diff, test_element_compare_result tecr
where isvalid(TEST_ELEM_DIFF_RELATED_FLD_NM) = 1) and tecr.TEST_ELEM_COMPR_RSULT_ID =
select diff.TEST_ELEM_COMPR_FLD_DIFF_ID, diff.TEST_ELEM_COMPR_RSULT_ID, tecr.TEST_ELEM_COMPR_RSULT_ID, diff.TEST_ELEM_COMPR_RSULT_ID
extractvalue(xmlcol,'//content-description/VAL2') "content-description"
from myXML
where extractvalue(xmlcol,'//content-description/VAL2')='FURNITURE'

Thank you.
Avatar of Sean Stuber
Sean Stuber
what are you trying to do in the second query?

what is this clause supposed to be?

and tecr.TEST_ELEM_COMPR_RSULT_ID =

also I think you're missing a comma before

EXTRACTVALUE       (xmlcol,'//content-description/VAL2') "content-description"
Avatar of j2911
j2911

ASKER

I'm trying to join 2 tables using this xmltype code.  I have several other tables I need to join as well so I am trying to start with 2 tables at first to get the idea of what is needed.

Correction (but I'm still not getting this to work):  

with myXML as (
select diff.TEST_ELEM_COMPR_FLD_DIFF_ID, diff.TEST_ELEM_COMPR_RSULT_ID, tecr.TEST_ELEM_COMPR_RSULT_ID,  
xmltype(TEST_ELEM_DIFF_RELATED_FLD_NM)
xmlcol from test_element_compr_field_diff diff, test_element_compare_result tecr
where isvalid(TEST_ELEM_DIFF_RELATED_FLD_NM) = 1) and tecr.TEST_ELEM_COMPR_RSULT_ID = diff.TEST_ELEM_COMPR_RSULT_ID
select diff.TEST_ELEM_COMPR_FLD_DIFF_ID, diff.TEST_ELEM_COMPR_RSULT_ID, tecr.TEST_ELEM_COMPR_RSULT_ID, diff.TEST_ELEM_COMPR_RSULT_ID,
extractvalue(xmlcol,'//content-description/VAL2') "content-description"
from myXML
where extractvalue(xmlcol,'//content-description/VAL2')='FURNITURE
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of j2911
j2911

ASKER

WITH myxml
     AS (SELECT diff.test_elem_compr_fld_diff_id,
                diff.test_elem_compr_rsult_id,
                tecr.test_elem_compr_rsult_id,
                xmltype(test_elem_diff_related_fld_nm) xmlcol
           FROM test_element_compr_field_diff diff, test_element_compare_result tecr
          WHERE     isvalid(test_elem_diff_related_fld_nm) = 1
                AND tecr.test_elem_compr_rsult_id = diff.test_elem_compr_rsult_id)
SELECT test_elem_compr_fld_diff_id,
       test_elem_compr_rsult_id,
       EXTRACTVALUE(xmlcol, '//content-description/VAL2') "content-description"
  FROM myxml
 WHERE EXTRACTVALUE(xmlcol, '//content-description/VAL2') = 'FURNITURE'

When I run this it says:

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 11 Column: 29
Avatar of j2911
j2911

ASKER

This worked but I am still trying to figure out how to combine the query I currently have with myxml query.  I have a start though - thank you.


WITH myxml
     AS (SELECT diff.test_elem_compr_fld_diff_id,
                diff.test_elem_compr_rsult_id,
                tecr.test_elem_compr_rsult_id,
                xmltype(test_elem_diff_related_fld_nm) xmlcol
           FROM test_element_compr_field_diff diff, test_element_compare_result tecr
          WHERE isvalid(test_elem_diff_related_fld_nm) = 1
            AND tecr.test_elem_compr_rsult_id = diff.test_elem_compr_rsult_id)
SELECT tecr.test_elem_compr_rsult_id, test_elem_compr_fld_diff_id,
       EXTRACTVALUE(xmlcol, '//content-description/VAL2') "content-description"
  FROM myxml, test_element_compare_result tecr
 WHERE EXTRACTVALUE(xmlcol, '//content-description/VAL2') = 'FURNITURE'