tag:blogger.com,1999:blog-15319370.post114202995799062998..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Importing XML data into MySQL using ExtractValue()rpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger47125tag:blogger.com,1999:blog-15319370.post-24044869065512686832020-02-04T17:32:36.711+01:002020-02-04T17:32:36.711+01:00hi, do i need to change the original xml file to h...hi, do i need to change the original xml file to have the "resultset" tag? thanks!simplymehttps://www.blogger.com/profile/09747798623170062360noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-40499929059999966192014-08-04T20:32:09.035+02:002014-08-04T20:32:09.035+02:00Punith, this sounds like a really specific request...Punith, this sounds like a really specific request. Please go to the mysql forums, or stackoverflow and post there. <br /><br />thank you.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-27847901019254966932014-08-04T12:59:37.838+02:002014-08-04T12:59:37.838+02:00I have a form at http://www.sledor.talk4fun.net/re...I have a form at http://www.sledor.talk4fun.net/results/results.html which shows results if a proper roll number is entered... the results are fetched from http://results.vtu.ac.in site and only the table is shown... now i want to add the data of the table to my mysql/database.<br /><br />Is there a way to do it..?<br /><br />for example if the roll number entered is 1db13cscs006<br /><br />the page will show the results as <br />Semester: 8 Result: FIRST CLASS WITH DISTINCTION<br /><br />Subject External Internal Total Result<br />Software Architectures (10IS81) 67 25 92 P<br />System Modeling and Simulation (10CS82) 73 24 97 P<br />Software Testing (10CS842) 48 24 72 P<br />Information and Network Security (10CS835) 64 25 89 P<br />Project Work (10CS85) 97 95 192 P<br />Seminar (10CS86) 0 47 47 P<br /><br /> <br />Total Marks: 589<br /><br /> <br />now i want the data from this table to be stored to my database so that if the same roll number is searched again... the result should be shown from my database and not from http://results.vtu.ac.in... help me out pls..Smile | Smile | :)Anonymoushttps://www.blogger.com/profile/05806170411319421319noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-53858868044216636832014-05-27T00:47:57.770+02:002014-05-27T00:47:57.770+02:00Thanks very much for posting this. 8 years later ...Thanks very much for posting this. 8 years later its helped me import my first XML file ;)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-65149056243952444162012-02-20T14:53:43.611+01:002012-02-20T14:53:43.611+01:00Serge, I know it's retarded but blogger commen...Serge, I know it's retarded but blogger comments doesn't escape XML itself. Please post again but escape the XML yourself so that<br /><br /><tagname> becomes &lt;tagname&gt;rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-86214970026424129762012-02-20T11:51:45.790+01:002012-02-20T11:51:45.790+01:00Hi,
Can someone help me with this. I am trying to...Hi,<br /><br />Can someone help me with this. I am trying to get the results from an xml string into mysql table.<br /><br />Here is a sample of the xml :<br /><br /><br /><br /><br /><br />I am trying to retrieve the <b>CountryCodeID, Canada, countryCode_2</b>, but i am currently stuck.<br /><br />ThanksSergehttps://www.blogger.com/profile/04777654644043252714noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-41677644780429670382012-01-23T08:10:19.432+01:002012-01-23T08:10:19.432+01:00Hello,
I am trying to use this example in what I ...Hello,<br /><br />I am trying to use this example in what I am trying to do. I am kind of a newbie with stored procedures in mySQL. I have this XML file that looks like this I would like to import into mySQL:<br /><br /><br />001/22/2012 21:50:581562181189<br />001/22/2012 21:50:571562181189<br /><br /><br />I want the script to create a table called temp in the database ted5000<br /><br />But I can't even get past the first part of my script:<br /><br />mysql> create procedure p_temp(p_xml text);<br />ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1<br /><br />Here is what I have as my sp so far:<br /><br />delimiter //<br /><br />drop procedure if exists p_temp<br />//<br /><br />create procedure p_temp(p_xml text)<br />begin<br /> declare v_row_index int unsigned default 0;<br /> declare v_row_count int unsigned;<br /> declare v_xpath_row varchar(255);<br /> <br /> --calculate the number of row elements.<br /> set v_row_count := extractValue(p_xml,'count(/History/SECOND)');<br /> <br /> -- loop through all the row elements<br /> while v_row_index < v_row_count do<br /> set v_row_index := v_row_index + 1;<br /> set v_xpath_row := concat(<br /> '/History/SECOND['<br /> , v_row_index<br /> , ']'<br /> );<br /> insert into temp values (<br /> extractValue(p_xml,concat(v_xpath_row,'/SECOND[1]/text()'))<br /> , extractValue(p_xml,concat(v_xpath_row,'/SECOND[2]/text()'))<br /> , extractValue(p_xml,concat(v_xpath_row,'/SECOND[3]/text()'))<br /> , extractValue(p_xml,concat(v_xpath_row,'/SECOND[4]/text()'))<br /> , extractValue(p_xml,concat(v_xpath_row,'/SECOND[5]/text()'))<br /> );<br /> end while;<br />end;<br />//<br /><br />If you could offer me a hand, I am kind of struggling here. Thank You!Greg Tompkinshttps://www.blogger.com/profile/01692478696315086890noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-45721204355804851792012-01-01T11:58:54.619+01:002012-01-01T11:58:54.619+01:00@Raptorak,
You should probably try the Streaming...@Raptorak, <br /><br />You should probably try the Streaming XML Input step in kettle (aka Pentaho Data Integration, and open source ETL tool, download at <a href="http://sourceforge.net/projects/pentaho/files/Data%20Integration/" rel="nofollow">http://sourceforge.net/projects/pentaho/files/Data%20Integration/</a>). See: <a href="http://wiki.pentaho.com/display/EAI/XML+Input+Stream+%28StAX%29" rel="nofollow">http://wiki.pentaho.com/display/EAI/XML+Input+Stream+%28StAX%29</a><br /><br />Alternatively, you could write a dedicated import program based on a SAX parser. (SAX is also the technique used by Kettle's streaming xml input step).rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-64230726596514414252012-01-01T11:57:31.418+01:002012-01-01T11:57:31.418+01:00This comment has been removed by the author.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-66861882835307100412012-01-01T08:32:41.607+01:002012-01-01T08:32:41.607+01:00This is a pretty old post but hopefully someone wi...This is a pretty old post but hopefully someone will read this... what method would one use to go about importing a very large XML file (such as 500-600 MB's) into a database?Raptorakhttp://www.rpgparadise.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-33848656406260816032011-11-12T05:41:31.051+01:002011-11-12T05:41:31.051+01:00xml is a very interesting language to be used and ...xml is a very interesting language to be used and contain the data object model or abbreviated DOM.tutorial very good and hopefully can help me in building a web application thanksandryhttp://library.gunadarma.ac.id/noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-23966289086124637182011-07-05T18:00:19.938+02:002011-07-05T18:00:19.938+02:00Fantastic this has just saved me
ThanksFantastic this has just saved me<br />ThanksAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-80588478447947306572010-04-21T02:18:52.498+02:002010-04-21T02:18:52.498+02:00James, I just completed some stuff that should hel...James, I just completed some stuff that should help you use your XML formatted MySQL dumps. You can find background information, as well as a link to the downloads and instructions here:<br /><br /><a href="http://rpbouman.blogspot.com/2010/04/restoring-xml-formatted-mysql-dumps.html" rel="nofollow">http://rpbouman.blogspot.com/2010/04/restoring-xml-formatted-mysql-dumps.html</a><br /><br />I hope this helps.<br /><br />kind regards,<br /><br />Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-33949205255327403322010-04-13T21:07:51.266+02:002010-04-13T21:07:51.266+02:00ugh...I meant to write "loading XML is *not* ...ugh...I meant to write "loading XML is *not* available in MySQL 5.1 (or lower for that matter).rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-42801263476958121112010-04-13T20:36:35.795+02:002010-04-13T20:36:35.795+02:00James, I am not kidding. The feature is available ...James, I am not kidding. The feature is available in MySQL 5.1. There is a LOAD XML feature available in he latest beta, MySQL 5.5. though:<br /><a href="http://dev.mysql.com/doc/refman/5.5/en/load-xml.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.5/en/load-xml.html</a><br /><br />If you have megabytes of XML to import, I advise either one of the following approaches:<br /><br />1) Write an XSLT stylesheet to tansform your XML to SQL. <br />2) Use kettle, a.k.a. Pentaho data integration. It comes with examples that show how to load XML, and you can dump that data directly into the database<br />3) write a custom script in php/perl/python etc.<br /><br />If you like I can help you and cobble up something to do approach 1.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-14023895679933728182010-04-13T20:29:51.577+02:002010-04-13T20:29:51.577+02:00You've got to be kidding.
I have sitting here ...You've got to be kidding.<br />I have sitting here megabytes of xml files dumped from a client that I need to import into a database. These xml file contain the ENTIRE database schema. You mean there is mysql 5.1+ DOESNT have a function that can simply import the xml files and create the db/tables/columns/data?Unknownhttps://www.blogger.com/profile/11265664600476642855noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-81623264851711446132009-12-24T10:58:04.939+01:002009-12-24T10:58:04.939+01:00Hi Arthy!
yes, you can use pentaho data integrat...Hi Arthy! <br /><br />yes, you can use pentaho data integration to import xml, in fact, I would recommend that method over using the MySQL functions I described in this article. <br /><br />With PDI, you can the XML input step to parse XML using an XPath expression. with the row denormalizer you can group key value pairs into a record. Whatever you can't solve with those steps, you can probably solve with a Javascript step and/or regexes. I have a practical example of XML import and export in My 2009 OSCON presentation (for details, slides, and code samples, go to <a href="http://rpbouman.blogspot.com/2009/07/oscon-2009-presentation-taming-your.html" rel="nofollow">http://rpbouman.blogspot.com/2009/07/oscon-2009-presentation-taming-your.html</a> and follow the links to the OSCON site)<br /><br />Now, I read you comment, and I suspect some info got lost by the blogger commenting system. I'd be happy to take a look if you could send your files to me by email (Roland.Bouman@gmail.com).<br /><br />regards, Roland Boumanrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-63834455046624099502009-12-24T05:40:11.607+01:002009-12-24T05:40:11.607+01:00Hi Roland,
I am using pentaho data integration to...Hi Roland,<br /><br />I am using pentaho data integration tool for reporting purpose.I am facing a problem when Transforming a dynamically generated xml into table output.<br /><br /><br /><br />Scenario is : Transformation of 3 files into DB. If the node is 'a' its value should be inserted into 'id' field if node name is !='a' it should be stored into table as 'Name' and 'value' fields.<br /><br />1.xml <br /><br />Content<br /><br />Quote:<br /><br /><a rel="nofollow"> 1</a><br /><b> b content</b><br /> <br /><br />2.xml <br /><br />Content<br /><br />Quote:<br /><br /><a rel="nofollow"> 1</a><br />c content<br /> <br /><br /><br />3.xml <br /><br />Content<br /><br />Quote:<br /><br /><a rel="nofollow"> 1</a><br />d content<br /> <br /><br /><br />I need to write a transformation that parses through these 3 files 1.xml , 2.xml and 3.xml<br /><br /><br />and produces a result of table containing<br /><br /><br />DB TABLE <br /><br />ID | Name | Value<br />_______________________________<br />1 | b | b content<br />1 | c | c content<br />1 | d | d content<br /><br /><br />Any Suggestions ? <br /><br />Thanks,<br />ArthyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-62764579014254621172009-12-15T09:20:15.265+01:002009-12-15T09:20:15.265+01:00Hi Anonymous,
no, I don't recall having any c...Hi Anonymous,<br /><br />no, I don't recall having any collation issues.<br /><br />From what can tell, the return value of extractValue() has the same collation as that of the first argument. So if there is any problem later on comparing the values, I should think that that would be caused by the (implicitly or explicitly) declared collation of the variables that you use to store the values in.<br /><br />I probably would try to explicitly declare my variables as having a specific unicode collation. For info on choosing the right unicode collation, read <a href="http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html</a><br /><br /><br />good luck,<br /><br />Roland.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-29052274951367731332009-12-15T04:26:00.479+01:002009-12-15T04:26:00.479+01:00Roland,
I am currently using your example for tes...Roland,<br /><br />I am currently using your example for testing a new method for doing insert into a table. However, I am running into an issue with collations. I realize it is probably something on my server, but I cannot seem to figure it out.<br /><br />You have the two statements below.<br /><br /> set v_row_count := extractValue(p_xml,'count(/resultset/row)');<br />extractValue(p_xml,concat(v_xpath_row,'/field[1]/text()'))<br /><br />the top statement is returning as a utf8_general_ci while the bottom statement is returning as a utf8_unicode_ci.<br /><br />Did you ever run across this during you testing and if so, how did you correct it? <br /><br />Thanks,Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-36341226034110806502009-11-02T01:53:30.821+01:002009-11-02T01:53:30.821+01:00Anonymous, I dont understand the question. How exa...Anonymous, I dont understand the question. How exactly does Bugzilla relate to this at all?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-60200164774328199562009-11-01T22:25:21.163+01:002009-11-01T22:25:21.163+01:00How to implement this with Bugzilla?How to implement this with Bugzilla?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-59771657470930530452009-09-25T09:20:02.167+02:002009-09-25T09:20:02.167+02:00Anonymous, yes - SQL injection should always be a ...Anonymous, yes - SQL injection should always be a concern. And, if you desire to parameterize the XPath expression, there is the additional concern of XPath injection.<br /><br />However, this article is simply showing the basics of working with ExtractValue function. Adding parameters or escaping would not be conducive to getting the point across.<br /><br />I probably should have added a line to mention that these are concerns are valid but unaddressed here. Thanks for pointing it out.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-47299778533717441022009-09-25T03:36:27.129+02:002009-09-25T03:36:27.129+02:00Roland,
If you did the insert dynamically as stat...Roland,<br /><br />If you did the insert dynamically as stated, would that not leave you open to SQL Injection Attacks?<br /><br />ThanksAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-54056280304879831682009-09-20T05:07:09.229+02:002009-09-20T05:07:09.229+02:00If you test it, please pass along the results as I...If you test it, please pass along the results as I would be interested in knowing as well being as I am rather new to MySQL.Anonymousnoreply@blogger.com