将API返回的XML订单详情插入SQL数据库表的需求

需求：将API返回的XML格式订单数据插入数据库表，当同一订单ID对应多个商品时，为每个商品单独插入一条包含该订单ID的记录。

期望插入结果：

row #1  order id: i6tp-pucp-dsrx-1gg7-ikef  item_no: 2304
row #2  order id: i6tp-pucp-dsrx-1gg7-ikef  item_no: 10914

API返回的XML数据：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns1:result xmlns:ns1="url.xsd">
<ns1:orders>
    <ns1:order>
        <ns1:code>i6tp-pucp-dsrx-1gg7-ikef</ns1:code>
        <ns1:status>CONFIRMED</ns1:status>
        <ns1:date>2022-11-30T20:50:36.920</ns1:date>
        <ns1:deliveryAddress>
            <ns1:firstName>fname</ns1:firstName>
            <ns1:lastName>lastname</ns1:lastName>
            <ns1:streetname>streetname </ns1:streetname>
            <ns1:streetnumber>stno</ns1:streetnumber>
            <ns1:town>town</ns1:town>
            <ns1:postalCode>PLZ</ns1:postalCode>
            <ns1:gender>FEMALE</ns1:gender>
            <ns1:deliveryAddressType>address</ns1:deliveryAddressType>
        </ns1:deliveryAddress>
        <ns1:paymentAddress>
            <ns1:firstName>fname</ns1:firstName>
            <ns1:lastName>lastname</ns1:lastName>
            <ns1:streetname>streetname </ns1:streetname>
            <ns1:streetnumber>stno</ns1:streetnumber>
            <ns1:town>town</ns1:town>
            <ns1:postalCode>PLZ</ns1:postalCode>
            <ns1:gender>FEMALE</ns1:gender>
        </ns1:paymentAddress>
        <ns1:currency>EUR</ns1:currency>
        <ns1:entries>
            <ns1:entry>
                <ns1:sku>2304</ns1:sku>
                <ns1:quantity>1</ns1:quantity>
                <ns1:basePrice>18.49</ns1:basePrice>
                <ns1:totalBasePrice>18.49</ns1:totalBasePrice>
                <ns1:merchantSubTotal>18.49</ns1:merchantSubTotal>
                <ns1:totalPrice>18.49</ns1:totalPrice>
                <ns1:name>ArtNo1</ns1:name>
                <ns1:taxClass>at-vat-full</ns1:taxClass>
                <ns1:warehouse>001-default-warehouse</ns1:warehouse>
            </ns1:entry>
            <ns1:entry>
                <ns1:sku>10914</ns1:sku>
                <ns1:quantity>1</ns1:quantity>
                <ns1:basePrice>49.99</ns1:basePrice>
                <ns1:totalBasePrice>49.99</ns1:totalBasePrice>
                <ns1:merchantSubTotal>49.99</ns1:merchantSubTotal>
                <ns1:totalPrice>49.99</ns1:totalPrice>
                <ns1:name>Artno2</ns1:name>
                <ns1:taxClass>at-vat-full</ns1:taxClass>
                <ns1:warehouse>001-default-warehouse</ns1:warehouse>
            </ns1:entry>
        </ns1:entries>
        <ns1:cancelOrders/>
        <ns1:returnOrders/>
        <ns1:delayOrders/>
        <ns1:consignments/>
        <ns1:totalBasePrice>68.48</ns1:totalBasePrice>
        <ns1:merchantDiscountTotal>0</ns1:merchantDiscountTotal>
        <ns1:merchantSubTotal>68.48</ns1:merchantSubTotal>
        <ns1:marketplaceDiscountTotal>0</ns1:marketplaceDiscountTotal>
        <ns1:subtotal>68.48</ns1:subtotal>
        <ns1:deliveryCost>0.0</ns1:deliveryCost>
        <ns1:paymentCost>0.0</ns1:paymentCost>
        <ns1:totalPrice>68.48</ns1:totalPrice>
        <ns1:paymentMode>payment</ns1:paymentMode>
        <ns1:deliveryMode>E+2</ns1:deliveryMode>
        <ns1:additionalDeliveryOption>E2_1</ns1:additionalDeliveryOption>
        <ns1:deliveryConfiguration>Post</ns1:deliveryConfiguration>
        <ns1:shipmentDate>2022-12-02T07:00:11.680</ns1:shipmentDate>
        <ns1:estimatedDeliveryDate>2022-12-06T07:00:11.680</ns1:estimatedDeliveryDate>
        <ns1:avisoData>
            <ns1:address>
                <ns1:line>line1</ns1:line>
                <ns1:line>line2</ns1:line>
                <ns1:line>line3</ns1:line>
            </ns1:address>
            <ns1:phone>0123456789</ns1:phone>
            <ns1:email>info@gmail.com</ns1:email>
        </ns1:avisoData>
    </ns1:order>
</ns1:orders>
</ns1:result>

解决方案 #

步骤1：解析XML数据 #

XML带有命名空间ns1，解析时需处理命名空间映射。以Python为例，用xml.etree.ElementTree提取订单ID和商品SKU：

import xml.etree.ElementTree as ET

# 替换为实际API响应文本
xml_content = """上述XML字符串"""

# 命名空间映射
namespace = {'ns1': 'url.xsd'}
root = ET.fromstring(xml_content)

# 提取订单ID
order_id = root.find('.//ns1:order/ns1:code', namespace).text

# 提取所有商品SKU
item_skus = [entry.text for entry in root.findall('.//ns1:entry/ns1:sku', namespace)]

步骤2：批量插入数据库 #

假设数据库表结构（MySQL为例）：

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50) NOT NULL,
    item_no VARCHAR(20) NOT NULL,
    INDEX idx_order_id (order_id)
);

用Python的mysql-connector执行批量插入：

import mysql.connector

# 数据库连接配置，替换为实际信息
db_config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',
    'database': 'your_db'
}

# 构造插入数据
insert_rows = [(order_id, sku) for sku in item_skus]

# 执行插入
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()

insert_sql = "INSERT INTO order_items (order_id, item_no) VALUES (%s, %s)"
cursor.executemany(insert_sql, insert_rows)

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

SQL Server直接解析方案 #

如果使用SQL Server，可通过OPENXML直接解析XML并插入：

DECLARE @xml XML = '上述XML字符串'

;WITH XMLNAMESPACES ('url.xsd' AS ns1)
INSERT INTO order_items (order_id, item_no)
SELECT 
    order_node.value('(ns1:code)[1]', 'VARCHAR(50)') AS order_id,
    item_node.value('(ns1:sku)[1]', 'VARCHAR(20)') AS item_no
FROM @xml.nodes('/ns1:result/ns1:orders/ns1:order') AS orders(order_node)
CROSS APPLY order_node.nodes('ns1:entries/ns1:entry') AS items(item_node)

内容的提问来源于stack exchange，提问作者naim