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.
LVL 2
musickmannData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

musickmannData AnalystAuthor Commented:
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?
0
HuaMin ChenProblem resolverCommented:
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.
0
meettechieCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Athar SyedCommented:
Have you tried to use Load_File(filename)


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;

Open in new window

ref: MYSQL 5.7 Documentations - String functions
0
MlandaTCommented:
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.
0
musickmannData AnalystAuthor Commented:
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.
0
Athar SyedCommented:
@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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
musickmannData AnalystAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.