Parsing XML from String Imported from SQL Server

Question

I have imported a query from SQL Server where the item is a stored XML script. It's being saved as a pyodbc item and I need to parse it as XML.

import pyodbc
import urllib
import xml.etree.ElementTree as ET


# Create connection
con = pyodbc.connect(driver="{SQL Server}",server="Server",database="Database")
cur = con.cursor()
db_cmd = "SELECT [XML] FROM [Database].[dbo].[Table] where ID = 1"
res = cur.execute(db_cmd)

for row in res.fetchall():
    print(row)

tree = ET.ElementTree(ET.fromstring(str(row)))

I keep getting this error:

Traceback (most recent call last):

  File "C:...", line 2961, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)

  File "<ipython-input-46-1e4a5c1ba170>", line 15, in <module>
    tree = ET.ElementTree(ET.fromstring(str(row)))

  File "C:...", line 1315, in XML
    parser.feed(text)

  File "<string>", line unknown
ParseError: syntax error: line 1, column 0

I'm guessing there is an issue with the XML script but I don't know enough about XML to determine what the issue is. Here is an excerpt of the script:

('<response error_code="0"><xml_root><report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
   <report_options>...;/html&gt;</html_root></response>', )

I tried saving it as a string as you can see from my code but I get the above error. How can I read in the value from SQL and save it as an XML item? The XML is redacted for privacy reasons but if more details are required, please let me know.

Please update your question and share the obfuscated XML in its entirety. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented May 12, 2021 at 19:07
Can't do that, it's Financial Information, but I have confirmed that it is correctly formatted, but correct me if I'm wrong, I think think the issue is the parenthesis before and after the two response lines? — Kardinol
– Kardinol, Commented May 12, 2021 at 19:11

davdforg · Accepted Answer · 2021-05-12 19:12:47Z

1

I think you are accidently passing a row object, rather than string to ET.fromstring(str(row))

Try:

tree = ET.ElementTree(ET.fromstring(str(row[0])))

According to the pyodbc docs, you may also be able to reference the column by name, e.g. row['XML'], rather than row[0]

edited May 12, 2021 at 19:12

answered May 12, 2021 at 19:04

davdforg

412 bronze badges

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

Comments

Yitzhak Khabinsky · Accepted Answer · 2021-05-12 19:02:59Z

0

Your XML is not well- formed:

XML

<response error_code="0"><xml_root><report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
   <report_options>...;/html&gt;</html_root></response>

Error

Parsing error: expected end of tag 'report_options' (line 3, column 20)

answered May 12, 2021 at 19:02

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

3 Comments

Luke Woodward Over a year ago

The OP does say 'The XML is redacted for privacy reasons'.

Yitzhak Khabinsky Over a year ago

I thought that the OP obfuscated just the text values, and no the XML tags.

Luke Woodward Over a year ago

I don't think that that would have given them an error at line 1 column 0. It looks to me as if the OP is getting a tuple back from the database, converting that to a string using str and attempting to parse the result of that.

Collectives™ on Stack Overflow

Parsing XML from String Imported from SQL Server

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related