myXML code help

j2911
j2911 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
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"

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2012
Commented:
I still don't know what functionality you're going for
but there was a ")" out of place and missing an end quote, and diff/tecr references don't apply when selecting from myxml


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,
       test_elem_compr_rsult_id,
       test_elem_compr_rsult_id,
       EXTRACTVALUE(xmlcol, '//content-description/VAL2') "content-description"
  FROM myxml
 WHERE EXTRACTVALUE(xmlcol, '//content-description/VAL2') = 'FURNITURE'

Author

Commented:
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

Author

Commented:
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'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial