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

Question

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

Tomalak · Accepted Answer · 2022-04-11 13:02:40Z

1

.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())

edited Apr 11, 2022 at 13:02

answered Apr 11, 2022 at 10:06

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Knolfuns Over a year ago

when i use fromstring i get: TypeError: a bytes-like object is required, not 'cx_Oracle.LOB' that's why i assumed it was actually a file and not a string. it works just fine with BeautifulSoup, however i was told not to use it due to an aversion towards non-standard libraries.

Tomalak Over a year ago

@Knolfuns You said, "this is XML", so I assumed x was an XML string. When you print(x.read()), what gets printed?

Knolfuns Over a year ago

oh dear lord, i might have massively confused things. if i pull it from the database could it be a lob and not an xml? when i use print(x.read()) i get the whole thing (<abc1><def1></def1></abc1>) printed in console. sorry for the confusion, i'm quite new to working with files located in oracle dbs.

Tomalak Over a year ago

@Knolfuns Well it already told you it was a LOB ("a bytes-like object is required, not 'cx_Oracle.LOB'"). I don't know if a cx_Oracle.LOB is a file-like object, but it does not seem like it, despite the .read() method. When you try et.fromstring(x.read()), it should work, I think? Whether that's the most elegant way to do it, I don't know either. For reasonably small XML sizes it should hardly make any difference, though.

Knolfuns Over a year ago

it kinda works but after i use getroot() i get the structure of the xml but everything appears to be empty. nothing inside the child tags.

|

Knolfuns · Accepted Answer · 2022-04-11 13:03:05Z

1

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)

edited Apr 11, 2022 at 13:03

answered Apr 11, 2022 at 12:53

Knolfuns

1891 gold badge2 silver badges13 bronze badges

1 Comment

Tomalak Over a year ago

You can say root = et.fromstring(x.read()), there is no need to construct an ElementTree instance first. You can use .iterfind('./some/xpath/expression') to single out specific nodes.

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related