How to import XML data straight to SQL server using Python?

Question

I have XML file I want to import XML data into SQL server table using Python. I know if you we want to run Python script we can use sp_execute_external_script stored procedure. I have also developed stored procedure which convert XML file to CSV file and then using Bulk Insert load it to SQL server. But is it possible to load it directly without converting to CSV file?

My XML to CSV and loading CSV to SQL server code is below:

CREATE PROCEDURE dbo.XMLParser
(
    @XMLFilePath VARCHAR(MAX),
    @CSVFilePath VARCHAR(MAX)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @a VARCHAR(MAX) = @XMLFilePath,
            @b VARCHAR(MAX) = @CSVFilePath;

    EXECUTE sp_execute_external_script @language = N'Python',
                                       @script = N'import xml.etree.ElementTree as ET
import csv

tree = ET.parse(a)
root = tree.getroot()

employee_data = open(b, "w")
csvwriter = csv.writer(employee_data)
employees_head = []

count = 0
for member in root.findall("Employee"):
    employee = []
    address_list = []
    if count == 0:
        name = member.find("Name").tag
        employees_head.append(name)
        PhoneNumber = member.find("PhoneNumber").tag
        employees_head.append(PhoneNumber)
        EmailAddress = member.find(''EmailAddress'').tag
        employees_head.append(EmailAddress)
        Address = member[3].tag
        employees_head.append(Address)
        csvwriter.writerow(employees_head)
        count = count + 1

    name = member.find("Name").text
    employee.append(name)
    PhoneNumber = member.find("PhoneNumber").text
    employee.append(PhoneNumber)
    EmailAddress = member.find("EmailAddress").text
    employee.append(EmailAddress)
    Address = member[3][0].text
    address_list.append(Address)
    City = member[3][1].text
    address_list.append(City)
    StateCode = member[3][2].text
    address_list.append(StateCode)
    PostalCode = member[3][3].text varcg
    address_list.append(PostalCode)
    employee.append(address_list)
    csvwriter.writerow(employee)
employee_data.close()',
                                       @params = N'@a varchar(max),@b varchar(max)',
                                       @a = @a,
                                       @b = @b;
    BULK INSERT dbo.Employee
    FROM 'E:\EmployeeData.csv'
    WITH
    (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'
    );
END;

Free Code · Accepted Answer · 2019-02-06 15:56:04Z

1

You could convert your XML to python list that contains dics. you loop and you insert row by row in your data base. You could also put 'sleep one second' when you insert every row.

answered Feb 6, 2019 at 15:56

Free Code

2832 silver badges9 bronze badges

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

2 Comments

Jay Desai Over a year ago

I can make a list with dics also loop it but I don't know how to insert into SQL server table. It will be helpful if you provide small example or link.

Free Code Over a year ago

cursor.execute("INSERT INTO yourtable(col1,col2) VALUES (?,?);",('value1','value2')) while value1 and value2 can retrieve when you loop from dic or even when you loop from XML parser.

Collectives™ on Stack Overflow

How to import XML data straight to SQL server using Python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related