Forum Stats

  • 3,825,758 Users
  • 2,260,558 Discussions
  • 7,896,662 Comments

Discussions

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

user8195117
user8195117 Member Posts: 56 Red Ribbon
edited Jul 23, 2020 12:32PM in XML DB

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>

Best Answer

  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Jul 23, 2020 12:32PM Answer ✓

    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;

Answers

  • user8195117
    user8195117 Member Posts: 56 Red Ribbon
    edited Jul 17, 2020 12:40PM

    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)
    Jason_(A_Non) Member Posts: 2,095 Silver Trophy
    edited Jul 17, 2020 1:01PM

    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
    user8195117 Member Posts: 56 Red Ribbon
    edited Jul 18, 2020 10:28PM

    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
    odie_63 Member Posts: 8,492 Silver Trophy
    edited Jul 20, 2020 7:42AM
    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
    user8195117 Member Posts: 56 Red Ribbon
    edited Jul 23, 2020 12:32PM Answer ✓

    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;