Map XML to SQL for easy export

user_8263237 0
Viewed 138

I have the following XML which I want to map to relational model, so that I can query and re-export the same XML again.

<?xml version="1.0" encoding="UTF-8"?>

<document name="001_COUNTERPARTY_CATEGORY_UK_BOE" date="2022-06-30" level="01-01-xx-xx-xx">

<PARTY F="01-01" PARTY_ID="201_A_Prod_P" />
<PARTY_FIELD F1="01-01" PARTY_ID="201_A_Prod_P" fieldname="CTY0" value="IR"/>
<PARTY_FIELD F1="01-01" PARTY_ID="201_A_Prod_P" fieldname="CTY1" value="IR"/>
<PARTY_FIELD F1="01-01" PARTY_ID="201_A_Prod_P" fieldname="SIE" value="64_19"/>
<PARTY_FIELD F1="01-01" PARTY_ID="201_A_Prod_P" fieldname="SIE" value="0"/>

<CHANNEL F="01-01" CHANNEL_ID="201_A_Prod_PRODUCT"/>
<CHANNEL_FIELD F="01-01" CHANNEL_ID="201_A_Prod_PRODUCT" fieldname="PRD013" value="1010"/>
<CHANNEL_FIELD F="01-01" CHANNEL_ID="201_A_Prod_PRODUCT" fieldname="CUR007" value="GBP"/>
<CHANNEL_FIELD F="01-01" CHANNEL_ID="201_A_Prod_PRODUCT" fieldname="PARTY_ID30" value="201_A_Prod_P"/>

<RATE F="01-01" RATE_ID="201_A_Prod_PRODUCT"/>
<RATE_FIELD F="01-01" RATE_ID="201_A_Prod_PRODUCT" fieldname="CHANNEL_ID0" value="201_A_Prod_PRODUCT"/>
<RATE_FIELD F="01-01" RATE_ID="201_A_Prod_PRODUCT" fieldname="C213" value="100000"/>    
<RATE_FIELD F="01-01" RATE_ID="201_A_Prod_PRODUCT" fieldname="C214" value="100000"/>    
<RATE_FIELD F="01-01" RATE_ID="201_A_Prod_PRODUCT" fieldname="C215" value="100000"/>    
<RATE_FIELD F="01-01" RATE_ID="201_A_Prod_PRODUCT" fieldname="PTY001" value="1"/>
<RATE_FIELD F="01-01" RATE_ID="201_A_Prod_PRODUCT" fieldname="PTY002" value="1"/>
<RATE_FIELD F="01-01" RATE_ID="201_A_Prod_PRODUCT" fieldname="PTY006" value="0"/>
<RATE_FIELD F="01-01" RATE_ID="201_A_Prod_PRODUCT" fieldname="PTY025" value="0"/>

</document>

PARTY AND CHANNEL relate to each other by CHANNEL_FIELD's attribute PARTY_ID30

and CHANNEL relate with RATE by RATE_FIELD's attribute CHANNEL_ID0

I created tables as following, but I am not able to query them to export like the xml given:

PARTY (F,PARTY_ID,PARTY_FIELDNAME,PARTY_FIELDVALUE)
CHANNEL (F,CHANNEL_ID,CHANNEL_FIELDNAME,CHANNEL_FIELDVALUE)
PRODUCT (F,RATE_ID,RATE_FIELDNAME,RATE_FIELDVALUE)

Either I need to change the schema to let me query and export rows to create this xml or build query to generate rows in same order as xml from above schema.

An alternate approach is to export rows in csv and then use python to generate xml, but it would be overhead for large dataset

xml mysql sql python
1 Answers

I would split this up in parties/party_fields tables (same for channels and rates):

CREATE TABLE parties (id VARCHAR(32), f TEXT, PRIMARY KEY(id));
CREATE TABLE party_fields (party_id VARCHAR(32), f TEXT, fieldname TEXT, value TEXT, FOREIGN KEY(party_id) REFERENCES parties(id));

(Side note: from your example data it seems you can't even have a UNIQUE constraint on (party_id, fieldname) as the fieldname "SIE" occurcs twice.)

Then use Python's xml.etree.ElementTree to iterate over the XML document and generate INSERT statements depending on the elements tag (I'm glossing over the MySQL client code a bit, assuming you have a cursor):

import xml.etree.ElementTree as et
for node in et.fromstring(the_long_xml_string):
    if node.tag == "PARTY":
        cursor.execute(
            "INSERT INTO parties (id, f) VALUES (%s, %s)",
            (node.attrib["PARTY_ID"], node.attrib["F"]),
        )
    elif node.tag == "PARTYFIELD":
        cursor.execute(
            "INSERT INTO party_fields (party_id, f, fieldname, value) VALUES (%s, %s, %s, %s)",
            (node.attrib["PARTY_ID"], node.attrib["F"], node.attrib["fieldname"], node.attrib["value"]),
        )
    elif node.tag == "CHANNEL":
        ...
cursor.commit()

Frankly with this XML structure, I wouldn't bother with the foreign keys in party fields with fieldname PARTY_ID30 - I think that would really mess up your database schema (well, at least I can't think of a way to do this cleanly; maybe someone else can).

Now to (re-)generate XML, you can iterate over the parties from the database:

current_party_id = None
for row in cursor.execute("SELECT * FROM party_fields ORDER BY party_id ASC"):
    if row["party_id"] != current_party_id:
        out.write(f'<PARTY PARTY_ID="{row["party_id"]}" F="{row["f"]} />\n"')
        current_party_id = row["party_id"]
    out.write(f'<PARTY_FIELD PARTY_ID="{row["party_id"]}" F="{row["f"]}" fieldname="{row["fieldname"]}" value="{row["value"]}" />\n')

... and the same for channels and rates.

Hope this helps. Let me know if you have further questions.

user_1890180
Related