Home » Developer & Programmer » JDeveloper, Java & XML » XMLTYPE Record is not getting update
icon6.gif  XMLTYPE Record is not getting update [message #324937] Wed, 04 June 2008 09:30 Go to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

Hi,


I created a test table from an existing table with XMLTYPE Datatype and inserted record into test table as below.

CREATE TABLE TEST
(USERID VARCHAR2(50),
USERNAME VARCHAR2(100),
ITEMDETAIL XMLTYPE);

INSERT INTO TEST (USERID,USERNAME)
SELECT * FROM DETAIL;

I am trying below query to generate an XMLTYPE record on the basis of USERID and trying to update ITEMDETAIL and not getting any values updated. It shows count of rows updated but no values are getting updated.

UPDATE test u
SET ITEMDETAIL = (SELECT XMLELEMENT("d",
XMLATTRIBUTES(e.ITEMTYPE AS "ID", e.ITEMVALUE)) AS "Element"
FROM ITEMRECORD e
WHERE e.USERID=u.USERID);

I want record for ITEMDETAIL to be inserted in the format

<r>
<d id=\"key_1\"><![CDATA[val 1]]></d>
<d id=\"key_2\"><![CDATA[val 2]]></d>
<d id=\"key_3\"><![CDATA[val 3]]></d>
</r>

where Key_1,Key_2,Key_3 are the column names and val 1,val 2, val 3 are the column values.

Please can anyone help me with this.

Thanks and Reagrds
Amit Verma
XMLTYPE Record is not getting update [message #324938 is a reply to message #324937] Wed, 04 June 2008 09:31 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member


Hi,


I created a test table from an existing table with XMLTYPE Datatype and inserted record into test table as below.

CREATE TABLE TEST
(USERID VARCHAR2(50),
USERNAME VARCHAR2(100),
ITEMDETAIL XMLTYPE);

INSERT INTO TEST (USERID,USERNAME)
SELECT * FROM DETAIL;

I am trying below query to generate an XMLTYPE record on the basis of USERID and trying to update ITEMDETAIL and not getting any values updated. It shows count of rows updated but no values are getting updated.

UPDATE test u
SET ITEMDETAIL = (SELECT XMLELEMENT("d",
XMLATTRIBUTES(e.ITEMTYPE AS "ID", e.ITEMVALUE)) AS "Element"
FROM ITEMRECORD e
WHERE e.USERID=u.USERID);

I want record for ITEMDETAIL to be inserted in the format

<r>
<d id=\"key_1\"><![CDATA[val 1]]></d>
<d id=\"key_2\"><![CDATA[val 2]]></d>
<d id=\"key_3\"><![CDATA[val 3]]></d>
</r>

where Key_1,Key_2,Key_3 are the column names and val 1,val 2, val 3 are the column values.

Please can anyone help me with this.

Thanks and Reagrds
Amit Verma
Re: XMLTYPE Record is not getting update [message #324944 is a reply to message #324937] Wed, 04 June 2008 10:02 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Previous Topic: SELECT XML DATA IN CLOB
Next Topic: problem with reading XML attributes and elements (split)
Goto Forum:
  


Current Time: Thu Mar 28 05:00:05 CDT 2024