Forum Stats

  • 3,825,053 Users
  • 2,260,461 Discussions
  • 7,896,399 Comments

Discussions

Convert NCHAR to Hex when using XMLElement

User_1U2TW
User_1U2TW Member Posts: 11 Green Ribbon

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 ?

Comments

  • cormaco
    cormaco Member Posts: 1,940 Silver Crown

    Here is an example:

    select xmlelement(nvarchar2col,utl_raw.cast_to_raw(N'nvarchar2col')) from dual
    
    <NVARCHAR2COL>006E007600610072006300680061007200320063006F006C</NVARCHAR2COL>
    
  • User_1U2TW
    User_1U2TW Member Posts: 11 Green Ribbon

    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
    cormaco Member Posts: 1,940 Silver Crown
    edited Feb 3, 2022 11:16AM

    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
    User_1U2TW Member Posts: 11 Green Ribbon

    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
    cormaco Member Posts: 1,940 Silver Crown

    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
    User_1U2TW Member Posts: 11 Green Ribbon

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

  • cormaco
    cormaco Member Posts: 1,940 Silver Crown

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