0

Test

CREATE OR REPLACE TYPE DTOBJECT AS OBJECT
( 
  project_no NUMBER(2),
  datecol DATE,
  ncharcol nchar(50),
  nvarchar2col nvarchar2(50)
);

CREATE OR REPLACE TYPE vDTOBJECT AS VARRAY(500) OF DTOBJECT;
CREATE TABLE qatest.varray_datetime (id int, vdate vDTOBJECT);

INSERT INTO qatest.varray_datetime VALUES (1, vDTOBJECT( DTOBJECT(1, 
    to_date('2020-06-08', 'yyyy-mm-dd'), 'nchar', 'nvarcharcol')
    ))

SQLPlus output

 SQL> SELECT id, XMLElement("vdate", vdate) FROM qatest.varray_datetime;

    ID
----------
XMLELEMENT("VDATE",VDATE)
-----------------------------------------------------------------------------------------
     1
<vdate><VDTOBJECT><DTOBJECT><PROJECT_NO>1</PROJECT_NO><DATECOL>2020-06-08T00:00:00.000000000</DATECOL><NCHARCOL>nchar                         </NCHARCOL><NVARCHAR2COL>nvarcharcol</
NVARCHAR2COL></DTOBJECT></VDTOBJECT></vdate>


SQL>

Question How can I convert NCHAR column value into Hexadecimal when fetching VARRAY in XML format ? The XMLElement should convert NCHAR value from nchar to 6E6368617220202020202020202020202020202020202020202020202020. Also NVARCHAR value should be converted from NVARCHAR2COL to 6E76617263686172636F6C.

Expected Output

<vdate><VDTOBJECT><DTOBJECT><PROJECT_NO>1</PROJECT_NO><DATECOL>2020-06-08T00:00:00.000000000</DATECOL><NCHARCOL>6E6368617220202020202020202020202020202020202020202020202020</NCHARCOL><NVARCHAR2COL>6E76617263686172636F6C</NVARCHAR2COL></DTOBJECT></VDTOBJECT></vdate>
akg
  • 43
  • 1
  • 5
  • Please edit your question to include the expected result, and explain what you mean by converting to hexadecimal. – Alex Poole Feb 02 '22 at 12:24
  • @AlexPoole - Modified the expected result – akg Feb 03 '22 at 04:56
  • What is your national character set? The default/standard AL16UTF16 would convert that as `006E0063006800610072...` but if you have something else you might get the result you want. (And what is your database character set; wondering if you need to be using nchar/nvarchar2 at all). – Alex Poole Feb 03 '22 at 12:51

0 Answers0