XML parsing and loading to postgresql

Hi,

I’m working on a Django app that is basically going to take monthly files containing millions of records that look like the XML file below.

From the research I’ve done, it is best to parse this data and convert it to a csv, then follow some of the advice to load into postgresql from this post.

Once I have this data loaded I want to build some specific reports and charts that summarizes the data month to month.

App Scope:
Summarize XML data and show difference in files, and some other domain specific analysis.

Questions:

  1. Should I load these files (up to 200MB in size) into a database every month, and then use SQL to summarize, and pass data to the view?
  2. Should I just load the files into a directory and try to summarize on the frontend with javascript and python?
  3. Should I convert xml to csv then upload to postgresql?
  4. Some elements inside of each App have an id, which I have relational tables for. Should I be creating a model that stores the id instead of the text?

Thanks in advance for your response, I really appreciate it. I’m a noop to django and python, but it’s been a fun process working with this framework.

Sample file:

<ACES version="3.0"> 
	<Header> 
		<Company>ACME Incorporated</Company>
		<SenderName>Wilson Usman</SenderName>
		<SenderPhone>419-111-1111</SenderPhone>
		<TransferDate>2019-12-17</TransferDate>
		<BrandAAIAID>BrandID</BrandAAIAID>
		<DocumentTitle>Brand</DocumentTitle>
		<EffectiveDate>2019-12-17</EffectiveDate>
		<ApprovedFor>US, CA</ApprovedFor>
		<SubmissionType>FULL</SubmissionType>
		<MapperCompany>ACME Incorporated</MapperCompany>
		<MapperContact>John Doe</MapperContact>
		<MapperPhone>419-111-1111</MapperPhone>
		<MapperEmail>wilson.usman@dana.com</MapperEmail>
		<VcdbVersionDate>2019-11-22</VcdbVersionDate>
		<QdbVersionDate>2019-11-22</QdbVersionDate>
		<PcdbVersionDate>2019-11-22</PcdbVersionDate>
	</Header>
	<App action="A" id="1">
		<BaseVehicle id="1">2002 Suzuki Aerio</BaseVehicle>
		<Qty>1</Qty>
		<PartType id="10396">Engine Oil Pan Gasket Set</PartType>
		<Part>10-10102-01</Part>
	</App>
	<App action="A" id="2">
		<BaseVehicle id="1">2002 Suzuki Aerio</BaseVehicle>
		<Qty>1</Qty>
		<PartType id="11593">Engine Coolant Thermostat Gasket</PartType>
		<Part>71-40884-00</Part>
	</App>
	<App action="A" id="3">
		<BaseVehicle id="1">2002 Suzuki Aerio</BaseVehicle>
		<Note>At Converter</Note>
		<Qty>1</Qty>
		<PartType id="5836">Exhaust Pipe Flange Gasket</PartType>
		<Part>71-15272-00</Part>
	</App>
	<App action="A" id="4">
		<BaseVehicle id="1">2002 Suzuki Aerio</BaseVehicle>
		<Note>At Converter</Note>
		<Qty>1</Qty>
		<PartType id="5836">Exhaust Pipe Flange Gasket</PartType>
		<Part>71-15272-00</Part>
	</App>
		<App action="A" id="5">
		<BaseVehicle id="1">2002 Suzuki Aerio</BaseVehicle>
		<Qual id="5390">
			<text>Improved Design</text>
		</Qual>
		<Qty>1</Qty>
		<PartType id="13680">Fuel Injector O-Ring Kit</PartType>
		<Position id="46">Upper</Position>
		<Part>18-10027-01</Part>
	</App>
	<App action="A" id="6">
		<BaseVehicle id="1">2002 Suzuki Aerio</BaseVehicle>
		<Note>Injectors To Cylinder Head</Note>
		<Qty>1</Qty>
		<PartType id="13680">Fuel Injector O-Ring Kit</PartType>
		<Part>18-10030-01</Part>
	</App>
	<App action="A" id="7">
		<BaseVehicle id="1">2002 Suzuki Aerio</BaseVehicle>
		<Qual id="229">
			<param value="1"/>
			<text>1 Piece</text>
		</Qual>
		<Qual id="8583">
			<text>Rubber</text>
		</Qual>
		<Note>Rear</Note>
		<Qty>1</Qty>
		<PartType id="10512">Engine Crankshaft Seal Kit</PartType>
		<Position id="30">Rear</Position>
		<Part>19-10094-01</Part>
	</App>
	<App action="A" id="8" validate="no">
		<BaseVehicle id="117764">1967 Pontiac Executive</BaseVehicle>
		<EngineBase id="5422">6.6L 6555cc 400ci V8 (4.12x3.75; 104.7x95.3)</EngineBase>
		<EngineVIN id="30">X</EngineVIN>
		<EngineMfr id="11">General Motors</EngineMfr>
		<ValvesPerEngine id="3">16</ValvesPerEngine>
		<FuelDeliverySubType id="10">4BBL</FuelDeliverySubType>
		<FuelSystemDesign id="29">Rochester</FuelSystemDesign>
		<Aspiration id="5">Naturally Aspirated</Aspiration>
		<CylinderHeadType id="7">OHV</CylinderHeadType>
		<FuelType id="5">GAS</FuelType>
		<Qty>1</Qty>
		<PartType id="10024">Carburetor Mounting Gasket</PartType>
		<Part>71-13689-00</Part>
	</App>
	<Footer>
		<RecordCount>8</RecordCount>
	</Footer>
</ACES>