kathygriffin Posted March 5, 2014 Share Posted March 5, 2014 I am using xampp 1.8.2 I am trying to load a very large xml file into myself. The file will run for about 3 hours then end but not import anything. I am not sure whatI am doing incorrectly. my steps 1. run shell 2. cd mysql/bin 3. Now, run the command: mysql.exe --user=root --password= then once I am in USE stuffdb; LOAD XML LOCAL INFILE 'C:/Users/computeruser/Desktop/stuff.xml' INTO TABLEstuff_load(stuff1,stuff2, stuff3, stuff4, stuff5, stuff6, stuff7, stuff8, stuff9) /g Then after that runs for 3 hours then it shows 0 rows changed. Thanks Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted March 10, 2014 Author Share Posted March 10, 2014 bump.... Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 10, 2014 Share Posted March 10, 2014 (edited) a) how large is the file, how many rows of data does it contain? b) does the file contain properly formatted <row></row> elements that mysql would be able to use by default to find the data? Edited March 10, 2014 by mac_gyver Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted March 11, 2014 Author Share Posted March 11, 2014 (edited) a) how large is the file, how many rows of data does it contain? b) does the file contain properly formatted <row></row> elements that mysql would be able to use by default to find the data? A. Some files are 100 mb some others are about 200 mb. Some part numbers contains more data then others. B. I'm not seeing row anywhere. I have provided a example below. Thanks <doc><field name="id">22439789</field><field name="prtnmbr">22439789</field><field name="subprtnmbr">22439930</field><field name="mannmbr">00955tr</field><field name="listprc">370.95</field><field name="updatedate">2014-01-01T01:56:03Z</field><field name="iname"><![CDATA[TIRE 190/55 ZR17 PILOT ROAD3 B]]></field><field name="vname"><![CDATA[MICHELIN]]></field><field name="atrribute"><![CDATA[BIAS / RADIAL, RADIAL]]></field><field name="atrributevalueid">11</field><field name="atrribute"><![CDATA[SPEED RATING, (W)->168 MPH]]></field><field name="atrributevalueid">118</field><field name="atrribute"><![CDATA[LOAD INDEX, 75-853 LBS]]></field><field name="atrributevalueid">154</field><field name="producttype"><![CDATA[TIRES]]></field><field name="atrribute"><![CDATA[FADE, REAR]]></field><field name="atrributevalueid">464</field><field name="atrribute"><![CDATA[TUBE TYPE, TUBELESS]]></field><field name="atrributevalueid">556</field><field name="atrribute"><![CDATA[TIRE INNER DIAMETER, 17 INCH]]></field><field name="atrributevalueid">561</field><field name="atrribute"><![CDATA[WIDTH, 190 MM]]></field><field name="atrributevalueid">588</field><field name="atrribute"><![CDATA[ASPECT RATIO, 55]]></field><field name="atrributevalueid">593</field><field name="atrribute"><![CDATA[PURPOSE, STREET]]></field><field name="atrributevalueid">608</field><field name="atrribute"><![CDATA[TIRE LINE, PILOT ROAD]]></field><field name="atrributevalueid">677</field><field name="atrribute"><![CDATA[TIRE BRAND, MICHELIN]]></field><field name="atrributevalueid">689</field><field name="atrribute"><![CDATA[VERSION, LOADED/TWO-UP]]></field><field name="atrributevalueid">692</field></doc> Edited March 11, 2014 by kathygriffin Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 11, 2014 Share Posted March 11, 2014 i recommend reading the mysql documentation for what you are doing - http://dev.mysql.com/doc/refman/5.5/en/load-xml.html based on what i know, you will need to use - ROWS IDENTIFIED BY '<doc>' Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted March 11, 2014 Author Share Posted March 11, 2014 i recommend reading the mysql documentation for what you are doing - http://dev.mysql.com/doc/refman/5.5/en/load-xml.html based on what i know, you will need to use - ROWS IDENTIFIED BY '<doc>' I did look at what you sent you but I am a bit confused here so if I have it right I would have to use it like this >>> USE stuffdb; LOAD XML LOCAL INFILE 'C:/Users/computeruser/Desktop/stuff.xml' INTO TABLE stuff_load (stuff1,stuff2, stuff3, stuff4, stuff5, stuff6, stuff7, stuff8, stuff9) ROWS IDENTIFIED BY '<doc>' /g Correct? Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted March 11, 2014 Author Share Posted March 11, 2014 Actually I just tried USE stuffdb; LOAD XML LOCAL INFILE 'C:/Users/computeruser/Desktop/stuff.xml' INTO TABLE stuff_load ROWS IDENTIFIED BY '<doc>' ; /g I will find out in about 4 hours if it worked.. Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted March 14, 2014 Author Share Posted March 14, 2014 How would I extract xml to go into the correct rows where the xml stuff1 is in there 4 times. I can't use stuff1 in mysql column because it will not allow me to use the same name... Any suggestions? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 14, 2014 Share Posted March 14, 2014 cannot really help you without specific information. the general answer to programming questions is yes, you can do just about anything you want if you write the code that does it. is this xml data intended to be directly imported into a database (it must be in a specific format, as shown at the link to the documentation that i posted above.) Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted March 14, 2014 Author Share Posted March 14, 2014 cannot really help you without specific information. the general answer to programming questions is yes, you can do just about anything you want if you write the code that does it. is this xml data intended to be directly imported into a database (it must be in a specific format, as shown at the link to the documentation that i posted above.) Sorry.. Heres what I am working with USE things; LOAD XML LOCAL INFILE 'C:/Users/computer/Documents/items.xml' INTO TABLE everything ROWS IDENTIFIED BY '<doc>'; /g Now in the xml file I have more than one field name that is named the same thing. For example I have 3 stuff2 and 4 stuff3s In my mysql table with the code I am using about it inserts only the first field of stuff2 and first field of stuff3s. So I setup stuff2 then stuff2a then stuff2b how would I get it setup to into the mysql column names that do match. Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted March 20, 2014 Author Share Posted March 20, 2014 bump Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted April 6, 2014 Author Share Posted April 6, 2014 bump Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 6, 2014 Share Posted April 6, 2014 just bumping your thread, in addition to being against the forum rules, unless you are adding or clarifying information, won't get you any help. no one has answered because the information you have supplied isn't specific. without actual or made-up xml data, that someone could copy and try, in the format you actually have in your xml file, no one here can tell you why what you are doing isn't working or what you would need to change to get this to work. Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted April 21, 2014 Author Share Posted April 21, 2014 Thank you for letting me. Ok Here is what I am dealing with. Only its about 200,000 items. <?xml version="1.0" encoding="UTF-8"?><add><doc><field name="id">something</field><field name="other1">something2</field><field name="other3">something3</field><field name="other4">something4</field><field name="other5">something5</field><field name="other6">something6</field><field name="other7"><![CDATA[something7]]></field><field name="other8"><![CDATA[something8]]></field><field name="other9"><![CDATA[something9.jpg]]></field><field name="other10a"><![CDATA[<p></p>]]></field><field name="other10">something10</field><field name="other11">something11</field><field name="other12">something12</field><field name="other13">something13</field><field name="other14">something14</field><field name="other15">something15</field><field name="other16">something16</field><field name="other17">something17</field><field name="other18">something18</field><field name="other19">something19</field></doc></add> Quote Link to comment Share on other sites More sharing options...
kathygriffin Posted April 22, 2014 Author Share Posted April 22, 2014 Actually I did that wrong It will look like this. <?xml version="1.0" encoding="UTF-8"?><add><doc><field name="id">something</field><field name="other1">something2</field><field name="other3">something3</field><field name="other4">something4</field><field name="other5">something5</field><field name="other6">something6</field><field name="other7"><![CDATA[something7]]></field><field name="other8"><![CDATA[something8]]></field><field name="other9"><![CDATA[something9.jpg]]></field><field name="other10a"><![CDATA[<p></p>]]></field><field name="other10">something10</field><field name="other11">something11</field><field name="other12">something12</field><field name="other13">something13</field><field name="other14">something14</field><field name="other16">something15</field><field name="other16">something16</field><field name="other18">something17</field><field name="other18">something18</field><field name="other18">something19</field></doc></add> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.