# 13. Using XMLTYPE Data

Oracle XMLType columns are fetched as strings by default in Thin and Thick mode. Note that in Thick mode you may need to use `XMLTYPE.GETCLOBVAL()` as discussed below.

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

    CREATE TABLE xml_table (
        id NUMBER,
        xml_data SYS.XMLTYPE
    );

Inserting into the table can be done by simply binding a string:

    xml_data = """<?xml version="1.0"?>
            <customer>
                <name>John Smith</name>
                <Age>43</Age>
                <Designation>Professor</Designation>
                <Subject>Mathematics</Subject>
            </customer>"""
    cursor.execute("insert into xml_table values (:id, :xml)",
                   id=1, xml=xml_data)

This approach works with XML strings up to 1 GB in size. For longer strings, a temporary CLOB must be created using `Connection.createlob()` and cast when bound:

    clob = connection.createlob(oracledb.DB_TYPE_CLOB)
    clob.write(xml_data)
    cursor.execute("insert into xml_table values (:id, sys.xmltype(:xml))",
                   id=2, xml=clob)

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

    cursor.execute("select xml_data from xml_table where id = :id", id=1)
    xml_data, = cursor.fetchone()
    print(xml_data)

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

    cursor.execute("""
            select xmltype.getclobval(xml_data)
            from xml_table
            where id = :id""", id=1)
    clob, = cursor.fetchone()
    print(clob.read())

The LOB that is returned can be streamed, as shown. Alternatively a string can be returned. See Using CLOB and BLOB Data for more information.