Convert NCHAR to Hex when using XMLElement

User_1U2TW

Hello All,

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 "ncharcol" and "nvarchar2col" values into Hexadecimal ?

cormaco

Here is an example:

select xmlelement(nvarchar2col,utl_raw.cast_to_raw(N'nvarchar2col')) from dual

<NVARCHAR2COL>006E007600610072006300680061007200320063006F006C</NVARCHAR2COL>

User_1U2TW

Thanks for your reply -

I don't want to process each and every element to check if it is containing NCHAR values or not. Is there any other way to process output of VARRAY and convert it to XML with HEX values of NCHAR column ?

cormaco

Here is an example that works with your varray_datetime table, I used a second DTOBJECT for testing:

insert into varray_datetime values (1, vDTOBJECT( 
    DTOBJECT(1, to_date('2020-06-08', 'yyyy-mm-dd'), 'nchar', 'nvarcharcol'),
    DTOBJECT(2, sysdate, 'nchar_2', 'nvarcharcol_2')
));

select 
    id,
    xmlelement(vdate,
        xmlelement(vdtobject,
            xmlagg(
                xmlelement(dtobject,
                    xmlforest(
                        project_no,
                        datecol,
                        utl_raw.cast_to_raw(nvarchar2col) as nvarchar2col, 
                        utl_raw.cast_to_raw(ncharcol)     as ncharcol 
                    )
                )
            )
        )
    )   
from varray_datetime vd,table(vd.vdate)
group by id;

XML-File:
<VDATE>
	<VDTOBJECT>
		<DTOBJECT>
			<PROJECT_NO>1</PROJECT_NO>
			<DATECOL>2020-06-08</DATECOL>
			<NVARCHAR2COL>006E00760061007200630068006100720063006F006C</NVARCHAR2COL>
			<NCHARCOL>006E0063006800610072002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020</NCHARCOL>
		</DTOBJECT>
		<DTOBJECT>
			<PROJECT_NO>2</PROJECT_NO>
			<DATECOL>2022-02-03</DATECOL>
			<NVARCHAR2COL>006E00760061007200630068006100720063006F006C005F0032</NVARCHAR2COL>
			<NCHARCOL>006E0063006800610072005F00320020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020</NCHARCOL>
		</DTOBJECT>
	</VDTOBJECT>
</VDATE>

User_1U2TW

Thank you again cormaco

This solution will solve the problem partially. In this solution - I need to find out the each column type before executing a query. Is there any parameter/settings etc available for XMLElement which validate all the columns of DTOBJECT and give value in HEX for NCHAR ?

cormaco

I'm quite sure there is no such setting.

Looking up the datatype of a column is what I do, when I write a SQL statement.

User_1U2TW

ok, is is possible for you to share that SQL Statement also ?

cormaco

You misunderstood me. I meant that is generally how I work, not a specific statemenent.