how to export xml file from a s3 bucket using lambda(python) into tables in an oracle database

clevermrj 0
Viewed 45

I'm having a hard time trying to connect to my s3 bucket to read the xml file with lambda I'm using "xml.estree.ElementTree". Then I want to be able to upload them into tables in oracle.

Example: file.xml

<row>
 <Id >1234</Id>
 <Name>Jon</Name>
</row>
<row>
 <Id>1244</Id>
 <Name>Doe</Name>
</row>

import json
from xml.dom import minidom
import boto3

s3 = boto3.resource('s3')

def lambda_handler(event, context):
   bucketname = 'my bucket' 
   filename = 'file.xml' 
   obj = s3.Object(bucketname, filename)
   file_data = obj.get()['Body'].read()

   #parse xml
   xmldoc = minidom.parseString(file_data)
   rows = xmldoc.getElementsByTagName("row")
   for row in rows:
        Id = row.getElementsByTagName("Id")[0]
        Name = row.getElementsByTagName("Name")[0]
        print("Id:% s, Name:% s" 
              (Id.firstChild.data, Name.firstChild.data))

This is the current code I have that reads it how can I upload it to a oracle database table.

Any help or guidance will be appreciated.

amazon-rds xml aws-lambda amazon-web-services python
1 Answers

Put xml into a datatable using Xml Linq :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;

namespace ConsoleApplication40
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string));

            XDocument doc = XDocument.Load(FILENAME);

            foreach (XElement row in doc.Descendants("row"))
            {
                dt.Rows.Add(new object[] { (string)row.Element("Id"), (string)row.Element("Name")});
            }

        }
    }

}
jdweng0
Related