将XML映射到SQL以便轻松导出。

Question

将XML映射到SQL以便轻松导出。

3

我有以下XML，希望将其映射到关系模型，以便可以查询并重新导出相同的XML。

<?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和CHANNEL通过CHANNEL_FIELD's属性PARTY_ID30相关联

CHANNEL与RATE通过RATE_FIELD's属性CHANNEL_ID0相关联

我创建了以下表格，但我不能像给定的xml一样查询它们以导出：

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

我需要更改模式来查询并导出行以创建此xml或构建查询以按与上述模式相同的顺序生成行。

另一种方法是将行导出为csv，然后使用Python生成xml，但对于大型数据集来说可能会有额外的开销。

- curious_nustian

1

请检查以下内容是否有所帮助：https://dev.mysql.com/doc/refman/8.0/en/xml-functions.html - pringi

数据模型中有一个特定的CHANNEL_FIELD字段名作为外键，这有点混乱。您确定要坚持使用这个模型吗？即使对于XML来说，这也非常奇怪。 - digitalarbeiter

@digitalarbeiter 我明白，但是我们的外部系统生成的数据就是这样的。 - curious_nustian

请查看 https://dev.mysql.com/doc/refman/8.0/en/load-xml.html。 - Rick James

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- digitalarbeiter · Answer 1

我会将这个拆分成parties/party_fields表（对于渠道和费率也是如此）：

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));

（旁注：从您的示例数据中可以看出，您甚至不能在（party_id，fieldname）上有唯一约束条件，因为字段名“SIE”出现了两次。）

然后使用Python的xml.etree.ElementTree遍历XML文档，并根据元素tag生成INSERT语句（我稍微略过了MySQL客户端代码，假设您有一个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()

说实话，对于带有字段名称PARTY_ID30的派对字段中的外键，我不会在这个XML结构上费心 - 我认为这将真正混乱你的数据库架构（至少我想不到一种干净的方法去做；也许其他人可以）。

现在重新生成XML，您可以遍历数据库中的聚会：

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')

...并且通道和速率也一样。

希望这可以帮到你。如果你有更多问题，请告诉我。