14. Using XMLType Data

Oracle XMLType columns are fetched as strings by default in node-oracledb Thin and Thick modes. Note that in Thick mode, you may need to use the XMLTYPE.GETCLOBVAL() function as detailed below.

The examples below demonstrate using XMLType data with node-oracledb. The following table will be used in these examples:

CREATE TABLE xwarehouses(
    warehouse_id NUMBER,
    warehouse_spec XMLTYPE
);

14.1. Inserting XML

To insert into an XMLType column, you can directly insert a string containing the XML or use a temporary LOB, depending on the data length. For example:

const myxml =
    `<Warehouse>
     <WarehouseId>1</WarehouseId>
     <WarehouseName>Melbourne, Australia</WarehouseName>
     <Building>Owned</Building>
     <Area>2020</Area>
     <Docks>1</Docks>
     <DockType>Rear load</DockType>
     <WaterAccess>false</WaterAccess>
     <RailAccess>N</RailAccess>
     <Parking>Garage</Parking>
     <VClearance>20</VClearance>
     </Warehouse>`;

const result = await connection.execute(
 `INSERT INTO xwarehouses (warehouse_id, warehouse_spec) VALUES (:id, XMLType(:bv))`,
 { id: 1, bv: myxml }
);

14.2. Fetching XML

Fetching XML data can be done directly in node-oracledb Thin mode. This also works in Thick mode for values that are shorter than the maximum allowed length of a VARCHAR2 column:

myxmldata = await connection.execute(`SELECT warehouse_spec FROM xwarehouses
                    WHERE warehouse_id = :id`, [1]);
console.log(myxmldata);

In Thick mode, for values that exceed the maximum allowed length of a VARCHAR2 column, a CLOB must be returned by using the XMLTYPE.GETCLOBVAL() function:

myxmldata = connection.execute(`SELECT XMLTYPE.GETCLOBVAL(warehouse_spec)
                                AS mycontent FROM xwarehouses
                                WHERE warehouse_id = :id`, [1]);
console.log(myxmldata);

The CLOB can be fetched in node-oracledb as a String or Lob. LOB handling is as discussed in the section Working with CLOB, NCLOB and BLOB Data.