Working with XMLTYPE¶
cx_Oracle has a major new release under a new name and homepage python-oracledb.
New projects should install python-oracledb instead of cx_Oracle.
Oracle XMLType columns are fetched as strings by default. This is currently
limited to the maximum length of a
VARCHAR2 column. To return longer XML
values, they must be queried as LOB values instead.
The examples below demonstrate using XMLType data with cx_Oracle. 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 as shown:
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 bound
clob = connection.createlob(cx_Oracle.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 simply for values that are shorter than the length of a VARCHAR2 column, as shown:
cursor.execute("select xml_data from xml_table where id = :id", id=1) xml_data, = cursor.fetchone() print(xml_data) # will print the string that was originally stored
For values that exceed the length of a VARCHAR2 column, a CLOB must be returned
instead by using the function
XMLTYPE.GETCLOBVAL() as shown:
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 or a string can be returned instead of a CLOB. See Using CLOB and BLOB Data for more information about processing LOBs.