Two quick functions for converting a Pandas DataFrame to XML, then to an executable query string for SQL Server.

dataframe_to_SQLServer.py

import xml.etree.ElementTree as ET

from collections import OrderedDict

def gen_XML(dFrame, subelement):
    """Function to generate an XML string from a provided pandas DataFrame
    
    # Arguments
        dFrame (pandas.DataFrame): DataFrame to be converted to an XML string
        subelement (str): Subelement of root to be used...can be just about anything
        
    # Returns
        String in XML format based on rows from dFrame
    """
    root = ET.Element('root')
    
    for i in list(dFrame.index):
       ET.SubElement(root, subelement, OrderedDict([(x, str(dFrame[x].loc[i])) for x in dFrame.columns]))
    
    return ET.tostring(root, encoding='us-ascii', method='xml').decode('utf-8')

def gen_XML_query(xmlstr, schema, table, subelement, columndata, truncate=False):
    """Function to generate SQL Server pass-through query to upload an XML string
    
    # Arguments
        xmlstr (str): XML formatted string to load (gen_XML function can do this)
        schema (str): Schema of the SQL Server table to be loaded
        table (str): Table name to be loaded
        subelement (str): Subelement of the XML string
        columndata (dict): Dict or OrderedDict of column names and data types matching the table design in SQL Server
        truncate (bool): If True, will truncate the SQL Server table prior to inserting new data
        
    # Returns
        Pass-through query that can be sent to MS SQL Server (str)
    """
    insertstr = ''
    columnstr = ''
    returnstr = ''
    
    for key in columndata.keys():
        if list(columndata.keys()).index(key) == 0:
            insertstr += key
            columnstr += key + ' ' + columndata[key]
        else:
            insertstr += ',' + key
            columnstr += ',' + key + ' ' + columndata[key]
            
    if truncate:
        returnstr += 'TRUNCATE TABLE {}.{};'.format(schema, table)
    
    returnstr += """DECLARE @idoc int
                            ,@doc nvarchar(max);
                            
                    SET @doc = '{0}'
                    
                    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
                    
                    INSERT INTO {1}.{2} ({3})
                    
                    SELECT {3}
                    FROM OPENXML (@idoc, '/root/{4}', 0)
                    WITH ({5});
                """.format(xmlstr, schema, table, insertstr, subelement, columnstr)
    
    return returnstr