 XMLTYPE Record is not getting update [message #324937] |
Wed, 04 June 2008 09:30  |
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  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
