将XML文件同步到MySQL数据库

2022-12-19 18:33:48

我公司使用内部管理软件存储产品.他们希望将所有产品转置到MySql数据库中,以便他们可以在公司网站上提供他们的产品.

注意：他们将继续使用自己的内部软件.该软件可以以各种文件格式(包括XML)导出所有产品.

同步不必是实时的,他们满意每天(深夜)同步MySql数据库一次.

此外,他们软件中的每个产品都有一个或多个图像,然后我还要提供网站上的图像.

以下是XML导出的示例：

<?xml version="1.0" encoding="UTF-8"?>
<export_management userid="78643">
    <product id="1234">
        <version>100</version>
        <insert_date>2013-12-12 00:00:00</insert_date>
        <warrenty>true</warrenty>
        <price>139,00</price>
        <model>
            <code>324234345</code>
            <model>Notredame</model>
            <color>red</color>
            <size>XL</size>
        </model>
        <internal>
            <color>green</color>
            <size>S</size>
        </internal>
        <options>
            <s_option>aaa</s_option>
            <s_option>bbb</s_option>
            <s_option>ccc</s_option>
            <s_option>ddd</s_option>
            <s_option>eee</s_option>
            <s_option>fff</s_option>
      ...
            <extra_option>ggg</extra_option>
            <extra_option>hhh</extra_option>
            <extra_option>jjj</extra_option>
            <extra_option>kkk</extra_option>
      ...
        </options>
        <images>
            <image>
                <small>1234_0.jpg</small>
            </image>
            <image>
                <small>1234_1.jpg</small>
            </image>
        </images>
    </product>
    <product id="5321">
    ...
    </product>
    <product id="2621">
    ...
    </product>
  ...
</export_management>

关于我该怎么做的一些想法？

如果我的问题不明确,请告诉我.谢谢

编辑：
我为每个表使用了这样的SQL来填充XML数据：

LOAD XML LOCAL INFILE '/products.xml' INTO TABLE table_name ROWS IDENTIFIED BY '<tag_name>';

然后,检查表格内容我可以看到字段“id”(主键)自动为每个表中的每个相应产品行保持相同.这是正确的,令人惊讶的真棒！

现在的问题是参数< options>因为它包含具有相同名称的子参数(< s_option>和< extra_option>).这些标签的值总是不同的(也就是说,没有特定的值列表,它们是由员工手动插入的),而且我不知道每个产品有多少.我读到将它们存储为数组并不是那么好但是如果它是唯一的简单解决方案我可以得到它.

解决方法:

我在你的案子中处理问题的方式是：

>在数据库中创建一组相应的表,然后通过从给定的XML中提取建模来表示公司的产品模型.
>创建并使用计划的每日同步作业,该作业可能会执行少量SQL命令,以便通过将产品XML解析为创建的表来刷新数据或引入新数据.

为了更加实用：

>对于数据库的表,我可以根据您的XML轻松识别要创建的三个表,查看黄色标记的元素：

>产品
>产品选项
> ProductsImages

(此图基于从XML生成的XSD创建)

所有其余的可以被视为Products表中的常规列,因为它们仅构成1-1关系.

接下来,在数据库中创建所需的表(您可以使用XSD2DB Schema转换器工具来创建DDL脚本,我手动完成)：

companydb.products

CREATE TABLE companydb.products (
  Id INT(11) NOT NULL,
  Version INT(11) DEFAULT NULL,
  InsertDate DATETIME DEFAULT NULL,
  Warrenty TINYINT(1) DEFAULT NULL,
  Price DECIMAL(19, 2) DEFAULT NULL,
  ModelCode INT(11) DEFAULT NULL,
  ModelColor VARCHAR(10) DEFAULT NULL,
  Model VARCHAR(255) DEFAULT NULL,
  ModelSize VARCHAR(10) DEFAULT NULL,
  InternalColor VARCHAR(10) DEFAULT NULL,
  InternalSize VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (Id)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Company''s Products';

companydb.productsimages

CREATE TABLE companydb.productimages (
  Id INT(11) NOT NULL AUTO_INCREMENT,
  ProductId INT(11) DEFAULT NULL,
  Size VARCHAR(10) DEFAULT NULL,
  FileName VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (Id),
  CONSTRAINT FK_productsimages_products_Id FOREIGN KEY (ProductId)
    REFERENCES companydb.products(Id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Products'' Images';

companydb.productsoptions

CREATE TABLE companydb.productoptions (
  Id INT(11) NOT NULL AUTO_INCREMENT,
  ProductId INT(11) DEFAULT NULL,
  Type VARCHAR(255) DEFAULT NULL,
  `Option` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (Id),
  CONSTRAINT FK_producstsoptions_products_Id FOREIGN KEY (ProductId)
    REFERENCES companydb.products(Id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;

>对于要进行的同步作业过程,您可以轻松创建MySql event并使用Event Scheduler来控制它,我创建了所需的事件,调用您将在下面找到的存储过程(SyncProductsDataFromXML),看看：

CREATE DEFINER = ‘root’@’localhost’ EVENT
companydb.ProductsDataSyncEvent ON SCHEDULE EVERY ‘1’ DAY STARTS
‘2014-06-13 01:27:38’ COMMENT ‘Synchronize Products table with
Products XMLs’ DO BEGIN SET @productsXml =
LOAD_FILE(‘C:/MySqlXmlSync/products.xml’); CALL
SyncProductsDataFromXML(@productsXml); END;
ALTER EVENT companydb.ProductsDataSyncEvent ENABLE

现在有趣的部分正在发生,这里是同步存储过程(注意上面的事件如何调用它)：

CREATE DEFINER = 'root'@'localhost'
PROCEDURE companydb.SyncProductsDataFromXML(IN productsXml MEDIUMTEXT)
BEGIN

  DECLARE totalProducts INT;
  DECLARE productIndex INT;

  SET totalProducts = ExtractValue(productsXml, 'count(//export_management/product)');

  SET productIndex = 1;
  WHILE productIndex <= totalProducts DO
        SET @productId = CAST(ExtractValue(productsXml, 'export_management/product[$productIndex]/@id') AS UNSIGNED);

        INSERT INTO products(`Id`, `Version`, InsertDate, Warrenty, Price, ModelCode, Model, ModelColor, ModelSize, InternalColor, InternalSize)
        VALUES(
                @productId,
                ExtractValue(productsXml, 'export_management/product[$productIndex]/version'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/insert_date'),
                CASE WHEN (ExtractValue(productsXml, 'export_management/product[$productIndex]/warrenty')) <> 'false' THEN 1 ELSE 0 END,
                CAST(ExtractValue(productsXml, 'export_management/product[$productIndex]/price') as DECIMAL),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/model/code'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/model/model'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/model/color'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/model/size'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/internal/color'),
                ExtractValue(productsXml, 'export_management/product[$productIndex]/internal/size')
              );

        SET @totalImages = ExtractValue(productsXml, 'count(//export_management/product[$productIndex]/images/image)');
        SET @imageIndex = 1;
        WHILE (@imageIndex <= @totalImages) DO
          INSERT INTO productimages(ProductId, Size, FileName) VALUES(@productId, 'small', EXTRACTVALUE(productsXml, 'export_management/product[$productIndex]/images/image[$@imageIndex]/small'));
          SET @imageIndex = @imageIndex + 1;        
        END WHILE;

        SET @totalStandardOptions = ExtractValue(productsXml, 'count(//export_management/product[$productIndex]/options/s_option)');
        SET @standardOptionIndex = 1;
        WHILE (@standardOptionIndex <= @totalStandardOptions) DO
          INSERT INTO productoptions(ProductId, `Type`, `Option`) VALUES(@productId, 'Standard Option', EXTRACTVALUE(productsXml, 'export_management/product[$productIndex]/options/s_option[$@standardOptionIndex]'));
          SET @standardOptionIndex = @standardOptionIndex + 1;
        END WHILE;

        SET @totalExtraOptions = ExtractValue(productsXml, 'count(//export_management/product[$productIndex]/options/extra_option)');
        SET @extraOptionIndex = 1;
        WHILE (@extraOptionIndex <= @totalExtraOptions) DO
          INSERT INTO productoptions(ProductId, `Type`, `Option`) VALUES(@productId, 'Extra Option', EXTRACTVALUE(productsXml, 'export_management/product[$productIndex]/options/extra_option[$@extraOptionIndex]'));
          SET @extraOptionIndex = @extraOptionIndex + 1;        
        END WHILE;

        SET productIndex = productIndex + 1;

  END WHILE;
END

而且你已经完成了,这是这个过程的最终预期结果：

注意：我已将整个代码提交到我的一个GitHub存储库：XmlSyncToMySql

更新：

因为您的XML数据可能比TEXT字段允许的最大值大,所以我将productsXml参数更改为MEDIUMTEXT.看看这个答案,它概述了各种文本数据类型的最大允许大小：
Maximum length for MYSQL type text

码农公寓

相关文章