Store XML File into MS SQL DB using Python

Question

My MSSQL DB table contains following structure:

create table TEMP
(
  MyXMLFile XML

)

Using Python, I a trying to load locally stored .XML file into MS SQL DB (No XML Parsing Required)

Following is Python code:

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

print("Connecting..")
# Establish a connection between Python and SQL Server
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=TEST;'
                      'Database=test;'
                      'Trusted_Connection=yes;')
print("DB Connected..")

# Get XMLFile
XMLFilePath = open('C:HelloWorld.xml')

# Create Table in DB
CreateTable = """
create table test.dbo.TEMP
(

 XBRLFile XML

)
"""

# execute create table
cursor = conn.cursor()
try:
    cursor.execute(CreateTable)
    conn.commit()
except pyodbc.ProgrammingError:
    pass
print("Table Created..")

InsertQuery = """
INSERT INTO test.dbo.TEMP (
    XBRLFile
) VALUES (?)"""

# Assign values from each row
values = (XMLFilePath)

# Execute SQL Insert Query
cursor.execute(InsertQuery, values)

# Commit the transaction
conn.commit()

# Close the database connection
conn.close()

But the code is storing the XML path in MYXMLFile column and not the XML file. I referred lxml library and other tutorials. But, I did not encountered straight forward approach to store file.

Please can anyone help me with it. I have just started working on Python.

"But the code is storing the XML path in MYXMLFile column and not the XML file." because that's what you're telling Python to do: values = (XMLFilePath) followed by cursor.execute(InsertQuery, values). You'll need to read the contents of the file into the variable. — Thom A
– Thom A ♦, Commented Nov 15, 2019 at 16:26
Thanks for the direction. I referred few articles online about it and figured it. — user2961127
– user2961127, Commented Nov 15, 2019 at 18:45

user2961127 · Accepted Answer · 2019-11-15 18:46:52Z

Here, is solution to load .XML file directly into MS SQL SB using Python.

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

print("Connecting..")
# Establish a connection between Python and SQL Server
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=TEST;'
                      'Database=test;'
                      'Trusted_Connection=yes;')
print("DB Connected..")

# Get XMLFile
XMLFilePath = open('C:HelloWorld.xml')
x = etree.parse(XBRLFilePath)          # Updated Code line
with open("FileName", "wb") as f:      # Updated Code line
    f.write(etree.tostring(x))         # Updated Code line

# Create Table in DB
CreateTable = """
create table test.dbo.TEMP
(

 XBRLFile XML

)
"""

# execute create table
cursor = conn.cursor()
try:
    cursor.execute(CreateTable)
    conn.commit()
except pyodbc.ProgrammingError:
    pass
print("Table Created..")

InsertQuery = """
INSERT INTO test.dbo.TEMP (
    XBRLFile
) VALUES (?)"""

# Assign values from each row
values = etree.tostring(x) # Updated Code line

# Execute SQL Insert Query
cursor.execute(InsertQuery, values)

# Commit the transaction
conn.commit()

# Close the database connection
conn.close()

Collectives™ on Stack Overflow

Store XML File into MS SQL DB using Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related