In modern data workfllows, XML remains a widely adopted format for structured data enterchange. GaussDB, known for its scalability and reliability, serves as an ideal backend for storing and querying such data. This guide demonstrates how to parse XML files with Python and load the extracted records into a GaussDB table.

Prerequisites

Install the necessary Python packages to handle XML parsing and database connectivity:

pip install pandas
pip install psycopg2-binary

Database Schema Setup

Before importing, define a target table in GaussDB to hold the incoming data:

CREATE TABLE person_records (
    record_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100),
    years_old INTEGER,
    location VARCHAR(80)
);

Parsing XML into Structured Data

Consider an input file dataset.xml with the folllowing structure:

<dataset>
    <entry>
        <full_name>Alice</full_name>
        <years_old>30</years_old>
        <location>New York</location>
    </entry>
    <entry>
        <full_name>Bob</full_name>
        <years_old>25</years_old>
        <location>Los Angeles</location>
    </entry>
</dataset>

Use Python’s built-in XML parser to extract and transform this into a DataFrame:

import pandas as pd
from xml.etree.ElementTree import parse

def extract_xml_to_df(filepath):
    root = parse(filepath).getroot()
    records = [
        {
            'full_name': item.find('full_name').text,
            'years_old': int(item.find('years_old').text),
            'location': item.find('location').text
        }
        for item in root.findall('entry')
    ]
    return pd.DataFrame(records)

Loading Data into GaussDB

Establish a connection and insert each row from the DataFrame:

import psycopg2

def load_to_gaussdb(df, conn_params):
    conn = psycopg2.connect(**conn_params)
    cursor = conn.cursor()

    insert_query = """
        INSERT INTO person_records (full_name, years_old, location)
        VALUES (%s, %s, %s)
    """

    for _, row in df.iterrows():
        cursor.execute(insert_query, (
            row['full_name'],
            row['years_old'],
            row['location']
        ))

    conn.commit()
    cursor.close()
    conn.close()

# Example usage
connection_config = {
    'host': 'localhost',
    'database': 'your_db',
    'user': 'your_user',
    'password': 'your_pass'
}

dataframe = extract_xml_to_df('dataset.xml')
load_to_gaussdb(dataframe, connection_config)

Schema Visualization

The resulting table structure can be represented as:

erDiagram
    person_records ||--o{ record_id : "Primary Key"
    person_records }|--|| full_name : string
    person_records }|--|| years_old : integer
    person_records }|--|| location : string