Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Need to update value in SQL records with contents from external files
I have XML files in a folder. Each file has an associated record in a MySQL table with a field called 'xml'. I need to replace the contents of that field with the contents that are in the file. There is a field in the record that matches the file name of the xml file, so I'm looking for the most straight forward way to do this?
I'm assuming this is not possible with only a SQL statement that reaches out for a file name and uses the contents to update the field, but that would be my preference. Using some external script would be the next step, but that introduces some internal process complexities I'd like to avoid if at all possible.
I'm assuming this is not possible with only a SQL statement that reaches out for a file name and uses the contents to update the field, but that would be my preference. Using some external script would be the next step, but that introduces some internal process complexities I'd like to avoid if at all possible.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'm wondering if i can use the infile option in MySQL, but I'm not sure how to create a txt file out of a list of xml files?
Hi,
You have to create relevant project (for instance, C# project), to loop through the Xml file, to generate relevant Insert/Update statement, to apply to the database.
You have to create relevant project (for instance, C# project), to loop through the Xml file, to generate relevant Insert/Update statement, to apply to the database.
Please refer the below link which explains working with XML data in MySQL.
http://www.databasejournal.com/features/mysql/article.php/3846526/Working-with-XML-Data-in-MySQL.htm
Hope this helps you.
http://www.databasejournal.com/features/mysql/article.php/3846526/Working-with-XML-Data-in-MySQL.htm
Hope this helps you.
Have you tried to use Load_File(filename)
ref: MYSQL 5.7 Documentations - String functions
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.
If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.
The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.
mysql> UPDATE t SET blob_col=LOAD_FILE('/tmp/picture') WHERE id=1;
but I'm not sure how to create a txt file out of a list of xml files
You have to loop through each of the files in question. You cannot just merge these files into one (unless there is no document declaration in each xml file, and there is just an xml fragment in there). You can use athar13's LOAD_FILE for each file.
You have to loop through each of the files in question. You cannot just merge these files into one (unless there is no document declaration in each xml file, and there is just an xml fragment in there). You can use athar13's LOAD_FILE for each file.
Thank you for the responses, if the LoadFile option requires files to be on the server then I'll have to pass this up the chain as I only have remote access to the MySQL server, not file access to the server.
I guess I should mention the root cause of the question is that a whole host of files were loaded into the DB but in the transition, the character encoding was missing in the chain and now the records have special characters that appear as ?, I'm trying to find the most effective way of replace the bad data with the source files. The DB table and the files are both UTF-8, so I'm guessing something went wrong in the loading of the file.
I can certainly look into creating some program to do the looping through my files and create an update statement for each file. The idea of using the load file and combing xml files was to create the txt file I thought would be required that would look like this:
item_id, xml
12345, [contents of 12345.xml]
12346, [contents of 12346.zml]
etc.
I guess I should mention the root cause of the question is that a whole host of files were loaded into the DB but in the transition, the character encoding was missing in the chain and now the records have special characters that appear as ?, I'm trying to find the most effective way of replace the bad data with the source files. The DB table and the files are both UTF-8, so I'm guessing something went wrong in the loading of the file.
I can certainly look into creating some program to do the looping through my files and create an update statement for each file. The idea of using the load file and combing xml files was to create the txt file I thought would be required that would look like this:
item_id, xml
12345, [contents of 12345.xml]
12346, [contents of 12346.zml]
etc.
@musickmann. It was an important bit of info you chose to leave out.
The work around for this is to setup a local mysql instance with the only the required table(s) for this process. Use LOAD_FILE() to insert into your files into the local DB and then create an application that will transfer/upload the local data and/or DB to the remote server.
If would suggest take the time to download the data off the remote server to your local database and update those records. This way you can update your remote database with the local data, so your downtime would be less.
Hope this sorts your problem out.
The work around for this is to setup a local mysql instance with the only the required table(s) for this process. Use LOAD_FILE() to insert into your files into the local DB and then create an application that will transfer/upload the local data and/or DB to the remote server.
If would suggest take the time to download the data off the remote server to your local database and update those records. This way you can update your remote database with the local data, so your downtime would be less.
Hope this sorts your problem out.
Thanks athar13, sorry for leaving that bit out.
Based on your thoughts there, I'm thinking I won't be able to do this on my end. My limited access would be the issue, but at least now I think I have some info to have a good discussion with the other team that would be responsible for this.
One task they asked of me that I didn't understand, was that they wanted be to take the directory of xml files I have, then create a tab delimited file that had one column with the item ID and then one column contained the xml text (with special characters). I'm not sure of how to do that without writing yet another script, so I think Ive got what I need to move this discussion on to the next level.
Thanks!
Based on your thoughts there, I'm thinking I won't be able to do this on my end. My limited access would be the issue, but at least now I think I have some info to have a good discussion with the other team that would be responsible for this.
One task they asked of me that I didn't understand, was that they wanted be to take the directory of xml files I have, then create a tab delimited file that had one column with the item ID and then one column contained the xml text (with special characters). I'm not sure of how to do that without writing yet another script, so I think Ive got what I need to move this discussion on to the next level.
Thanks!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial