Home » Developer & Programmer » JDeveloper, Java & XML » how to append root node to xml data in oracle (oracle 10.2.0.1)
how to append root node to xml data in oracle [message #461912] Tue, 22 June 2010 02:04 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
How to append root node (as ROWSET) to a variable in pl/sql.
I have the below xml data in a variable. I want to append a root node to the xml data exists in the variable.
I have the below data in a variable.
<ROW>
  <TABLESPACE_T>
    <VERS_MAJOR>1</VERS_MAJOR>
    <VERS_MINOR>0 </VERS_MINOR>
  </TABLESPACE_T>
</ROW>


finally i'm expecting the below xml data.

<ROWSET>
 <ROW>
  <TABLESPACE_T>
    <VERS_MAJOR>1</VERS_MAJOR>
    <VERS_MINOR>0 </VERS_MINOR>
  </TABLESPACE_T>
 </ROW>
</ROWSET>


Which function is useful?

Regards,
Madhavi.
Re: how to append root node to xml data in oracle [message #461934 is a reply to message #461912] Tue, 22 June 2010 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
<ROWSET>||your_variable||</ROWSET>

Regards
Michel
Re: how to append root node to xml data in oracle [message #461937 is a reply to message #461934] Tue, 22 June 2010 03:24 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
I have tried but get error.

my procedure and error is

SQL> CREATE OR REPLACE PROCEDURE SYS.Database_Import2
  2      AUTHID CURRENT_USER
  3  AS
  4     V_Objtype VARCHAR2(200);
  5     V_Cnt NUMBER := 1;
  6     V_Xml XMLTYPE;
  7     V_Xml1 XMLTYPE;
  8  BEGIN
  9    FOR I IN ( SELECT Rowid, Obj_Id, Repos FROM sys.DDI_REPOS_T WHERE OBJ_ID <25 ORDER BY Obj_Id)
 LOOP
 10       SELECT Obj_Tp_Nm INTO V_Objtype FROM DDI_OBJ_TP_T WHERE Obj_Tp_Id IN (SELECT Obj_Tp_Id FRO
M DDI_OBJ_DEF_T WHERE Obj_Id = I.Obj_Id);
 11       DBMS_OUTPUT.PUT_LINE('Object type is ' || V_Objtype); 
 12              LOOP
 13                 SELECT EXTRACT (I.REPOS, 'ROWSET/ROW['||V_CNT ||']') INTO V_XML FROM DUAL;
 14                 EXIT WHEN V_XML IS NULL; 
 15                 V_XML1 := <ROWSET>||V_XML||</ROWSET> ;
 16                 INSERT INTO ABC VALUES (I.OBJ_ID, V_XML1); 
 17                V_CNT := V_CNT+1; 
 18               END LOOP;
 19               V_cnt := 1;
 20    END LOOP;
 21  END;
 22  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE SYS.DATABASE_IMPORT2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/26    PLS-00103: Encountered the symbol "<" when expecting one of the
         following:
         ( - + case mod new not null <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> avg
         count current exists max min prior sql stddev sum variance
         execute forall merge time timestamp interval date
         <a string literal with character set specification>
         <a number> <a single-quoted SQL string> pipe
         <an alternatively-quoted string literal with character set
         specification>
         <an alternatively-quoted S


Regards,
Madhavi.
Re: how to append root node to xml data in oracle [message #461940 is a reply to message #461937] Tue, 22 June 2010 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have tried but get error.

OK, my fault I should post a complete valid syntax, I thought you will be able to understand.
It is:
'<ROWSET>'||your_variable||'</ROWSET>'

Regards
Michel
Re: how to append root node to xml data in oracle [message #461943 is a reply to message #461940] Tue, 22 June 2010 03:37 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
i have tried like that also but another error.

V_XML1 := '<ROWSET>'||V_XML||'</ROWSET>' ;
PLS-00306: wrong number or types of arguments in call to '||'


i used two single quotes also like below.
V_XML1 := ''<ROWSET>''||V_XML||''</ROWSET>'' ;

error is..

ERROR line 15, col 35, ending_line 15, ending_col 35, Found '>', Expecting: ;   -or-   OR   -or-   AND   -or-   ||   -or-   /   -or-   **   -or-   (+)   -or-   %  .  [   -or-   :  @  CASE  CONNECT_BY_ROOT  decimal number  EXISTS  FALSE  float  integer  NEW  PRIOR  SQL  TRUE   -or-   (  *  +  -  AT  CONTINUE  CURSOR  DAY  identifier  MOD  MODEL  MULTISET  NOT  NULL  REM  string  THE  WHEN  YEAR

[Updated on: Tue, 22 June 2010 03:39]

Report message to a moderator

Re: how to append root node to xml data in oracle [message #461945 is a reply to message #461943] Tue, 22 June 2010 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use conversion: declare v_xml as varchar2(something) and use xmltype() function to convert the result of the concatenation.

Regards
Michel
Re: how to append root node to xml data in oracle [message #461950 is a reply to message #461945] Tue, 22 June 2010 04:01 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Or:
SELECT XMLELEMENT
("ROWSET",
  XMLType('
  <ROW>
    <TABLESPACE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>0 </VERS_MINOR>
    </TABLESPACE_T>
  </ROW>')
 )xmlstr FROM dual;

XMLSTR
----------------------
<ROWSET>
  <ROW>
    <TABLESPACE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>0 </VERS_MINOR>
    </TABLESPACE_T>
  </ROW>
</ROWSET>

[Updated on: Tue, 22 June 2010 04:04]

Report message to a moderator

Re: how to append root node to xml data in oracle [message #461957 is a reply to message #461950] Tue, 22 June 2010 04:30 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is more correct.

Regards
Michel
Previous Topic: an unknown problem (I really need help)
Next Topic: Associations in ADF
Goto Forum:
  


Current Time: Thu Mar 28 16:04:53 CDT 2024