{"id":10303,"date":"2014-04-12T14:43:41","date_gmt":"2014-04-12T12:43:41","guid":{"rendered":"http:\/\/flaven.fr\/?p=10303"},"modified":"2026-09-16T10:08:58","modified_gmt":"2026-09-16T08:08:58","slug":"xpath-xml-bigdata-rationnaliser-ces-donnees-manipulation-du-xml-a-laide-simplexml","status":"publish","type":"post","link":"https:\/\/flaven.fr\/2014\/04\/xpath-xml-bigdata-rationnaliser-ces-donnees-manipulation-du-xml-a-laide-simplexml\/","title":{"rendered":"XPath, XML, BigData – Rationnaliser ces donn\u00e9es, manipulation du XML \u00e0 l’aide SimpleXML"},"content":{"rendered":"


\n \t<\/p>\n

Comme dans le pr\u00e9c\u00e8dent billet, un autre petit billet sur la recherche d’une solution rapide et peu co\u00fbteuse pour r\u00e9cup\u00e9rer des donn\u00e9es \u00e9parses afin de les consolider ult\u00e9rieurement dans une base de donn\u00e9es.<\/p>\n

En clair, la cible comme aime \u00e0 dire les consultants : c’est r\u00e9cup\u00e9rer de gros fichiers de donn\u00e9es notamment au format XML, donc passablement structur\u00e9es, les “d\u00e9couper”, les passer \u00e0 la moulinette de SimpleXML pour g\u00e9n\u00e9rer des queries et injecter le tout dans une base de donn\u00e9es MySQL. Une base sur laquelle, vous pourrez requ\u00eater facilement, sortir de KPI, d\u00e9doublonner, corriger les corruptions de donn\u00e9es… Bref, transformer tel un docteur Frankenstein de la data, des donn\u00e9es mortes en donn\u00e9es vivantes et exploitables. <\/p>\n

Bien \u00e9videmment, il y a moyen d’industrialiser, automatiser ce type de manipulation mais volontairement on n’utilise que ce que des outils tr\u00e8s usuels. Certes, cela fait un “workflow” un peu chaotique mais qui permet de s\u00e9quentiellement voir chaque \u00e9tape<\/b><\/p>\n

Pour information, nous avons utiliser MAMP pour PHP et MySQL et la console MAC.<\/i><\/p>\n

1. XML \u00e0 la D\u00e9coupe<\/h4>\n

Vous \u00eates souvent face \u00e0 des fichiers imposants en XML, vous aurez donc un s\u00e9rieux probl\u00e8me \u00e0 charger le fichier ou les fichiers .xml<\/code>. Pour contrer, ce probl\u00e8me, vous pouvez indiquer dans votre fichier php que vous allez \u00e9tendre le param\u00e8tre memory_limit<\/code><\/p>\n

Dans votre fichier php<\/code><\/b><\/p>\n

ini_set(\"memory_limit\",\"12M\");<\/pre>\n
Dans le php.ini<\/code><\/b><\/p>\n
ini_set(\"memory_limit\",\"12M\");<\/pre>\n
Par ailleurs, vous pouvez d\u00e9couper pr\u00e9alablement le fichier .xml<\/code> volumes en fichiers plus petits de taille \u00e9gales.<\/p>\n
Un unique enregistrement de notre fichier xml sample_demo.xml<\/code>, un row<\/code><\/b><\/p>\n
\r\n  \r\n  <?xml version='1.0' encoding='utf-8'?>\r\n  <MEMBER_LIST>\r\n  <!-- \/\/ some records -->\r\n\r\n          <row>\r\n        <MEMBER num="1"><\/MEMBER>\r\n        <CAMPAGNE_ID>TT1114079764<\/CAMPAGNE_ID>\r\n        <MEMBER_ID>9437858932541<\/MEMBER_ID>\r\n        <CLIENT_ID>1187635315<\/CLIENT_ID>\r\n        <EMVHBQ><\/EMVHBQ>\r\n        <EMVISP><\/EMVISP>\r\n        <DATEJOIN>12\/05\/2011<\/DATEJOIN>\r\n        <DATEUNJOIN><\/DATEUNJOIN>\r\n        <EMAIL>barack.ndlf@lamaisonblanche.eu<\/EMAIL>\r\n        <EMVCELLPHONE><\/EMVCELLPHONE>\r\n        <EMVUNROUTABLE><\/EMVUNROUTABLE>\r\n        <HBQ_REASON><\/HBQ_REASON>\r\n        <SMS_DATE_UNJOIN><\/SMS_DATE_UNJOIN>\r\n        <SMS_EMVHBQ><\/SMS_EMVHBQ>\r\n        <SMS_HBQ_REASON><\/SMS_HBQ_REASON>\r\n        <FIRSTNAME>Barack<\/FIRSTNAME>\r\n        <LASTNAME>N'diang-Le floc'h<\/LASTNAME>\r\n        <TITLE><\/TITLE>\r\n        <DATEOFBIRTH><\/DATEOFBIRTH>\r\n        <ADRESSE><\/ADRESSE>\r\n        <ANNEE_NAISSANCE><\/ANNEE_NAISSANCE>\r\n        <CHAMP_DATE_1><\/CHAMP_DATE_1>\r\n        <CHAMP_DATE_2><\/CHAMP_DATE_2>\r\n        <CHAMP_DATE_3><\/CHAMP_DATE_3>\r\n        <CHAMP_DATE_4><\/CHAMP_DATE_4>\r\n        <CHAMP_DATE_5><\/CHAMP_DATE_5>\r\n        <CHAMP_NUM_1><\/CHAMP_NUM_1>\r\n        <CHAMP_NUM_2><\/CHAMP_NUM_2>\r\n        <CHAMP_NUM_3><\/CHAMP_NUM_3>\r\n        <CHAMP_NUM_4><\/CHAMP_NUM_4>\r\n        <CHAMP_NUM_5><\/CHAMP_NUM_5>\r\n        <CHAMP_TXT_3><\/CHAMP_TXT_3>\r\n        <CHAMP_TXT_4><\/CHAMP_TXT_4>\r\n        <CHAMP_TXT_5><\/CHAMP_TXT_5>\r\n        <CLIENTURN><\/CLIENTURN>\r\n        <CODE><\/CODE>\r\n        <CODEPOSTAL><\/CODEPOSTAL>\r\n        <COUNTRY><\/COUNTRY>\r\n        <CRENEAU_HORAIRE><\/CRENEAU_HORAIRE>\r\n        <DATE_ABO_ALERT><\/DATE_ABO_ALERT>\r\n        <DATE_ABO_AUTOPROMO><\/DATE_ABO_AUTOPROMO>\r\n        <DATE_ABO_BESTOF_OBS><\/DATE_ABO_BESTOF_OBS>\r\n        <DATE_ABO_BESTOF_WEEK><\/DATE_ABO_BESTOF_WEEK>\r\n        <DATE_ABO_BESTOF_W_END><\/DATE_ABO_BESTOF_W_END>\r\n        <DATE_ABO_NEWS><\/DATE_ABO_NEWS>\r\n        <DATE_DEMANDE_MODIFICATION_PROF><\/DATE_DEMANDE_MODIFICATION_PROF>\r\n        <DATE_DESABO_ALERT><\/DATE_DESABO_ALERT>\r\n        <DATE_DESABO_AUTOPROMO><\/DATE_DESABO_AUTOPROMO>\r\n        <DATE_DESABO_BESTOF_OBS><\/DATE_DESABO_BESTOF_OBS>\r\n        <DATE_DESABO_BESTOF_WEEK><\/DATE_DESABO_BESTOF_WEEK>\r\n        <DATE_DESABO_BESTOF_W_END><\/DATE_DESABO_BESTOF_W_END>\r\n        <DATE_DESABO_NEWS><\/DATE_DESABO_NEWS>\r\n        <DATE_ENVOI_PDM><\/DATE_ENVOI_PDM>\r\n        <DATE_MODIFICATION_PROFIL><\/DATE_MODIFICATION_PROFIL>\r\n        <DATE_MODIF_NEWS><\/DATE_MODIF_NEWS>\r\n        <EMAIL_FORMAT><\/EMAIL_FORMAT>\r\n        <EMAIL_ORIGINE><\/EMAIL_ORIGINE>\r\n        <EMVADMIN1><\/EMVADMIN1>\r\n        <EMVADMIN2><\/EMVADMIN2>\r\n        <EMVADMIN3><\/EMVADMIN3>\r\n        <EMVADMIN4><\/EMVADMIN4>\r\n        <EMVADMIN5><\/EMVADMIN5>\r\n        <EMVDOUBLON><\/EMVDOUBLON>\r\n        <FAI><\/FAI>\r\n        <FREQUENCE><\/FREQUENCE>\r\n        <GENDER><\/GENDER>\r\n        <HEURE_ENVOI><\/HEURE_ENVOI>\r\n        <LASTACTIVITY><\/LASTACTIVITY>\r\n        <LG><\/LG>\r\n        <LHE><\/LHE>\r\n        <MESSAGEID_PDM><\/MESSAGEID_PDM>\r\n        <MOBICLIP><\/MOBICLIP>\r\n        <NEWS_MENU><\/NEWS_MENU>\r\n        <OPTINALERT><\/OPTINALERT>\r\n        <OPTINMONFRANCE><\/OPTINMONFRANCE>\r\n        <OPTINPARTENAIRES><\/OPTINPARTENAIRES>\r\n        <OPTIN_ACTU_AFRIQUE>1<\/OPTIN_ACTU_AFRIQUE>\r\n        <OPTIN_ACTU_MONDE>1<\/OPTIN_ACTU_MONDE>\r\n        <OPTIN_ACTU_MUSIQUE>False<\/OPTIN_ACTU_MUSIQUE>\r\n        <OPTIN_AUTOPROMO><\/OPTIN_AUTOPROMO>\r\n        <OPTIN_BESTOF_OBS><\/OPTIN_BESTOF_OBS>\r\n        <OPTIN_BESTOF_WEEK><\/OPTIN_BESTOF_WEEK>\r\n        <OPTIN_BESTOF_W_END><\/OPTIN_BESTOF_W_END>\r\n        <OPTIN_MFI><\/OPTIN_MFI>\r\n        <OPTIN_NEWSLETTER5><\/OPTIN_NEWSLETTER5>\r\n        <OPTIN_NEWSLETTERS><\/OPTIN_NEWSLETTERS>\r\n        <OPTIN_OFFRE_PARTENAIRE><\/OPTIN_OFFRE_PARTENAIRE>\r\n        <OPTIN_OFFRE_RFI><\/OPTIN_OFFRE_RFI>\r\n        <OP_MARKETING><\/OP_MARKETING>\r\n        <OP_WEBMARKETING><\/OP_WEBMARKETING>\r\n        <PAYS><\/PAYS>\r\n        <PROFESSION><\/PROFESSION>\r\n        <SEED><\/SEED>\r\n        <SEGMENT><\/SEGMENT>\r\n        <SOURCE>IHH<\/SOURCE>\r\n        <SYNCHRO_INSERT><\/SYNCHRO_INSERT>\r\n        <SYNCHRO_UNJOIN><\/SYNCHRO_UNJOIN>\r\n        <SYNCHRO_UPDATE><\/SYNCHRO_UPDATE>\r\n        <TELEPHONE><\/TELEPHONE>\r\n        <TEMP10_NBMAX><\/TEMP10_NBMAX>\r\n        <TEMP10_TOPIC><\/TEMP10_TOPIC>\r\n        <TEMP11_NBMAX><\/TEMP11_NBMAX>\r\n        <TEMP11_TOPIC><\/TEMP11_TOPIC>\r\n        <TEMP1_NBMAX><\/TEMP1_NBMAX>\r\n        <TEMP1_TOPIC><\/TEMP1_TOPIC>\r\n        <TEMP2_NBMAX><\/TEMP2_NBMAX>\r\n        <TEMP2_TOPIC><\/TEMP2_TOPIC>\r\n        <TEMP3_NBMAX><\/TEMP3_NBMAX>\r\n        <TEMP3_TOPIC><\/TEMP3_TOPIC>\r\n        <TEMP4_NBMAX><\/TEMP4_NBMAX>\r\n        <TEMP4_TOPIC><\/TEMP4_TOPIC>\r\n        <TEMP5_NBMAX><\/TEMP5_NBMAX>\r\n        <TEMP5_TOPIC><\/TEMP5_TOPIC>\r\n        <TEMP6_NBMAX><\/TEMP6_NBMAX>\r\n        <TEMP6_TOPIC><\/TEMP6_TOPIC>\r\n        <TEMP7_NBMAX><\/TEMP7_NBMAX>\r\n        <TEMP7_TOPIC><\/TEMP7_TOPIC>\r\n        <TEMP8_NBMAX><\/TEMP8_NBMAX>\r\n        <TEMP8_TOPIC><\/TEMP8_TOPIC>\r\n        <TEMP9_NBMAX><\/TEMP9_NBMAX>\r\n        <TEMP9_TOPIC><\/TEMP9_TOPIC>\r\n        <TEMPLATE_TYPE><\/TEMPLATE_TYPE>\r\n        <UPLOAD_ID><\/UPLOAD_ID>\r\n        <VILLE><\/VILLE>\r\n        <ZONE><\/ZONE>\r\n  <\/row>\r\n\r\n  <!-- \/\/ some records -->\r\n  <\/MEMBER_LIST>\r\n  \r\n<\/pre>\n
On peut donc consid\u00e9rer que notre gros fichier contient 2 190 enregistrements et qu’un enregistrement (row) fait 129 lignes.<\/p>\n
Si on souhaite scinder le gros fichier en petits fichiers de taille \u00e9gale de 500 enregistrements, un rapide calcul nous le donne (282 449x500)\/2 190 = 64 500<\/code>, soit 5 fichiers.<\/b><\/p>\n
Scinder le gros fichier .xml<\/code> “quick and dirty”<\/b><\/p>\n
\r\nsplit -l 103 200 \/path_to_file\/big_file.xml \/path_to_file\/destination\/small_file_\r\n<\/pre>\n
Ajouter l’extension .xml aux fichiers small_file_aa small_file_ab small_file_ac…etc.<\/b><\/p>\n
\r\nfor f in $(find . -type f); do mv $f ${f}.xml; done\r\n<\/pre>\n
Attention, la d\u00e9coupe est faite \u00e0 la hache, v\u00e9rifier tout de m\u00eame de pas scinder au milieu un enregistrement, d’une balise <row>...<\/row><\/code>.<\/b><\/p>\n
2. Utiliser SimpleXML<\/h4>\n
SimpleXML est une extension de PHP 5 destin\u00e9e \u00e0 simplifier la manipulation des documents XML. Par rapport \u00e0 DOM ou au parser Expat, SimpleXML peut \u00eatre utilis\u00e9 en quelques lignes de code pour lire et extraire des donn\u00e9es de fichiers XML, notamment en manipulant les noeuds (nodes) ou les attributs (attributes) de votre fichier .xml<\/code>.<\/p>\n
Nous allons nous servir d’un petit script .php<\/code> pour se servir de SimpleXML<\/code> et g\u00e9n\u00e9rer des requ\u00eates d’insertion sql<\/code> pour charger un fichier .sql<\/code> qui reprend les donn\u00e9es de notre XML bien gras que nous venons d’\u00e9carir \u00e0 l’aide de split.<\/b><\/p>\n
La base de donn\u00e9es et mettre la table<\/b><\/p>\n
    \n
  • Database: fsb_cpg_opened<\/li>\n
  • Table: member_list_index_full<\/li>\n<\/ul>\n
    \r\n  -- --------------------------------------------------------\r\n\r\n  --\r\n  -- Table structure for table `member_list_index_full`\r\n  --\r\n\r\n  CREATE TABLE `member_list_index_full` (\r\n    `id` int(11) NOT NULL AUTO_INCREMENT,\r\n    `campagne_id` text,\r\n    `member_id` text,\r\n    `client_id` text,\r\n    `emvhbq` text,\r\n    `emvisp` text,\r\n    `datejoin` text,\r\n    `dateunjoin` text,\r\n    `email` varchar(255) NOT NULL DEFAULT '',\r\n    `emvcellphone` text,\r\n    `emvunroutable` text,\r\n    `hbq_reason` text,\r\n    `sms_date_unjoin` text,\r\n    `sms_emvhbq` text,\r\n    `sms_hbq_reason` text,\r\n    `firstname` varchar(255) NOT NULL DEFAULT '',\r\n    `lastname` varchar(255) NOT NULL DEFAULT '',\r\n    `title` text,\r\n    `dateofbirth` text,\r\n    `adresse` text,\r\n    `annee_naissance` text,\r\n    `champ_date_1` text,\r\n    `champ_date_2` text,\r\n    `champ_date_3` text,\r\n    `champ_date_4` text,\r\n    `champ_date_5` text,\r\n    `champ_num_1` text,\r\n    `champ_num_2` text,\r\n    `champ_num_3` text,\r\n    `champ_num_4` text,\r\n    `champ_num_5` text,\r\n    `champ_txt_3` text,\r\n    `champ_txt_4` text,\r\n    `champ_txt_5` text,\r\n    `clienturn` text,\r\n    `code` text,\r\n    `codepostal` text,\r\n    `country` text,\r\n    `creneau_horaire` text,\r\n    `date_abo_alert` text,\r\n    `date_abo_autopromo` text,\r\n    `date_abo_bestof_obs` text,\r\n    `date_abo_bestof_week` text,\r\n    `date_abo_bestof_w_end` text,\r\n    `date_abo_news` text,\r\n    `date_demande_modification_prof` text,\r\n    `date_desabo_alert` text,\r\n    `date_desabo_autopromo` text,\r\n    `date_desabo_bestof_obs` text,\r\n    `date_desabo_bestof_week` text,\r\n    `date_desabo_bestof_w_end` text,\r\n    `date_desabo_news` text,\r\n    `date_envoi_pdm` text,\r\n    `date_modification_profil` text,\r\n    `date_modif_news` text,\r\n    `email_format` text,\r\n    `email_origine` text,\r\n    `emvadmin1` text,\r\n    `emvadmin2` text,\r\n    `emvadmin3` text,\r\n    `emvadmin4` text,\r\n    `emvadmin5` text,\r\n    `emvdoublon` text,\r\n    `fai` text,\r\n    `frequence` text,\r\n    `gender` text,\r\n    `heure_envoi` text,\r\n    `lastactivity` text,\r\n    `lg` text,\r\n    `lhe` text,\r\n    `messageid_pdm` text,\r\n    `mobiclip` text,\r\n    `news_menu` text,\r\n    `optinalert` text,\r\n    `optinmonfrance` text,\r\n    `optinpartenaires` text,\r\n    `optin_actu_afrique` text,\r\n    `optin_actu_monde` text,\r\n    `optin_actu_musique` text,\r\n    `optin_autopromo` text,\r\n    `optin_bestof_obs` text,\r\n    `optin_bestof_week` text,\r\n    `optin_bestof_w_end` text,\r\n    `optin_mfi` text,\r\n    `optin_newsletter5` text,\r\n    `optin_newsletters` text,\r\n    `optin_offre_partenaire` text,\r\n    `optin_offre_rfi` text,\r\n    `op_marketing` text,\r\n    `op_webmarketing` text,\r\n    `pays` text,\r\n    `profession` text,\r\n    `seed` text,\r\n    `segment` text,\r\n    `source` text,\r\n    `synchro_insert` text,\r\n    `synchro_unjoin` text,\r\n    `synchro_update` text,\r\n    `telephone` text,\r\n    `temp10_nbmax` text,\r\n    `temp10_topic` text,\r\n    `temp11_nbmax` text,\r\n    `temp11_topic` text,\r\n    `temp1_nbmax` text,\r\n    `temp1_topic` text,\r\n    `temp2_nbmax` text,\r\n    `temp2_topic` text,\r\n    `temp3_nbmax` text,\r\n    `temp3_topic` text,\r\n    `temp4_nbmax` text,\r\n    `temp4_topic` text,\r\n    `temp5_nbmax` text,\r\n    `temp5_topic` text,\r\n    `temp6_nbmax` text,\r\n    `temp6_topic` text,\r\n    `temp7_nbmax` text,\r\n    `temp7_topic` text,\r\n    `temp8_nbmax` text,\r\n    `temp8_topic` text,\r\n    `temp9_nbmax` text,\r\n    `temp9_topic` text,\r\n    `template_type` text,\r\n    `upload_id` text,\r\n    `ville` text,\r\n    `zone` text,\r\n    PRIMARY KEY (`id`),\r\n    FULLTEXT KEY `ind_full_email` (`email`),\r\n    FULLTEXT KEY `ind_full_firstname` (`firstname`),\r\n    FULLTEXT KEY `ind_full_lastname` (`lastname`),\r\n    FULLTEXT KEY `ind_full_ema_fir_las` (`email`,`firstname`,`lastname`)\r\n  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;\r\n<\/pre>\n
    3. G\u00e9n\u00e9rer l’ensemble des requ\u00eates pour injecter le contenu du XML dans la BDD<\/h4>\n
    On va se servir d’un mod\u00e8le de requ\u00eate tout b\u00eate d’insertion. Faite attention, lorsque vous h\u00e9ritez des donn\u00e9es de langue \u00e9trang\u00e8re, le fran\u00e7ais est \u00e0 ce titre une langue \u00e9trang\u00e8re, il faut faire une petite “sanitization”  de vos donn\u00e9es avant des les injecter dans votre requ\u00eate SQL. Prenons par exemple, une personne fictive dot\u00e9e d’un nom d’origine bretonne et africaine, nommons-le Barack N'diang-Le floc'h<\/code>. Cet individu est donc dot\u00e9 d’une double apostrophe, il faut donc prot\u00e9ger avec des addslashes<\/code> le nom de ce brave homme, ce qui donne N\\'diang-Le floc\\'h<\/code>.<\/p>\n
    fsb_cpg_opened_massive_insert.php<\/code><\/p>\n
    \r\n  \/*  Source : http:\/\/www.phpro.org\/tutorials\/Introduction-To-SimpleXML-With-PHP.html *\/\r\n\r\n\r\n  \/*** create a SimpleXML object ***\/ \r\n      if( ! $xml = simplexml_load_file(\"sample_demo.xml\") ) \r\n      { \r\n      echo \"Unable to load XML file\"; \r\n      } else { \r\n            $i = 0; \r\n            \/*** loop over the elements ***\/ \r\n            foreach($xml as $node) { \r\n                \r\n                \/* MODEL *\/\r\n                \/*\r\n                <\/MEMBER>\r\n                        TT1114079764<\/CAMPAGNE_ID>\r\n                        9437858932541<\/MEMBER_ID>\r\n                        1187635315<\/CLIENT_ID>\r\n                        <\/EMVHBQ>\r\n                        <\/EMVISP>\r\n                        12\/05\/2011<\/DATEJOIN>\r\n                        <\/DATEUNJOIN>\r\n                        barack.ndlf@lamaisonblanche.eu<\/EMAIL>\r\n                        <\/EMVCELLPHONE>\r\n                        <\/EMVUNROUTABLE>\r\n                        <\/HBQ_REASON>\r\n                        <\/SMS_DATE_UNJOIN>\r\n                        <\/SMS_EMVHBQ>\r\n                        <\/SMS_HBQ_REASON>\r\n                        Barack<\/FIRSTNAME>\r\n                        N'diang-Le floc'h<\/LASTNAME>\r\n                        <\/TITLE>\r\n                        ... to be continued\r\n                *\/\r\n                \r\n                \/* VALUES *\/\r\n                $MEMBER_num = $xml->row[$i]->MEMBER->attributes();\r\n                $CAMPAGNE_ID = $xml->row[$i]->CAMPAGNE_ID;\r\n                $MEMBER_ID = $xml->row[$i]->MEMBER_ID;\r\n                $CLIENT_ID = $xml->row[$i]->CLIENT_ID;\r\n                $EMVHBQ = $xml->row[$i]->EMVHBQ;\r\n                $EMVISP = $xml->row[$i]->EMVISP;\r\n                $DATEJOIN = $xml->row[$i]->DATEJOIN;\r\n                $EMAIL = $xml->row[$i]->EMAIL;\r\n                $EMVCELLPHONE = $xml->row[$i]->EMVCELLPHONE;\r\n                $EMVUNROUTABLE = $xml->row[$i]->EMVUNROUTABLE;\r\n                $HBQ_REASON = $xml->row[$i]->HBQ_REASON;\r\n                $SMS_DATE_UNJOIN = $xml->row[$i]->SMS_DATE_UNJOIN;\r\n                $SMS_EMVHBQ = $xml->row[$i]->SMS_EMVHBQ;\r\n                $SMS_HBQ_REASON = $xml->row[$i]->SMS_HBQ_REASON;\r\n                $FIRSTNAME = $xml->row[$i]->FIRSTNAME;\r\n                $LASTNAME = $xml->row[$i]->LASTNAME;\r\n                $TITLE = $xml->row[$i]->TITLE;\r\n                $DATEOFBIRTH = $xml->row[$i]->DATEOFBIRTH;\r\n                $ADRESSE = $xml->row[$i]->ADRESSE;\r\n                $ANNEE_NAISSANCE = $xml->row[$i]->ANNEE_NAISSANCE;\r\n                $CHAMP_DATE_1 = $xml->row[$i]->CHAMP_DATE_1;\r\n                $CHAMP_DATE_2 = $xml->row[$i]->CHAMP_DATE_2;\r\n                $CHAMP_DATE_3 = $xml->row[$i]->CHAMP_DATE_3;\r\n                $CHAMP_DATE_4 = $xml->row[$i]->CHAMP_DATE_4;\r\n                $CHAMP_DATE_5 = $xml->row[$i]->CHAMP_DATE_5;\r\n                $CHAMP_NUM_1 = $xml->row[$i]->CHAMP_NUM_1;\r\n                $CHAMP_NUM_2 = $xml->row[$i]->CHAMP_NUM_2;\r\n                $CHAMP_NUM_3 = $xml->row[$i]->CHAMP_NUM_3;\r\n                $CHAMP_NUM_4 = $xml->row[$i]->CHAMP_NUM_4;\r\n                $CHAMP_NUM_5 = $xml->row[$i]->CHAMP_NUM_5;\r\n                $CHAMP_TXT_3 = $xml->row[$i]->CHAMP_TXT_3;\r\n                $CHAMP_TXT_4 = $xml->row[$i]->CHAMP_TXT_4;\r\n                $CHAMP_TXT_5 = $xml->row[$i]->CHAMP_TXT_5;\r\n                $CLIENTURN = $xml->row[$i]->CLIENTURN;\r\n                $CODE = $xml->row[$i]->CODE;\r\n                $CODEPOSTAL = $xml->row[$i]->CODEPOSTAL;\r\n                $COUNTRY = $xml->row[$i]->COUNTRY;\r\n                $CRENEAU_HORAIRE = $xml->row[$i]->CRENEAU_HORAIRE;\r\n                $DATE_ABO_ALERT = $xml->row[$i]->DATE_ABO_ALERT;\r\n                $DATE_ABO_AUTOPROMO = $xml->row[$i]->DATE_ABO_AUTOPROMO;\r\n                $DATE_ABO_BESTOF_OBS = $xml->row[$i]->DATE_ABO_BESTOF_OBS;\r\n                $DATE_ABO_BESTOF_WEEK = $xml->row[$i]->DATE_ABO_BESTOF_WEEK;\r\n                $DATE_ABO_BESTOF_W_END = $xml->row[$i]->DATE_ABO_BESTOF_W_END;\r\n                $DATE_ABO_NEWS = $xml->row[$i]->DATE_ABO_NEWS;\r\n                $DATE_DEMANDE_MODIFICATION_PROF = $xml->row[$i]->DATE_DEMANDE_MODIFICATION_PROF;\r\n                $DATE_DESABO_ALERT = $xml->row[$i]->DATE_DESABO_ALERT;\r\n                $DATE_DESABO_AUTOPROMO = $xml->row[$i]->DATE_DESABO_AUTOPROMO;\r\n                $DATE_DESABO_BESTOF_OBS = $xml->row[$i]->DATE_DESABO_BESTOF_OBS;\r\n                $DATE_DESABO_BESTOF_WEEK = $xml->row[$i]->DATE_DESABO_BESTOF_WEEK;\r\n                $DATE_DESABO_BESTOF_W_END = $xml->row[$i]->DATE_DESABO_BESTOF_W_END;\r\n                $DATE_DESABO_NEWS = $xml->row[$i]->DATE_DESABO_NEWS;\r\n                $DATE_ENVOI_PDM = $xml->row[$i]->DATE_ENVOI_PDM;\r\n                $DATE_MODIFICATION_PROFIL = $xml->row[$i]->DATE_MODIFICATION_PROFIL;\r\n                $DATE_MODIF_NEWS = $xml->row[$i]->DATE_MODIF_NEWS;\r\n                $EMAIL_FORMAT = $xml->row[$i]->EMAIL_FORMAT;\r\n                $EMAIL_ORIGINE = $xml->row[$i]->EMAIL_ORIGINE;\r\n                $EMVADMIN1 = $xml->row[$i]->EMVADMIN1;\r\n                $EMVADMIN2 = $xml->row[$i]->EMVADMIN2;\r\n                $EMVADMIN3 = $xml->row[$i]->EMVADMIN3;\r\n                $EMVADMIN4 = $xml->row[$i]->EMVADMIN4;\r\n                $EMVADMIN5 = $xml->row[$i]->EMVADMIN5;\r\n                $EMVDOUBLON = $xml->row[$i]->EMVDOUBLON;\r\n                $FAI = $xml->row[$i]->FAI;\r\n                $FREQUENCE = $xml->row[$i]->FREQUENCE;\r\n                $GENDER = $xml->row[$i]->GENDER;\r\n                $HEURE_ENVOI = $xml->row[$i]->HEURE_ENVOI;\r\n                $LASTACTIVITY = $xml->row[$i]->LASTACTIVITY;\r\n                $LG = $xml->row[$i]->LG;\r\n                $LHE = $xml->row[$i]->LHE;\r\n                $MESSAGEID_PDM = $xml->row[$i]->MESSAGEID_PDM;\r\n                $MOBICLIP = $xml->row[$i]->MOBICLIP;\r\n                $NEWS_MENU = $xml->row[$i]->NEWS_MENU;\r\n                $OPTINALERT = $xml->row[$i]->OPTINALERT;\r\n                $OPTINMONFRANCE = $xml->row[$i]->OPTINMONFRANCE;\r\n                $OPTINPARTENAIRES = $xml->row[$i]->OPTINPARTENAIRES;\r\n                $OPTIN_ACTU_AFRIQUE = $xml->row[$i]->OPTIN_ACTU_AFRIQUE;\r\n                $OPTIN_ACTU_MONDE = $xml->row[$i]->OPTIN_ACTU_MONDE;\r\n                $OPTIN_ACTU_MUSIQUE = $xml->row[$i]->OPTIN_ACTU_MUSIQUE;\r\n                $OPTIN_AUTOPROMO = $xml->row[$i]->OPTIN_AUTOPROMO;\r\n                $OPTIN_BESTOF_OBS = $xml->row[$i]->OPTIN_BESTOF_OBS;\r\n                $OPTIN_BESTOF_WEEK = $xml->row[$i]->OPTIN_BESTOF_WEEK;\r\n                $OPTIN_BESTOF_W_END = $xml->row[$i]->OPTIN_BESTOF_W_END;\r\n                $OPTIN_MFI = $xml->row[$i]->OPTIN_MFI;\r\n                $OPTIN_NEWSLETTER5 = $xml->row[$i]->OPTIN_NEWSLETTER5;\r\n                $OPTIN_NEWSLETTERS = $xml->row[$i]->OPTIN_NEWSLETTERS;\r\n                $OPTIN_OFFRE_PARTENAIRE = $xml->row[$i]->OPTIN_OFFRE_PARTENAIRE;\r\n                $OPTIN_OFFRE_RFI = $xml->row[$i]->OPTIN_OFFRE_RFI;\r\n                $OP_MARKETING = $xml->row[$i]->OP_MARKETING;\r\n                $OP_WEBMARKETING = $xml->row[$i]->OP_WEBMARKETING;\r\n                $PAYS = $xml->row[$i]->PAYS;\r\n                $PROFESSION = $xml->row[$i]->PROFESSION;\r\n                $SEED = $xml->row[$i]->SEED;\r\n                $SEGMENT = $xml->row[$i]->SEGMENT;\r\n                $SOURCE = $xml->row[$i]->SOURCE;\r\n                $SYNCHRO_INSERT = $xml->row[$i]->SYNCHRO_INSERT;\r\n                $SYNCHRO_UNJOIN = $xml->row[$i]->SYNCHRO_UNJOIN;\r\n                $SYNCHRO_UPDATE = $xml->row[$i]->SYNCHRO_UPDATE;\r\n                $TELEPHONE = $xml->row[$i]->TELEPHONE;\r\n                $TEMP10_NBMAX = $xml->row[$i]->TEMP10_NBMAX;\r\n                $TEMP10_TOPIC = $xml->row[$i]->TEMP10_TOPIC;\r\n                $TEMP11_NBMAX = $xml->row[$i]->TEMP11_NBMAX;\r\n                $TEMP11_TOPIC = $xml->row[$i]->TEMP11_TOPIC;\r\n                $TEMP1_NBMAX = $xml->row[$i]->TEMP1_NBMAX;\r\n                $TEMP1_TOPIC = $xml->row[$i]->TEMP1_TOPIC;\r\n                $TEMP2_NBMAX = $xml->row[$i]->TEMP2_NBMAX;\r\n                $TEMP2_TOPIC = $xml->row[$i]->TEMP2_TOPIC;\r\n                $TEMP3_NBMAX = $xml->row[$i]->TEMP3_NBMAX;\r\n                $TEMP3_TOPIC = $xml->row[$i]->TEMP3_TOPIC;\r\n                $TEMP4_NBMAX = $xml->row[$i]->TEMP4_NBMAX;\r\n                $TEMP4_TOPIC = $xml->row[$i]->TEMP4_TOPIC;\r\n                $TEMP5_NBMAX = $xml->row[$i]->TEMP5_NBMAX;\r\n                $TEMP5_TOPIC = $xml->row[$i]->TEMP5_TOPIC;\r\n                $TEMP6_NBMAX = $xml->row[$i]->TEMP6_NBMAX;\r\n                $TEMP6_TOPIC = $xml->row[$i]->TEMP6_TOPIC;\r\n                $TEMP7_NBMAX = $xml->row[$i]->TEMP7_NBMAX;\r\n                $TEMP7_TOPIC = $xml->row[$i]->TEMP7_TOPIC;\r\n                $TEMP8_NBMAX = $xml->row[$i]->TEMP8_NBMAX;\r\n                $TEMP8_TOPIC = $xml->row[$i]->TEMP8_TOPIC;\r\n                $TEMP9_NBMAX = $xml->row[$i]->TEMP9_NBMAX;\r\n                $TEMP9_TOPIC = $xml->row[$i]->TEMP9_TOPIC;\r\n                $TEMPLATE_TYPE = $xml->row[$i]->TEMPLATE_TYPE;\r\n                $UPLOAD_ID = $xml->row[$i]->UPLOAD_ID;\r\n                $VILLE = $xml->row[$i]->VILLE;\r\n                $ZONE = $xml->row[$i]->ZONE;\r\n                \r\n                \/* DEBUG *\/\r\n                \/\/ print_r($node); \r\n                \r\n                \/* MODEL QUERY *\/\r\n\r\n                \/*\r\n                INSERT INTO fsb_cpg_opened.member_list_index_full (id, campagne_id, member_id, client_id, emvhbq, emvisp, datejoin, dateunjoin, email, emvcellphone, emvunroutable, hbq_reason, sms_date_unjoin, sms_emvhbq, sms_hbq_reason, firstname, lastname, title, dateofbirth, adresse, annee_naissance, champ_date_1, champ_date_2, champ_date_3, champ_date_4, champ_date_5, champ_num_1, champ_num_2, champ_num_3, champ_num_4, champ_num_5, champ_txt_3, champ_txt_4, champ_txt_5, clienturn, code, codepostal, country, creneau_horaire, date_abo_alert, date_abo_autopromo, date_abo_bestof_obs, date_abo_bestof_week, date_abo_bestof_w_end, date_abo_news, date_demande_modification_prof, date_desabo_alert, date_desabo_autopromo, date_desabo_bestof_obs, date_desabo_bestof_week, date_desabo_bestof_w_end, date_desabo_news, date_envoi_pdm, date_modification_profil, date_modif_news, email_format, email_origine, emvadmin1, emvadmin2, emvadmin3, emvadmin4, emvadmin5, emvdoublon, fai, frequence, gender, heure_envoi, lastactivity, lg, lhe, messageid_pdm, mobiclip, news_menu, optinalert, optinmonfrance, optinpartenaires, optin_actu_afrique, optin_actu_monde, optin_actu_musique, optin_autopromo, optin_bestof_obs, optin_bestof_week, optin_bestof_w_end, optin_mfi, optin_newsletter5, optin_newsletters, optin_offre_partenaire, optin_offre_rfi, op_marketing, op_webmarketing, pays, profession, seed, segment, source, synchro_insert, synchro_unjoin, synchro_update, telephone, temp10_nbmax, temp10_topic, temp11_nbmax, temp11_topic, temp1_nbmax, temp1_topic, temp2_nbmax, temp2_topic, temp3_nbmax, temp3_topic, temp4_nbmax, temp4_topic, temp5_nbmax, temp5_topic, temp6_nbmax, temp6_topic, temp7_nbmax, temp7_topic, temp8_nbmax, temp8_topic, temp9_nbmax, temp9_topic, template_type, upload_id, ville, zone) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);\r\n                *\/\r\n                \r\n                echo ('INSERT INTO fsb_cpg_opened.member_list_index_full (id, campagne_id, member_id, client_id, emvhbq, emvisp, datejoin, dateunjoin, email, emvcellphone, emvunroutable, hbq_reason, sms_date_unjoin, sms_emvhbq, sms_hbq_reason, firstname, lastname, title, dateofbirth, adresse, annee_naissance, champ_date_1, champ_date_2, champ_date_3, champ_date_4, champ_date_5, champ_num_1, champ_num_2, champ_num_3, champ_num_4, champ_num_5, champ_txt_3, champ_txt_4, champ_txt_5, clienturn, code, codepostal, country, creneau_horaire, date_abo_alert, date_abo_autopromo, date_abo_bestof_obs, date_abo_bestof_week, date_abo_bestof_w_end, date_abo_news, date_demande_modification_prof, date_desabo_alert, date_desabo_autopromo, date_desabo_bestof_obs, date_desabo_bestof_week, date_desabo_bestof_w_end, date_desabo_news, date_envoi_pdm, date_modification_profil, date_modif_news, email_format, email_origine, emvadmin1, emvadmin2, emvadmin3, emvadmin4, emvadmin5, emvdoublon, fai, frequence, gender, heure_envoi, lastactivity, lg, lhe, messageid_pdm, mobiclip, news_menu, optinalert, optinmonfrance, optinpartenaires, optin_actu_afrique, optin_actu_monde, optin_actu_musique, optin_autopromo, optin_bestof_obs, optin_bestof_week, optin_bestof_w_end, optin_mfi, optin_newsletter5, optin_newsletters, optin_offre_partenaire, optin_offre_rfi, op_marketing, op_webmarketing, pays, profession, seed, segment, source, synchro_insert, synchro_unjoin, synchro_update, telephone, temp10_nbmax, temp10_topic, temp11_nbmax, temp11_topic, temp1_nbmax, temp1_topic, temp2_nbmax, temp2_topic, temp3_nbmax, temp3_topic, temp4_nbmax, temp4_topic, temp5_nbmax, temp5_topic, temp6_nbmax, temp6_topic, temp7_nbmax, temp7_topic, temp8_nbmax, temp8_topic, temp9_nbmax, temp9_topic, template_type, upload_id, ville, zone) VALUES');\r\n                \r\n                echo ('(NULL,');                  \r\n                echo ('\\''.$MEMBER_num.'\\',');\r\n                echo ('\\''.$CAMPAGNE_ID.'\\',');\r\n                echo ('\\''.$MEMBER_ID.'\\',');\r\n                echo ('\\''.$CLIENT_ID.'\\',');\r\n                echo ('\\''.$EMVHBQ.'\\',');\r\n                echo ('\\''.$EMVISP.'\\',');\r\n                echo ('\\''.$DATEJOIN.'\\',');\r\n                echo ('\\''.$EMAIL.'\\',');\r\n                echo ('\\''.$EMVCELLPHONE.'\\',');\r\n                echo ('\\''.$EMVUNROUTABLE.'\\',');\r\n                echo ('\\''.$HBQ_REASON.'\\',');\r\n                echo ('\\''.$SMS_DATE_UNJOIN.'\\',');\r\n                echo ('\\''.$SMS_EMVHBQ.'\\',');\r\n                echo ('\\''.$SMS_HBQ_REASON.'\\',');\r\n                echo ('\\''.addslashes($FIRSTNAME).'\\',');\r\n                echo ('\\''.addslashes($LASTNAME).'\\',');\r\n                echo ('\\''.$TITLE.'\\',');\r\n                echo ('\\''.$DATEOFBIRTH.'\\',');\r\n                echo ('\\''.$ADRESSE.'\\',');\r\n                echo ('\\''.$ANNEE_NAISSANCE.'\\',');\r\n                echo ('\\''.$CHAMP_DATE_1.'\\',');\r\n                echo ('\\''.$CHAMP_DATE_2.'\\',');\r\n                echo ('\\''.$CHAMP_DATE_3.'\\',');\r\n                echo ('\\''.$CHAMP_DATE_4.'\\',');\r\n                echo ('\\''.$CHAMP_DATE_5.'\\',');\r\n                echo ('\\''.$CHAMP_NUM_1.'\\',');\r\n                echo ('\\''.$CHAMP_NUM_2.'\\',');\r\n                echo ('\\''.$CHAMP_NUM_3.'\\',');\r\n                echo ('\\''.$CHAMP_NUM_4.'\\',');\r\n                echo ('\\''.$CHAMP_NUM_5.'\\',');\r\n                echo ('\\''.$CHAMP_TXT_3.'\\',');\r\n                echo ('\\''.$CHAMP_TXT_4.'\\',');\r\n                echo ('\\''.$CHAMP_TXT_5.'\\',');\r\n                echo ('\\''.$CLIENTURN.'\\',');\r\n                echo ('\\''.$CODE.'\\',');\r\n                echo ('\\''.$CODEPOSTAL.'\\',');\r\n                echo ('\\''.$COUNTRY.'\\',');\r\n                echo ('\\''.$CRENEAU_HORAIRE.'\\',');\r\n                echo ('\\''.$DATE_ABO_ALERT.'\\',');\r\n                echo ('\\''.$DATE_ABO_AUTOPROMO.'\\',');\r\n                echo ('\\''.$DATE_ABO_BESTOF_OBS.'\\',');\r\n                echo ('\\''.$DATE_ABO_BESTOF_WEEK.'\\',');\r\n                echo ('\\''.$DATE_ABO_BESTOF_W_END.'\\',');\r\n                echo ('\\''.$DATE_ABO_NEWS.'\\',');\r\n                echo ('\\''.$DATE_DEMANDE_MODIFICATION_PROF.'\\',');\r\n                echo ('\\''.$DATE_DESABO_ALERT.'\\',');\r\n                echo ('\\''.$DATE_DESABO_AUTOPROMO.'\\',');\r\n                echo ('\\''.$DATE_DESABO_BESTOF_OBS.'\\',');\r\n                echo ('\\''.$DATE_DESABO_BESTOF_WEEK.'\\',');\r\n                echo ('\\''.$DATE_DESABO_BESTOF_W_END.'\\',');\r\n                echo ('\\''.$DATE_DESABO_NEWS.'\\',');\r\n                echo ('\\''.$DATE_ENVOI_PDM.'\\',');\r\n                echo ('\\''.$DATE_MODIFICATION_PROFIL.'\\',');\r\n                echo ('\\''.$DATE_MODIF_NEWS.'\\',');\r\n                echo ('\\''.$EMAIL_FORMAT.'\\',');\r\n                echo ('\\''.$EMAIL_ORIGINE.'\\',');\r\n                echo ('\\''.$EMVADMIN1.'\\',');\r\n                echo ('\\''.$EMVADMIN2.'\\',');\r\n                echo ('\\''.$EMVADMIN3.'\\',');\r\n                echo ('\\''.$EMVADMIN4.'\\',');\r\n                echo ('\\''.$EMVADMIN5.'\\',');\r\n                echo ('\\''.$EMVDOUBLON.'\\',');\r\n                echo ('\\''.$FAI.'\\',');\r\n                echo ('\\''.$FREQUENCE.'\\',');\r\n                echo ('\\''.$GENDER.'\\',');\r\n                echo ('\\''.$HEURE_ENVOI.'\\',');\r\n                echo ('\\''.$LASTACTIVITY.'\\',');\r\n                echo ('\\''.$LG.'\\',');\r\n                echo ('\\''.$LHE.'\\',');\r\n                echo ('\\''.$MESSAGEID_PDM.'\\',');\r\n                echo ('\\''.$MOBICLIP.'\\',');\r\n                echo ('\\''.$NEWS_MENU.'\\',');\r\n                echo ('\\''.$OPTINALERT.'\\',');\r\n                echo ('\\''.$OPTINMONFRANCE.'\\',');\r\n                echo ('\\''.$OPTINPARTENAIRES.'\\',');\r\n                echo ('\\''.$OPTIN_ACTU_AFRIQUE.'\\',');\r\n                echo ('\\''.$OPTIN_ACTU_MONDE.'\\',');\r\n                echo ('\\''.$OPTIN_ACTU_MUSIQUE.'\\',');\r\n                echo ('\\''.$OPTIN_AUTOPROMO.'\\',');\r\n                echo ('\\''.$OPTIN_BESTOF_OBS.'\\',');\r\n                echo ('\\''.$OPTIN_BESTOF_WEEK.'\\',');\r\n                echo ('\\''.$OPTIN_BESTOF_W_END.'\\',');\r\n                echo ('\\''.$OPTIN_MFI.'\\',');\r\n                echo ('\\''.$OPTIN_NEWSLETTER5.'\\',');\r\n                echo ('\\''.$OPTIN_NEWSLETTERS.'\\',');\r\n                echo ('\\''.$OPTIN_OFFRE_PARTENAIRE.'\\',');\r\n                echo ('\\''.$OPTIN_OFFRE_RFI.'\\',');\r\n                echo ('\\''.$OP_MARKETING.'\\',');\r\n                echo ('\\''.$OP_WEBMARKETING.'\\',');\r\n                echo ('\\''.$PAYS.'\\',');\r\n                echo ('\\''.$PROFESSION.'\\',');\r\n                echo ('\\''.$SEED.'\\',');\r\n                echo ('\\''.$SEGMENT.'\\',');\r\n                echo ('\\''.$SOURCE.'\\',');\r\n                echo ('\\''.$SYNCHRO_INSERT.'\\',');\r\n                echo ('\\''.$SYNCHRO_UNJOIN.'\\',');\r\n                echo ('\\''.$SYNCHRO_UPDATE.'\\',');\r\n                echo ('\\''.$TELEPHONE.'\\',');\r\n                echo ('\\''.$TEMP10_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP10_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP11_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP11_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP1_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP1_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP2_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP2_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP3_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP3_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP4_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP4_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP5_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP5_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP6_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP6_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP7_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP7_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP8_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP8_TOPIC.'\\',');\r\n                echo ('\\''.$TEMP9_NBMAX.'\\',');\r\n                echo ('\\''.$TEMP9_TOPIC.'\\',');\r\n                echo ('\\''.$TEMPLATE_TYPE.'\\',');\r\n                echo ('\\''.$UPLOAD_ID.'\\',');\r\n                echo ('\\''.$VILLE.'\\',');\r\n                \r\n                \/* end *\/\r\n                \r\n                echo ('\\''.$ZONE.'\\'');\r\n                \/* very end*\/\r\n                echo (');');\r\n                \r\n                \r\n                echo \"\\n\\n\";\r\n                $i++;\r\n            } \r\n\r\n      }\/\/ EOI  \r\n<\/pre>\n<pre lang=\"sql\">\r\n  INSERT INTO fsb_cpg_opened.member_list_index_full (id, campagne_id, member_id, client_id, emvhbq, emvisp, datejoin, dateunjoin, email, emvcellphone, emvunroutable, hbq_reason, sms_date_unjoin, sms_emvhbq, sms_hbq_reason, firstname, lastname, title, dateofbirth, adresse, annee_naissance, champ_date_1, champ_date_2, champ_date_3, champ_date_4, champ_date_5, champ_num_1, champ_num_2, champ_num_3, champ_num_4, champ_num_5, champ_txt_3, champ_txt_4, champ_txt_5, clienturn, code, codepostal, country, creneau_horaire, date_abo_alert, date_abo_autopromo, date_abo_bestof_obs, date_abo_bestof_week, date_abo_bestof_w_end, date_abo_news, date_demande_modification_prof, date_desabo_alert, date_desabo_autopromo, date_desabo_bestof_obs, date_desabo_bestof_week, date_desabo_bestof_w_end, date_desabo_news, date_envoi_pdm, date_modification_profil, date_modif_news, email_format, email_origine, emvadmin1, emvadmin2, emvadmin3, emvadmin4, emvadmin5, emvdoublon, fai, frequence, gender, heure_envoi, lastactivity, lg, lhe, messageid_pdm, mobiclip, news_menu, optinalert, optinmonfrance, optinpartenaires, optin_actu_afrique, optin_actu_monde, optin_actu_musique, optin_autopromo, optin_bestof_obs, optin_bestof_week, optin_bestof_w_end, optin_mfi, optin_newsletter5, optin_newsletters, optin_offre_partenaire, optin_offre_rfi, op_marketing, op_webmarketing, pays, profession, seed, segment, source, synchro_insert, synchro_unjoin, synchro_update, telephone, temp10_nbmax, temp10_topic, temp11_nbmax, temp11_topic, temp1_nbmax, temp1_topic, temp2_nbmax, temp2_topic, temp3_nbmax, temp3_topic, temp4_nbmax, temp4_topic, temp5_nbmax, temp5_topic, temp6_nbmax, temp6_topic, temp7_nbmax, temp7_topic, temp8_nbmax, temp8_topic, temp9_nbmax, temp9_topic, template_type, upload_id, ville, zone) VALUES(NULL,'1','TT1114079764','9437858932541','1187635315','','','12\/05\/2011','barack.ndlf@lamaisonblanche.eu','','','','','','','Barack','N\\'diang-Le floc\\'h','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','1','1','False','','','','','','','','','','','','','','','','IHH','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');\r\n<\/pre>\n<h4>4. Injecter le contenu et nettoyer la BDD des corruptions li\u00e9es au caract\u00e8re encoding<\/h4>\n<p>Bon disons que vous \u00eates parvenu(e) \u00e0 cr\u00e9er votre requ\u00eate et \u00e0 l’injecter.<\/p>\n<pre lang=\"sql\">\r\n  -- se connecter \u00e0 MySQL\r\n  \/Applications\/MAMP\/Library\/bin\/mysql -u root -p\r\n\r\n  -- Utiliser \u00e0 la BDD\r\n  USE rfi_cpg_opened;\r\n  \r\n  -- Vider la table \"aucazou\"\r\n  TRUNCATE TABLE member_list_index_full;\r\n  \r\n  -- Injecter le fichier .sql\r\n  SOURCE \/path_to_your_sql_file\/insert_full_from_xml.sql;\r\n<\/pre>\n<p>Vous pouvez d\u00e9sormais nettoyer les probl\u00e8mes li\u00e9s au mauvais encodage des caract\u00e8res : pays, title, firstname, lastname, profession.<\/p>\n<ul>\n<li><b>Exemple simple de nettoyage du champ <code>pays<\/code><\/b>\n<pre lang=\"sql\">\r\n  update `member_list_index_full` set `pays` = replace(`pays` ,'\u00c3\u00a8','\u00e8');\r\n<\/pre>\n<\/li>\n<li><b>Exemple plus avanc\u00e9 de nettoyage du champ <code>ville<\/code><\/b>\n<pre lang=\"sql\">\r\n--- nettoyage du champ ville\r\nupdate `member_list_index_full` set ville = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(ville,'ü','\u00fc'),'ô','\u00f4'),'ó','\u00f3'),'ê','\u00ea'),'à','\u00e0'),'ã','\u00e3'),'Ü','\u00dc'),'Ô','\u00d4'),'Ó','\u00d3'),'Ê','\u00ca'),'À','\u00c0'),'Ã','\u00c3'),'Ç','\u00c7'),'Ú','\u00da'),'Õ','\u00d5'),'Í','\u00cd'),'Í','\u00cd'),'É','\u00c9'),'Â','\u00c2'),'Á','\u00c1'),'ç','\u00e7'),'ú','\u00fa'),'õ','\u00f5'),'í','\u00ed'),'é','\u00e9'),'â','\u00e2'),'á','\u00e1'),'ã','\u00e3'),'ç','\u00e7'),'\u00c3 ','\u00e0'),'\u00c3 ','\u00e0'),'\u00c2\u00ba','\u00ba'),'\u00c2\u00aa','\u00aa'),'\u00c3\u00a7','\u00e7'),'\u00e2\u20ac\u201c','\u2013'),'\u00c3\u00b3','\u00f3'),'\u00c3\u00a9','\u00e9'),'\u00c3\u00a1','\u00e1'),'\u00c3\u00aa','\u00ea'),'\u00c3\u00a3','\u00e3'),'\u00c3\u00a2','\u00e2'),'\u00c3\u00ad','\u00ed'),'\u00c3\u00b5','\u00f5'),'\u00c3\u02dc','\u00d8'),'\u00e2\u20ac\u00a2','-'),'\u00c3\u00ba','\u00fa'),'\u00c3 ','\u00c0'),'\u00c3\u0192','\u00c3'),'\u00c3\u2021','\u00c7'),'\u00e2\u20ac','\"'),'\u00e2\u20ac\u0153','\"'),'\u00c3\u2030','\u00c9');\r\n<\/pre>\n<\/li>\n<\/ul>\n<p><b>Conclusion : Il est certain que c’est un peu fastidieux comme proc\u00e9dure mais c’est parfois un mal n\u00e9cessaire pour pouvoir exploiter des donn\u00e9es trop longtemps endormies.<\/b><\/p>\n<h2>En savoir plus<\/h2>\n<ul>\n<li>SimpleXML Manipulation XML<br \/><a href=\"http:\/\/php.net\/manual\/fr\/book.simplexml.php\" target=\"_blank\">http:\/\/php.net\/manual\/fr\/book.simplexml.php<\/a><\/li>\n<li>Introduction To SimpleXML With PHP<br \/><a href=\"http:\/\/www.phpro.org\/tutorials\/Introduction-To-SimpleXML-With-PHP.html\" target=\"_blank\">http:\/\/www.phpro.org\/tutorials\/Introduction-To-SimpleXML-With-PHP.html<\/a><\/li>\n<li>PHP SimpleXML par w3schools.com<br \/><a href=\"http:\/\/www.w3schools.com\/php\/php_xml_simplexml.asp\" target=\"_blank\">http:\/\/www.w3schools.com\/php\/php_xml_simplexml.asp<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Comme dans le pr\u00e9c\u00e8dent billet, un autre petit billet sur la recherche d’une solution rapide et peu co\u00fbteuse pour r\u00e9cup\u00e9rer des donn\u00e9es \u00e9parses afin de… <\/p>\n<p class=\"text-center\"><a href=\"https:\/\/flaven.fr\/2014\/04\/xpath-xml-bigdata-rationnaliser-ces-donnees-manipulation-du-xml-a-laide-simplexml\/\" class=\"more-link\">Continue reading → <span class=\"screen-reader-text\">XPath, XML, BigData – Rationnaliser ces donn\u00e9es, manipulation  du XML \u00e0 l’aide SimpleXML<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":10305,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"bf_ai_meta_description":"Rationnalisez vos donn\u00e9es XML avec SimpleXML. Manipulez fichiers XML, g\u00e9n\u00e9rez des queries et injectez-les dans MySQL pour des analyses efficaces.","bf_ai_og_title":"Rationnaliser XML avec SimpleXML","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[3441,3444,3447,3449,3435],"tags":[2084,3493,2085,609,468,2211,90,2089],"class_list":["post-10303","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analytics","category-programming-databases","category-technology-trends","category-tutorials-how-to","category-web-development","tag-bigdata","tag-clip","tag-dette","tag-donnees","tag-marketing","tag-mysql","tag-xml","tag-xpath"],"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3Vuhl-2Gb","jetpack_featured_media_url":"https:\/\/flaven.fr\/wp-content\/uploads\/2014\/04\/xml_transfert_xpath_bf.jpg","_links":{"self":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/10303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/comments?post=10303"}],"version-history":[{"count":4,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/10303\/revisions"}],"predecessor-version":[{"id":10308,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/10303\/revisions\/10308"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/media\/10305"}],"wp:attachment":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/media?parent=10303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/categories?post=10303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/tags?post=10303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}