How to create a XMLTYPE data type in Oracle

XMLTYPE in Oracle

XMLTYPE is a data type in oracle. Oracle has made this data type for storing the XML message, this data type is made up of CLOB which can store XML data.

Let us check how to use XMLTYPE.

1)   Creating table column using XMLTYPE

CREATE TABLE XMLTEST2

(ID NUMBER,

XMLDATA XMLTYPE,

NAME VARCHAR2(50)

);

2)   Inserting data in XMLTYPE column

DECLARE

v_data   CLOB;

BEGIN

v_data:='<?xml version="1.0" encoding="UTF-8"?>

<ADDRESS>

            <ID>12</ID>

<LINE>

<H_NO>9-4-789</H_NO>

<COLONY>GAUTAM NAGAR</COLONY>

<STREET>Second</STREET>

<PIN_CODE>123456</PIN_CODE>

<CITY>Hyderabad</CITY>

<STATE>Telangana</STATE>

<COUNTRY>India</COUNTRY>

</LINE>

</ADDRESS>';

INSERT INTO XMLTEST2 VALUES (12,XMLTYPE(v_data),'ARUN KUMAR');

COMMIT;

END;

/

3)   Select data from XMLTYPE column.

  SELECT a.id,

                           UPPER (b.H_NO) H_NO,

                           UPPER (b.COLONY) COLONY,

                           UPPER (b.STREET) STREET,

                           UPPER (b.PIN_CODE) PIN_CODE,

                                                                           UPPER (b.CITY) CITY,

                                                                           UPPER (b.STATE) STATE,

                                                                           UPPER (b.COUNTRY) COUNTRY

                      FROM XMLTEST2 a,

                           XMLTABLE (

                              '/ADDRESS/LINE'

                              PASSING a.XMLDATA

                              COLUMNS                                

                                     H_NO           VARCHAR2 (1000) PATH 'H_NO',

                                      COLONY        VARCHAR2 (1000) PATH 'COLONY',

                                      STREET        VARCHAR2 (1000) PATH 'STREET',

                                      PIN_CODE      VARCHAR2 (1000) PATH 'PIN_CODE',

                                      CITY          VARCHAR2 (1000) PATH 'CITY',

                                      STATE                     VARCHAR2 (1000) PATH 'STATE',

                                      COUNTRY       VARCHAR2 (1000) PATH 'COUNTRY') b

WHERE a.ID=12; 

4)   How to select the whole XML data stored in XMLTYPE.

If you write a select query for the table which has a XMLTYPE column in it, then it will look like below.

SELECT * FROM XMLTEST2;

As you can see above we can only see the {XMLTYPE} in XMLDATA column.

Now to select the xml data we can use the getCLOBVal() function.

SELECT x.XMLDATA.getCLOBVal(),x.* FROM XMLTEST2 x;

  

5)   How to update the XML data stored in XMLTYPE column.

We can use the XML data stored in XMLTYPE column using UPDATEXML, Use the below script

UPDATE XMLTEST2 a

               SET a.NAME = 'JOHNATHON',

                        a.XMLDATA = UPDATEXML (a.XMLDATA, '/ADDRESS/LINE/COUNTRY/text()', 'NEW_YORK')

             WHERE a.id = 12;

As you can see in the below screenshot the country tag data is updated to ‘NEW_YORK’;

Thanks for reading let me know if i missed anything⌣𝨾