What is the easiest way to search and replace in text files encoded UTF-16?

Question

I'm trying to update a series of xml files by changing names that they reference. I have a table of names that have changed, column for the current name and a column for the name to replace with.

I looked for ways to script search and replace and found sed. It seemed like a good choice until I ran my first attempt. On inspecting the file, I found several unrecognized characters between every carriage return and line feed. I did some searching and found that there are issues with sed used on utf-16 files.

These are text files that I'm searching, though there are copies stored in a SQL Server table in a column with type xml. When updating or populating the database, the fields are always set like ColumnName=N'xmltext'.

I'm interested in how I could update those fields also, though the main question is how best to update the text files.

I'm using extended regular expressions, my search is for ([>\.])OldName([<\.]) and I replace it with \1NewName\2

Answer 1

You will likely have to load the XML files into an actual XML parser and then use XPath and/or XQuery statements to locate the elements that contain the name value you are looking for. Once you know the specific element(s), you can simply assign the new value to them directly, and then re-save the XML back to file.

As for SQL Server, its XML data type natively supports XPath and XQuery statements for searching and updating element data. So you can probably use the same XPath/XQuery statements in both situations.