Converting CSV to XML with Hierarchies using Python to feed through API
<p>I've seen a couple of answers of how to convert a csv (or SQL table) to XML, but I haven't seen one that includes hierarchies and isn't overtly complicated. I need to map a csv file into a pre-existing XML format to feed it to an API using Python. I can already send a valid XML to the website, but I'm having issues converting the csv to the XML in the first place.</p>

<p>My CSV has this format:</p>

<pre><code>OrganizationName,OrdNum,OrdType,OrderTMSStatus,FreightTerms,IsPrePayment,ScheduledEarlyPickup,ScheduledEarlyDelivery,WeightValue,uom,WeightBase,uom2,WeightValue3,uom4,WeightBase5,uom6,VolumeValue,uom7,VolumeBase,uom8,VolumeValue9,uom10,VolumeBase11,uom12,TotalPieceCount,TotalHandlingUnitCount,IsInPlanning,AreTotalsOverridden,CurrencyValue,uom13,CurrencyBase,uom14,IsHot,IsHazmat,BillingStatus,IntegrationStatus,OriginLocNum,OrganizationName15,TradingPartnerNum,TradingPartnerType,LocNum,LocationType,IsActive,IsBillTo,IsRemitTo,IsCorporate,AddrName,Addr1,CityName,StateCode,CountryISO2,PostalCode,CalendarName,CalendarAppointmentName,AllowsHazmat,IsDeliveryAptRequired,IsPickupAptRequired,DestinationLocNum,OrganizationName16,TradingPartnerNum17,TradingPartnerType18,LocNum19,LocationType20,IsActive21,IsBillTo22,IsRemitTo23,IsCorporate24,AddrName25,Addr126,CityName27,StateCode28,CountryISO229,PostalCode30,CalendarName31,CalendarAppointmentName32,AllowsHazmat33,IsDeliveryAptRequired34,IsPickupAptRequired35,OrganizationName36,TradingPartnerNum37,TradingPartnerName,TradingPartnerType38,IsActive39,OrdLineNum,WeightValue40,uom41,WeightBase42,uom43,WeightValue44,uom45,WeightBase46,uom47,VolumeValue48,uom49,VolumeBase50,uom51,VolumeValue52,uom53,VolumeBase54,uom55,PieceCount,HandlingUnitCount,IsHazmat56
My-Organization,PythonTest1,Planning,New,PPD,FALSE,3/17/2016 13:30,3/21/2016 20:00,30000,Lb,30000,Lb,30000,Lb,30000,Lb,2100,CuFt,2100,CuFt,2100,CuFt,2100,CuFt,2100,26,FALSE,FALSE,0,USD,0,USD,FALSE,FALSE,New,New,DC_OH,My-Organization,Test,Client,DC_OH,ShipReceive,TRUE,FALSE,FALSE,FALSE,DC_OH,--,Hamilton,OH,US,45014,Mon-Fri-8-5,24/7 Appointment,FALSE,FALSE,FALSE,CZ_906,My-Organization,Test,Client,CZ_906,ShipReceive,TRUE,FALSE,FALSE,FALSE,7-ELEVEN CDC C/O GENESIS LOGISTICS,--,Santa Fe Springs,CA,US,90670,Mon-Fri-8-5,24/7 Appointment,FALSE,FALSE,FALSE,My-Organization,Test,Test,Client,TRUE,1,30000,Lb,30000,Lb,30000,Lb,30000,Lb,2100,CuFt,2100,CuFt,2100,CuFt,2100,CuFt,1170,26,FALSE
My-Organization,PythonTest2,Planning,New,PPD,FALSE,3/16/2016 14:00,3/21/2016 21:00,25000,Lb,25000,Lb,25000,Lb,25000,Lb,2300,CuFt,2300,CuFt,2300,CuFt,2300,CuFt,2300,26,FALSE,FALSE,0,USD,0,USD,FALSE,FALSE,New,New,DC_KY,My-Organization,Test,Client,DC_KY,ShipReceive,TRUE,FALSE,FALSE,FALSE,DC_KY,--,Florence,KY,US,41042,Mon-Fri-8-5,24/7 Appointment,FALSE,FALSE,FALSE,CZ_906,My-Organization,Test,Client,CZ_906,ShipReceive,TRUE,FALSE,FALSE,FALSE,7-ELEVEN CDC C/O GENESIS LOGISTICS,--,Santa Fe Springs,CA,US,90670,Mon-Fri-8-5,24/7 Appointment,FALSE,FALSE,FALSE,My-Organization,Test,Test,Client,TRUE,1,25000,Lb,25000,Lb,25000,Lb,25000,Lb,2300,CuFt,2300,CuFt,2300,CuFt,2300,CuFt,1170,26,FALSE
</code></pre>

<p>This is how it should look:</p>

<pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;
&lt;ns1:OrderData xmlns:ns1="http://schemas.3gtms.com/tms/v1/tns" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
&lt;Orders&gt;
    &lt;Order&gt;
        &lt;OrganizationName&gt;My-Organization&lt;/OrganizationName&gt;
        &lt;OrdNum&gt;PythonTest1&lt;/OrdNum&gt;
        &lt;OrdType&gt;Planning&lt;/OrdType&gt;
        &lt;OrderTMSStatus&gt;New&lt;/OrderTMSStatus&gt;
        &lt;FreightTerms&gt;PPD&lt;/FreightTerms&gt;
        &lt;IsPrePayment&gt;false&lt;/IsPrePayment&gt;
        &lt;ScheduledEarlyPickup&gt;2016-03-17T13:30:00.000&lt;/ScheduledEarlyPickup&gt;
        &lt;ScheduledEarlyDelivery&gt;2016-03-21T20:00:00.000&lt;/ScheduledEarlyDelivery&gt;
        &lt;TotalGrossWeight&gt;
            &lt;WeightValue uom="Lb"&gt;30000&lt;/WeightValue&gt;
            &lt;WeightBase uom="Lb"&gt;30000&lt;/WeightBase&gt;
        &lt;/TotalGrossWeight&gt;
        &lt;TotalNetWeight&gt;
            &lt;WeightValue uom="Lb"&gt;30000&lt;/WeightValue&gt;
            &lt;WeightBase uom="Lb"&gt;30000&lt;/WeightBase&gt;
        &lt;/TotalNetWeight&gt;
        &lt;TotalGrossVolume&gt;
            &lt;VolumeValue uom="CuFt"&gt;2100&lt;/VolumeValue&gt;
            &lt;VolumeBase uom="CuFt"&gt;2100&lt;/VolumeBase&gt;
        &lt;/TotalGrossVolume&gt;
        &lt;TotalNetVolume&gt;
            &lt;VolumeValue uom="CuFt"&gt;2100&lt;/VolumeValue&gt;
            &lt;VolumeBase uom="CuFt"&gt;2100&lt;/VolumeBase&gt;
        &lt;/TotalNetVolume&gt;
        &lt;TotalPieceCount&gt;2100&lt;/TotalPieceCount&gt;
        &lt;TotalHandlingUnitCount&gt;26&lt;/TotalHandlingUnitCount&gt;
        &lt;IsInPlanning&gt;false&lt;/IsInPlanning&gt;
        &lt;AreTotalsOverridden&gt;false&lt;/AreTotalsOverridden&gt;
        &lt;FreightValue&gt;
            &lt;CurrencyValue uom="USD"&gt;0&lt;/CurrencyValue&gt;
            &lt;CurrencyBase uom="USD"&gt;0&lt;/CurrencyBase&gt;
        &lt;/FreightValue&gt;
        &lt;IsHot&gt;false&lt;/IsHot&gt;
        &lt;IsHazmat&gt;false&lt;/IsHazmat&gt;
        &lt;BillingStatus&gt;New&lt;/BillingStatus&gt;
        &lt;IntegrationStatus&gt;New&lt;/IntegrationStatus&gt;
        &lt;OriginLocNum&gt;DC_OH&lt;/OriginLocNum&gt;
        &lt;OriginLoc&gt;
            &lt;OrganizationName&gt;My-Organization&lt;/OrganizationName&gt;
            &lt;TradingPartnerNum&gt;Test&lt;/TradingPartnerNum&gt;
            &lt;TradingPartnerType&gt;Client&lt;/TradingPartnerType&gt;
            &lt;LocNum&gt;DC_OH&lt;/LocNum&gt;
            &lt;LocationType&gt;ShipReceive&lt;/LocationType&gt;
            &lt;IsActive&gt;true&lt;/IsActive&gt;
            &lt;IsBillTo&gt;false&lt;/IsBillTo&gt;
            &lt;IsRemitTo&gt;false&lt;/IsRemitTo&gt;
            &lt;IsCorporate&gt;false&lt;/IsCorporate&gt;
            &lt;AddrName&gt;DC_OH&lt;/AddrName&gt;
            &lt;Addr1&gt;--&lt;/Addr1&gt;
            &lt;CityName&gt;Hamilton&lt;/CityName&gt;
            &lt;StateCode&gt;OH&lt;/StateCode&gt;
            &lt;CountryISO2&gt;US&lt;/CountryISO2&gt;
            &lt;PostalCode&gt;45014&lt;/PostalCode&gt;
            &lt;CalendarName&gt;Mon-Fri-8-5&lt;/CalendarName&gt;
            &lt;CalendarAppointmentName&gt;24/7 Appointment&lt;/CalendarAppointmentName&gt;
            &lt;AllowsHazmat&gt;false&lt;/AllowsHazmat&gt;
            &lt;IsDeliveryAptRequired&gt;false&lt;/IsDeliveryAptRequired&gt;
            &lt;IsPickupAptRequired&gt;false&lt;/IsPickupAptRequired&gt;
        &lt;/OriginLoc&gt;
        &lt;DestinationLocNum&gt;CZ_906&lt;/DestinationLocNum&gt;
        &lt;DestinationLoc&gt;
            &lt;OrganizationName&gt;My-Organization&lt;/OrganizationName&gt;
            &lt;TradingPartnerNum&gt;Test&lt;/TradingPartnerNum&gt;
            &lt;TradingPartnerType&gt;Client&lt;/TradingPartnerType&gt;
            &lt;LocNum&gt;CZ_906&lt;/LocNum&gt;
            &lt;LocationType&gt;ShipReceive&lt;/LocationType&gt;
            &lt;IsActive&gt;true&lt;/IsActive&gt;
            &lt;IsBillTo&gt;false&lt;/IsBillTo&gt;
            &lt;IsRemitTo&gt;false&lt;/IsRemitTo&gt;
            &lt;IsCorporate&gt;false&lt;/IsCorporate&gt;
            &lt;AddrName&gt;7-ELEVEN CDC C/O GENESIS LOGISTICS&lt;/AddrName&gt;
            &lt;Addr1&gt;--&lt;/Addr1&gt;
            &lt;CityName&gt;Santa Fe Springs&lt;/CityName&gt;
            &lt;StateCode&gt;CA&lt;/StateCode&gt;
            &lt;CountryISO2&gt;US&lt;/CountryISO2&gt;
            &lt;PostalCode&gt;90670&lt;/PostalCode&gt;
            &lt;CalendarName&gt;Mon-Fri-8-5&lt;/CalendarName&gt;
            &lt;CalendarAppointmentName&gt;24/7 Appointment&lt;/CalendarAppointmentName&gt;
            &lt;AllowsHazmat&gt;false&lt;/AllowsHazmat&gt;
            &lt;IsDeliveryAptRequired&gt;false&lt;/IsDeliveryAptRequired&gt;
            &lt;IsPickupAptRequired&gt;false&lt;/IsPickupAptRequired&gt;
        &lt;/DestinationLoc&gt;
        &lt;Client&gt;
            &lt;OrganizationName&gt;My-Organization&lt;/OrganizationName&gt;
            &lt;TradingPartnerNum&gt;Test&lt;/TradingPartnerNum&gt;
            &lt;TradingPartnerName&gt;Test&lt;/TradingPartnerName&gt;
            &lt;TradingPartnerType&gt;Client&lt;/TradingPartnerType&gt;
            &lt;IsActive&gt;true&lt;/IsActive&gt;
        &lt;/Client&gt;
        &lt;OrderLines&gt;
            &lt;OrderLine&gt;
                &lt;OrdLineNum&gt;1&lt;/OrdLineNum&gt;
                &lt;WeightGross&gt;
                    &lt;WeightValue uom="Lb"&gt;30000&lt;/WeightValue&gt;
                    &lt;WeightBase uom="Lb"&gt;30000&lt;/WeightBase&gt;
                &lt;/WeightGross&gt;
                &lt;WeightNet&gt;
                    &lt;WeightValue uom="Lb"&gt;30000&lt;/WeightValue&gt;
                    &lt;WeightBase uom="Lb"&gt;30000&lt;/WeightBase&gt;
                &lt;/WeightNet&gt;
                &lt;VolumeGross&gt;
                    &lt;VolumeValue uom="CuFt"&gt;2100&lt;/VolumeValue&gt;
                    &lt;VolumeBase uom="CuFt"&gt;2100&lt;/VolumeBase&gt;
                &lt;/VolumeGross&gt;
                &lt;VolumeNet&gt;
                    &lt;VolumeValue uom="CuFt"&gt;2100&lt;/VolumeValue&gt;
                    &lt;VolumeBase uom="CuFt"&gt;2100&lt;/VolumeBase&gt;
                &lt;/VolumeNet&gt;
                &lt;PieceCount&gt;1170&lt;/PieceCount&gt;
                &lt;HandlingUnitCount&gt;26&lt;/HandlingUnitCount&gt;
                &lt;IsHazmat&gt;false&lt;/IsHazmat&gt;
            &lt;/OrderLine&gt;
        &lt;/OrderLines&gt;
    &lt;/Order&gt;
    &lt;Order&gt;
        &lt;OrganizationName&gt;My-Organization&lt;/OrganizationName&gt;
        &lt;OrdNum&gt;PythonTest2&lt;/OrdNum&gt;
        &lt;OrdType&gt;Planning&lt;/OrdType&gt;
        &lt;OrderTMSStatus&gt;New&lt;/OrderTMSStatus&gt;
        &lt;FreightTerms&gt;PPD&lt;/FreightTerms&gt;
        &lt;IsPrePayment&gt;false&lt;/IsPrePayment&gt;
        &lt;ScheduledEarlyPickup&gt;2016-03-16T14:00:00.000&lt;/ScheduledEarlyPickup&gt;
        &lt;ScheduledEarlyDelivery&gt;2016-03-21T21:00:00.000&lt;/ScheduledEarlyDelivery&gt;
        &lt;TotalGrossWeight&gt;
            &lt;WeightValue uom="Lb"&gt;25000&lt;/WeightValue&gt;
            &lt;WeightBase uom="Lb"&gt;25000&lt;/WeightBase&gt;
        &lt;/TotalGrossWeight&gt;
        &lt;TotalNetWeight&gt;
            &lt;WeightValue uom="Lb"&gt;25000&lt;/WeightValue&gt;
            &lt;WeightBase uom="Lb"&gt;25000&lt;/WeightBase&gt;
        &lt;/TotalNetWeight&gt;
        &lt;TotalGrossVolume&gt;
            &lt;VolumeValue uom="CuFt"&gt;2300&lt;/VolumeValue&gt;
            &lt;VolumeBase uom="CuFt"&gt;2300&lt;/VolumeBase&gt;
        &lt;/TotalGrossVolume&gt;
        &lt;TotalNetVolume&gt;
            &lt;VolumeValue uom="CuFt"&gt;2300&lt;/VolumeValue&gt;
            &lt;VolumeBase uom="CuFt"&gt;2300&lt;/VolumeBase&gt;
        &lt;/TotalNetVolume&gt;
        &lt;TotalPieceCount&gt;2300&lt;/TotalPieceCount&gt;
        &lt;TotalHandlingUnitCount&gt;26&lt;/TotalHandlingUnitCount&gt;
        &lt;IsInPlanning&gt;false&lt;/IsInPlanning&gt;
        &lt;AreTotalsOverridden&gt;false&lt;/AreTotalsOverridden&gt;
        &lt;FreightValue&gt;
            &lt;CurrencyValue uom="USD"&gt;0&lt;/CurrencyValue&gt;
            &lt;CurrencyBase uom="USD"&gt;0&lt;/CurrencyBase&gt;
        &lt;/FreightValue&gt;
        &lt;IsHot&gt;false&lt;/IsHot&gt;
        &lt;IsHazmat&gt;false&lt;/IsHazmat&gt;
        &lt;BillingStatus&gt;New&lt;/BillingStatus&gt;
        &lt;IntegrationStatus&gt;New&lt;/IntegrationStatus&gt;
        &lt;OriginLocNum&gt;DC_KY&lt;/OriginLocNum&gt;
        &lt;OriginLoc&gt;
            &lt;OrganizationName&gt;My-Organization&lt;/OrganizationName&gt;
            &lt;TradingPartnerNum&gt;Test&lt;/TradingPartnerNum&gt;
            &lt;TradingPartnerType&gt;Client&lt;/TradingPartnerType&gt;
            &lt;LocNum&gt;DC_KY&lt;/LocNum&gt;
            &lt;LocationType&gt;ShipReceive&lt;/LocationType&gt;
            &lt;IsActive&gt;true&lt;/IsActive&gt;
            &lt;IsBillTo&gt;false&lt;/IsBillTo&gt;
            &lt;IsRemitTo&gt;false&lt;/IsRemitTo&gt;
            &lt;IsCorporate&gt;false&lt;/IsCorporate&gt;
            &lt;AddrName&gt;DC_KY&lt;/AddrName&gt;
            &lt;Addr1&gt;--&lt;/Addr1&gt;
            &lt;CityName&gt;Florence&lt;/CityName&gt;
            &lt;StateCode&gt;KY&lt;/StateCode&gt;
            &lt;CountryISO2&gt;US&lt;/CountryISO2&gt;
            &lt;PostalCode&gt;41042&lt;/PostalCode&gt;
            &lt;CalendarName&gt;Mon-Fri-8-5&lt;/CalendarName&gt;
            &lt;CalendarAppointmentName&gt;24/7 Appointment&lt;/CalendarAppointmentName&gt;
            &lt;AllowsHazmat&gt;false&lt;/AllowsHazmat&gt;
            &lt;IsDeliveryAptRequired&gt;false&lt;/IsDeliveryAptRequired&gt;
            &lt;IsPickupAptRequired&gt;false&lt;/IsPickupAptRequired&gt;
        &lt;/OriginLoc&gt;
        &lt;DestinationLocNum&gt;CZ_906&lt;/DestinationLocNum&gt;
        &lt;DestinationLoc&gt;
            &lt;OrganizationName&gt;My-Organization&lt;/OrganizationName&gt;
            &lt;TradingPartnerNum&gt;Test&lt;/TradingPartnerNum&gt;
            &lt;TradingPartnerType&gt;Client&lt;/TradingPartnerType&gt;
            &lt;LocNum&gt;CZ_906&lt;/LocNum&gt;
            &lt;LocationType&gt;ShipReceive&lt;/LocationType&gt;
            &lt;IsActive&gt;true&lt;/IsActive&gt;
            &lt;IsBillTo&gt;false&lt;/IsBillTo&gt;
            &lt;IsRemitTo&gt;false&lt;/IsRemitTo&gt;
            &lt;IsCorporate&gt;false&lt;/IsCorporate&gt;
            &lt;AddrName&gt;7-ELEVEN CDC C/O GENESIS LOGISTICS&lt;/AddrName&gt;
            &lt;Addr1&gt;--&lt;/Addr1&gt;
            &lt;CityName&gt;Santa Fe Springs&lt;/CityName&gt;
            &lt;StateCode&gt;CA&lt;/StateCode&gt;
            &lt;CountryISO2&gt;US&lt;/CountryISO2&gt;
            &lt;PostalCode&gt;90670&lt;/PostalCode&gt;
            &lt;CalendarName&gt;Mon-Fri-8-5&lt;/CalendarName&gt;
            &lt;CalendarAppointmentName&gt;24/7 Appointment&lt;/CalendarAppointmentName&gt;
            &lt;AllowsHazmat&gt;false&lt;/AllowsHazmat&gt;
            &lt;IsDeliveryAptRequired&gt;false&lt;/IsDeliveryAptRequired&gt;
            &lt;IsPickupAptRequired&gt;false&lt;/IsPickupAptRequired&gt;
        &lt;/DestinationLoc&gt;
        &lt;Client&gt;
            &lt;OrganizationName&gt;My-Organization&lt;/OrganizationName&gt;
            &lt;TradingPartnerNum&gt;Test&lt;/TradingPartnerNum&gt;
            &lt;TradingPartnerName&gt;Test&lt;/TradingPartnerName&gt;
            &lt;TradingPartnerType&gt;Client&lt;/TradingPartnerType&gt;
            &lt;IsActive&gt;true&lt;/IsActive&gt;
        &lt;/Client&gt;
        &lt;OrderLines&gt;
            &lt;OrderLine&gt;
                &lt;OrdLineNum&gt;1&lt;/OrdLineNum&gt;
                &lt;WeightGross&gt;
                    &lt;WeightValue uom="Lb"&gt;25000&lt;/WeightValue&gt;
                    &lt;WeightBase uom="Lb"&gt;25000&lt;/WeightBase&gt;
                &lt;/WeightGross&gt;
                &lt;WeightNet&gt;
                    &lt;WeightValue uom="Lb"&gt;25000&lt;/WeightValue&gt;
                    &lt;WeightBase uom="Lb"&gt;25000&lt;/WeightBase&gt;
                &lt;/WeightNet&gt;
                &lt;VolumeGross&gt;
                    &lt;VolumeValue uom="CuFt"&gt;2300&lt;/VolumeValue&gt;
                    &lt;VolumeBase uom="CuFt"&gt;2300&lt;/VolumeBase&gt;
                &lt;/VolumeGross&gt;
                &lt;VolumeNet&gt;
                    &lt;VolumeValue uom="CuFt"&gt;2300&lt;/VolumeValue&gt;
                    &lt;VolumeBase uom="CuFt"&gt;2300&lt;/VolumeBase&gt;
                &lt;/VolumeNet&gt;
                &lt;PieceCount&gt;1170&lt;/PieceCount&gt;
                &lt;HandlingUnitCount&gt;26&lt;/HandlingUnitCount&gt;
                &lt;IsHazmat&gt;false&lt;/IsHazmat&gt;
            &lt;/OrderLine&gt;
        &lt;/OrderLines&gt;
    &lt;/Order&gt;
&lt;/Orders&gt;
</code></pre>

<p></p>

<p>Help is much appreciated.</p>

<hr>

<p>In case it is helpful for anyone trying to do something similar, this is how you upload the valid XML to the system:</p>

<pre><code>import requests
filename = 'somefile.xml'
api_url = 'someurl.com'
headers = {'Content-Type': 'application/xml'}
response = requests.post(api_url, data=open(filename).read(), headers=headers)
</code></pre>

<p>I actually figured out a SQL solution for this...but it is ridiculously manual and specific for my case. I hope the format still helps someone solve a similar issue.</p>

<p>If you notice from the code below, the XML tags will assume the name of the column if you don't include an alias. You can use the format 'Parent/Child' for hierarchies... Look at the bottom of the query to see multiple embedded hierarchies.</p>

<pre><code>SELECT 
    [OrganizationName]
  ,[OrdNum]
  ,[OrdType]
  ,[OrderTMSStatus]
  ,[FreightTerms]
  ,[IsPrePayment]
  ,[ScheduledEarlyPickup]
  ,[ScheduledEarlyDelivery]
  ,[WeightValue]  'TotalGrossWeight/WeightValue'
  ,[WeightBase] 'TotalGrossWeight/WeightBase'
  ,[WeightValue3]  'TotalNetWeight/WeightValue'
  ,[WeightBase5]  'TotalNetWeight/WeightBase'
  ,[VolumeValue]  'TotalGrossVolume/VolumeValue'
  ,[VolumeBase] 'TotalGrossVolume/VolumeBase'
  ,[VolumeValue9] 'TotalNetVolume/VolumeValue'
  ,[VolumeBase11] 'TotalNetVolume/VolumeBase'
  ,[TotalPieceCount]
  ,[TotalHandlingUnitCount]
  ,[IsInPlanning]
  ,[AreTotalsOverridden]
  ,[CurrencyValue] 'FreightValue/CurrencyValue'
  ,[CurrencyBase] 'FreightValue/CurrencyBase'
  ,[IsHot]
  ,[IsHazmat]
  ,[BillingStatus]
  ,[IntegrationStatus]
  ,[OriginLocNum]
  ,[OrganizationName15] 'OriginLoc/OrganizationName'
  ,[TradingPartnerNum] 'OriginLoc/TradingPartnerNum'
  ,[TradingPartnerType] 'OriginLoc/TradingPartnerType'
  ,[LocNum] 'OriginLoc/LocNum'
  ,[LocationType] 'OriginLoc/LocationType'
  ,[IsActive] 'OriginLoc/IsActive'
  ,[IsBillTo] 'OriginLoc/IsBillTo'
  ,[IsRemitTo] 'OriginLoc/IsRemitTo'
  ,[IsCorporate] 'OriginLoc/IsCorporate'
  ,[AddrName] 'OriginLoc/AddrName'
  ,[Addr1] 'OriginLoc/Addr1'
  ,[CityName] 'OriginLoc/CityName'
  ,[StateCode] 'OriginLoc/StateCode'
  ,[CountryISO2] 'OriginLoc/CountryISO2'
  ,[PostalCode] 'OriginLoc/PostalCode'
  ,[CalendarName] 'OriginLoc/CalendarName'
  ,[CalendarAppointmentName] 'OriginLoc/CalendarAppointmentName'
  ,[AllowsHazmat] 'OriginLoc/AllowsHazmat'
  ,[IsDeliveryAptRequired] 'OriginLoc/IsDeliveryAptRequired'
  ,[IsPickupAptRequired] 'OriginLoc/IsPickupAptRequired'
  ,[DestinationLocNum] 
  ,[OrganizationName16] 'DestinationLoc/OrganizationName'
  ,[TradingPartnerNum17] 'DestinationLoc/TradingPartnerNum'
  ,[TradingPartnerType18] 'DestinationLoc/TradingPartnerType'
  ,[LocNum19] 'DestinationLoc/LocNum'
  ,[LocationType20] 'DestinationLoc/LocationType'
  ,[IsActive21] 'DestinationLoc/IsActive'
  ,[IsBillTo22] 'DestinationLoc/IsBillTo'
  ,[IsRemitTo23] 'DestinationLoc/IsRemitTo'
  ,[IsCorporate24] 'DestinationLoc/IsCorporate'
  ,[AddrName25] 'DestinationLoc/AddrName'
  ,[Addr126] 'DestinationLoc/Addr1'
  ,[CityName27] 'DestinationLoc/CityName'
  ,[StateCode28] 'DestinationLoc/StateCode'
  ,[CountryISO229] 'DestinationLoc/CountryISO2'
  ,[PostalCode30] 'DestinationLoc/PostalCode'
  ,[CalendarName31] 'DestinationLoc/CalendarName'
  ,[CalendarAppointmentName32] 'DestinationLoc/CalendarAppointmentName'
  ,[AllowsHazmat33] 'DestinationLoc/AllowsHazmat'
  ,[IsDeliveryAptRequired34] 'DestinationLoc/IsDeliveryAptRequired'
  ,[IsPickupAptRequired35] 'DestinationLoc/IsPickupAptRequired' 
  ,[OrganizationName36] 'Client/OrganizationName'
  ,[TradingPartnerNum37] 'Client/TradingPartnerNum'
  ,[TradingPartnerName] 'Client/TradingPartnerName'
  ,[TradingPartnerType38] 'Client/TradingPartnerType'
  ,[IsActive39] 'Client/IsActive'
  ,[OrdLineNum] 'OrderLines/OrderLine/OrdLineNum'
  ,[WeightValue40] 'OrderLines/OrderLine/WeightGross/WeightValue'
  ,[WeightBase42] 'OrderLines/OrderLine/WeightGross/WeightBase'
  ,[WeightValue44] 'OrderLines/OrderLine/WeightNet/WeightValue'
  ,[WeightBase46] 'OrderLines/OrderLine/WeightNet/WeightBase'
  ,[VolumeValue48] 'OrderLines/OrderLine/VolumeGross/VolumeValue'
  ,[VolumeBase50] 'OrderLines/OrderLine/VolumeGross/VolumeBase'
  ,[VolumeValue52] 'OrderLines/OrderLine/VolumeNet/VolumeValue'
  ,[VolumeBase54] 'OrderLines/OrderLine/VolumeNet/VolumeBase'
  ,[PieceCount] 'OrderLines/OrderLine/PieceCount'
  ,[HandlingUnitCount] 'OrderLines/OrderLine/HandlingUnitCount'
  ,[IsHazmat56] 'OrderLines/OrderLine/IsHazmat'
 FROM [MyDatabase].[dbo].[OrderSample]
 For XML PATH ('Order')
</code></pre>

<p>I run that script inside of Python using the pymssql library</p>

<pre><code>#Create a function that reads SQL Query file and returns the result as a string
def queryReturn(query=sql, connection = conn):
    '''
    This function runs a SQL File and returns the result as a strings
    It takes in a T-SQL file as the first argument
    For example:  
    sql=open('sqlFinal.sql', 'r').read().decode('utf-8')
    xml = queryReturn(sql)
    You can also define which database connection to use
    For example: xml = queryReturn(sql,conn)

    '''
    cursor = connection.cursor()
    cursor.execute(query)
    result= "".join([item[0] for item in cursor.fetchall()])
    return result

#Create the header and tail for the XML
header ='''&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;
&lt;ns1:OrderData xmlns:ns1="http://schemas.3gtms.com/tms/v1/tns"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
&lt;Orders&gt;\n '''
tail = '''\n    &lt;/Orders&gt;
&lt;/ns1:OrderData&gt; '''

#Add uom to the opening tags that need it
xml2=xml2.replace('&lt;WeightValue','&lt;WeightValue uom="Lb"')
xml2=xml2.replace('&lt;WeightBase','&lt;WeightBase uom="Lb"')
xml2=xml2.replace('&lt;VolumeValue','&lt;VolumeValue uom="CuFt"')
xml2=xml2.replace('&lt;VolumeBase','&lt;VolumeBase uom="CuFt"')
xml2=xml2.replace('&lt;CurrencyValue','&lt;CurrencyValue uom="USD"')
xml2=xml2.replace('&lt;CurrencyBase','&lt;CurrencyBase uom="USD"')
</code></pre>

<p>Then I add the top and bottom parts of the query to the main body:</p>

<pre><code>#create final XML string
final = header+xml2+tail
</code></pre>

python xml csv