UltraEdit Text Editor 15

Most SQL Server DBA’s are used to dealing with data that comes in via some other method than a database connection; flat files, csv’s, and XML files are all too common.  However, as databases get larger, and XML becomes more prevalent, I don’t think I’m alone in suggesting that Notepad doesn’t really cut it as a text editor anymore.

Quick case in point; last week I was charged with importing a 32 Meg XML file into a database so that we could use some of the values to update one of our older security scanning tools. Unfortunately, while the XML file was valid (e.g., all tags were closed), SQL Server was screaming about certain characters not being valid XML characters.  Rendering the file in IE 7 killed IE.  Wordpad would open the file, but 32 megs of text yields something along the lines of 300,000 characters; not exactly easy to scan and edit.

Enter UltraEdit.  I downloaded the 45-day free trial, and went to work.  SQL Server identified the line and character position of the invalid characters in the xml column in my scratch table; I opened the file in UltraEdit, used the Goto Line command (including the column number) and discovered the first invalid character: the trademark symbol, or ™.  I edited it out, used the find and replace feature to find the rest of them and do the same, and uploaded the file to SQL Server again.  Using XQuery on the new imported contents gave me a new character position, so I repeated the process.  After about 10 repeats (less than an hour of time), I had a clean XML file, and was off to the races.

UltraEdit was also very useful when querying the file; I could use the XML manager to explore and identify the nodes far better than the XML parser included in SQL Server Management Studio (which choked when trying to open the whole XML value).  This was a great help when attempting to write valid XQuery statements to select particular nodes, attributes, and values from the database.

I realize that UltraEdit is more than an XML editor, and that there are probably better XML editors out there, but I was very pleased with how easy it was to use it for this particular project.  I’m hoping that I’ll have time to more fully explore it’s capabilities for authoring scripts, etc, in the future.  The cost for a license is only $49.95, and it was well worth it to solve this particular problem.