mysql "LINES STARTING BY" node with attribute



How can I import XML file with following syntax load into mysql table using Load data or load XML. XML Format:



<?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://ift.tt/1JlstcH;
<profile>
<summary>tienda especializada en máquinas de fitness y material de musculación a precios de descuento.</summary>
<logo>
<logoURL>http://ift.tt/1FRTqPG;
</logo>
</profile>
<email>contacto@tool-fitness.es</email>
</merchant>

<merchant id="13531013">
<name>Electrodomesticosweb</name>
<url>http://ift.tt/1Jlsqxx;
<profile>
<summary>tienda venta online de electrodomesticos para el hogar</summary>
<logo> <logoURL>http://ift.tt/1FRTqPM;
</logo>
</profile>
<email>info@electrodomesticosweb.es</email>
</merchant>
</merchants>


First I tried this, but it enters only one record



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


Next, when I'm trying to do so with these code, it failed:



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


Any help?


No comments:

Post a Comment