mysql "LINES STARTING BY"具有属性的节点

Question

如何使用加载数据或加载 XML 将具有以下语法加载的 XML 文件导入到 mysql 表中。XML 格式:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<merchants xmlns="urn:com:kelkoo:merchant:bean:v1">
    <merchant id="11994513">
        <name>tool-fitness.es</name>
         <url>http://www.tool-fitness.es</url>
         <profile>
            <summary>tienda especializada en máquinas de fitness y material de musculación a precios de descuento.</summary>
            <logo>
                <logoURL>http://r6.kelkoo.com//data/merchantlogos/11994513/tool.JPG</logoURL>
            </logo>
        </profile>
         <email>contacto@tool-fitness.es</email>
         </merchant>

    <merchant id="13531013">
     <name>Electrodomesticosweb</name>
         <url>http://www.electrodomesticosweb.es/</url>
        <profile>
            <summary>tienda venta online de electrodomesticos para el hogar</summary>
            <logo>                <logoURL>http://r6.kelkoo.com//data/merchantlogos/13531013/logo.jpg</logoURL>
            </logo>
        </profile>
         <email>info@electrodomesticosweb.es</email>
    </merchant>
</merchants>

首先我尝试了这个，但它只输入一条记录

LOAD DATA LOCAL INFILE "/home/wwwindia/public_html/europe/spain/MerchantFeed_es.xml"
INTO TABLE es_merchant
CHARACTER SET binary
LINES STARTING BY '<merchants xmlns="urn:com:kelkoo:merchant:bean:v1">'   TERMINATED BY '</merchants>'
(@merchants)
SET
  merchant_id = ExtractValue(@merchants, '/merchant/@id'),
  url = ExtractValue(@merchants, '/merchant/url'),
  name = ExtractValue(@merchants, '/merchant/name'),   
  summary = ExtractValue(@merchants, '/merchant/profile/summary'),   
  logoURL = ExtractValue(@merchants, '/merchant/profile/logo/logoURL'),   
  email = ExtractValue(@merchants, '/merchant/email') 
;

接下来，当我尝试使用这些代码执行此操作时，它失败了:

LOAD DATA LOCAL INFILE "/home/wwwindia/public_html/europe/spain/MerchantFeed_es.xml"
INTO TABLE es_merchant
CHARACTER SET binary
LINES STARTING BY '<merchant id="13531013">' TERMINATED BY '</merchant>'
(@merchant)
SET
  merchant_id = ExtractValue(@merchant, '@id'),
  url = ExtractValue(@merchant, 'url'),
  name = ExtractValue(@merchant, 'name'),   
  summary = ExtractValue(@merchant, 'profile/summary'),   
  logoURL = ExtractValue(@merchant, 'profile/logo/logoURL'),   
  email = ExtractValue(@merchant, 'email') 
;

有什么帮助吗？

Answer 1

您可以更改 XML 中的顺序吗？在下面的示例中您可以看到原因。

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<merchants xmlns="urn:com:kelkoo:merchant:bean:v1">
    <merchant id="11994513">
        <name>tool-fitness.es</name>
        <url>http://www.tool-fitness.es</url>
        <email>contacto@tool-fitness.es</email>
        <profile>
            <summary>tienda especializada en máquinas de fitness y material de musculación a precios de descuento.</summary>
            <logo>
                <logoURL>http://r6.kelkoo.com//data/merchantlogos/11994513/tool.JPG</logoURL>
            </logo>
        </profile>
    </merchant>
    <merchant id="13531013">
        <name>Electrodomesticosweb</name>
        <url>http://www.electrodomesticosweb.es/</url>
        <email>info@electrodomesticosweb.es</email>
        <profile>
            <summary>tienda venta online de electrodomesticos para el hogar</summary>
            <logo>
                <logoURL>http://r6.kelkoo.com//data/merchantlogos/13531013/logo.jpg</logoURL>
            </logo>
        </profile>
    </merchant>
</merchants>

/* CODE FOR DEMONSTRATION PURPOSES */
mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.5.22-0ubuntu1 |
+-----------------+
1 row in set (0.00 sec)

DROP TABLE IF EXISTS `es_merchant`;
Query OK, 0 rows affected (0.00 sec)    

CREATE TABLE `es_merchant` (
    `id` INT NOT NULL,
    `url` VARCHAR(200) NULL,
    `name` VARCHAR(60) NULL,
    `summary` VARCHAR(500) NULL,
    `logoURL` VARCHAR(100) NULL,
    `email` VARCHAR(60) NULL
);
Query OK, 0 rows affected (0.00 sec)    

LOAD XML INFILE '/path/to/file/MerchantFeed_es.xml'
INTO TABLE `es_merchant`
CHARACTER SET 'utf8'
ROWS IDENTIFIED BY '<profile>';
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

SELECT
    `id`,
    `url`,
    `name`,
    `summary`,
    `logoURL`,
    `email`
FROM
    `es_merchant`;

+----------+-------------------------------------+----------------------+-------------------------------------------------------------------------------------------------+------------------------------------------------------------+------------------------------+
| id       | url                                 | name                 | summary                                                                                         | logoURL                                                    | email                        |
+----------+-------------------------------------+----------------------+-------------------------------------------------------------------------------------------------+------------------------------------------------------------+------------------------------+
| 11994513 | http://www.tool-fitness.es          | tool-fitness.es      | tienda especializada en máquinas de fitness y material de musculación a precios de descuento.   | http://r6.kelkoo.com//data/merchantlogos/11994513/tool.JPG | contacto@tool-fitness.es     |
| 13531013 | http://www.electrodomesticosweb.es/ | Electrodomesticosweb | tienda venta online de electrodomesticos para el hogar                                          | http://r6.kelkoo.com//data/merchantlogos/13531013/logo.jpg | info@electrodomesticosweb.es |
+----------+-------------------------------------+----------------------+-------------------------------------------------------------------------------------------------+------------------------------------------------------------+------------------------------+
2 rows in set (0.00 sec)
/* CODE FOR DEMONSTRATION PURPOSES */