Updating xmltype column with existing rowset and row tags from 1 table to another table

user8195117

Hi All,

I have a XMLTYPE column in source table with rowset and row tags. Updating this column data to another clob column of another column adds another set of rowset and row tags and results in 2 sets of rowsets and row tags. Is there any way... we can avoid addition of the 2nd set. Please help

<?xml version="1.0"?>

<ROWSET>

  <ROW>

    <XML_DATA>

      <ROWSET>

        <ROW>

          <XML_DATA>

            </XML_DATA>

        </ROW>

      </ROWSET>

    </XML_DATA>

  </ROW>

</ROWSET>

user8195117

Hi odie_63,

The Update works with paranthesis while converting to CLOB

UPDATE tab2

SET XML_DATA = (

  SELECT (XML_DATA).getclobval()

  FROM tab1

  WHERE SEQ_NUM = tab2.SEQ_NUM

  AND file_id = tab2.file_id

)

WHERE file_id = p_file_id

AND seq_num = p_SEQ_NUM;

user8195117

there is a refcursor while selecting data from destination table and this causes an extra rowset and row tags. Is there a way to avoid these tags

Jason_(A_Non)

Please share some SQL and/or PL/SQL that demonstrates your problem, including how the tables are setup.  We can't see your screen or what you are trying/doing so the chance of you receiving helpful information is low.  Also include your version of Oracle as output by

select * from v$version;

user8195117

Jason,

Here is the version information

Oracle Database 11g Release 11.2.0.4.0 - 64bit

PL/SQL Release 11.2.0.4.0.

Here I am selecting the XML from tab1. The data in XML_DATA in tab1 has <rowset> and <row> tags.

OPEN l_refcursor FOR

                  SELECT XML_DATA

                    FROM tab1

                   WHERE     SEQ_NUM = p_SEQ_NUM

                         AND file_id = p_file_id;

Again the refcursor has added another set of <rowset> and <row> tags. 

l_xml := XMLTYPE (l_refcursor);

UPDATE tab2

SET XML_DATA = l_xml.getClobVal ()

WHERE file_id = p_file_id

AND seq_num = p_SEQ_NUM;

odie_63

user8195117 wrote:Here I am selecting the XML from tab1. The data in XML_DATA in tab1 has <rowset> and <row> tags. 
OPEN l_refcursor FOR SELECT XML_DATA FROM tab1 WHERE SEQ_NUM = p_SEQ_NUM AND file_id = p_file_id;Again the refcursor has added another set of <rowset> and <row> tags. l_xml := XMLTYPE (l_refcursor);UPDATE tab2SET XML_DATA = l_xml.getClobVal ()WHERE file_id = p_file_idAND seq_num = p_SEQ_NUM;

I don't really understand what you're trying to do.

Do you want to update TAB2.XML_DATA with the value of TAB1.XML_DATA, for the same (FILE_ID, SEQ_NUM) ?

If so, you have to fetch from the cursor (which doesn't have to be a ref cursor anyway), get XML_DATA value then update TAB2 with that value.

You don't even need PL/SQL for that.

A single UPDATE or MERGE statement will do just fine.

UPDATE tab2SET XML_DATA = (  SELECT XML_DATA.getclobval()  FROM tab1  WHERE SEQ_NUM = tab2.SEQ_NUM  AND file_id = tab2.file_id)WHERE file_id = p_file_idAND seq_num = p_SEQ_NUM;

user8195117

Hi odie_63,

The Update works with paranthesis while converting to CLOB

UPDATE tab2

SET XML_DATA = (

  SELECT (XML_DATA).getclobval()

  FROM tab1

  WHERE SEQ_NUM = tab2.SEQ_NUM

  AND file_id = tab2.file_id

)

WHERE file_id = p_file_id

AND seq_num = p_SEQ_NUM;