Problem with passing XML element from Oracle database to ElementTree (Python xml parser)-CodePudding

i want to get an XML file out of a database, manipulate it with ElementTree and then insert it into another database. Getting the file works just fine, i can print it in it's entirety. However, whenever i try to get it through the parser it returns the error "no element found".

Here is the code:

import cx_Oracle
import xml.etree.ElementTree as et

cx_Oracle.init_oracle_client(lib_dir=r"here and there")

try:
    dsn_tns_source = cx_Oracle.makedsn('censored for obvious reasons')
    con_source = cx_Oracle.connect(cx_Oracle.makedsn('same here'))

except cx_Oracle.DatabaseError as err:
    print("Connection DB error:", err)


try:
    cur_source = con_source.cursor()

    source_select = cur_source.execute("working SELECT")

    print(source_select)

    for row in source_select:
        x = row[(len(row) - 1)]  # This is the XML
        print("source_row: ", x)
        tree = et.parse(x)
        root = tree.getroot()
        print(root)
        print(et.tostring(root, encoding='utf-8').decode('utf-8'))
    for col in cur_source.description:
        print("source_col: ", col)

Apparently I am not passing "x" correctly, however, the entire XML should be help in that variable at the point of calling it. Most tutorials only show how to insert local files so i thought simply using the variable would be sufficient. The error message is the following:

Traceback (most recent call last):
  File "Z:\basler_benchmark\main.py", line 24, in <module>
     tree = et.parse(x)
  File "C:\Python\lib\xml\etree\ElementTree.py", line 1229, in parse
     tree.parse(source, parser)
  File "C:\Python\lib\xml\etree\ElementTree.py", line 580, in parse
     self._root = parser._parse_whole(source)
xml.etree.ElementTree.ParseError: no element found: line 1, column 0

CodePudding user response：

.parse() specifically parses files.

If x is a string, use .fromstring():

root = et.fromstring(x)

If x is something else, it must be turned into a string first. For cx_Oracle.LOB objects, calling .read() should do the trick:

root = et.fromstring(x.read())

CodePudding user response：

The error was

a) That what I had in x was not an XML file but a LOB and that i had to import it with

et.fromstring(x.read())

like Tomalak answered/commented and

b) that I didn't realize that you HAVE TO iterate over the entire thing in order to be able to use .text to get what's inside the field/tag.

So the solution looks like this:

    for row in source_select:
        x = row[(len(row) - 1)]
        tree = et.ElementTree(et.fromstring(x.read()))
        root = tree.getroot()
        for aref in root.iter('name_of_tag'):
            print(aref.text)