I am working on a project where I have some xml data stored in an SQL Server database column of type Image. Apparently there is now an XML column available in SQL Server, but for some reason we don't use it.
I was working on a similar issue a few weeks ago and downloaded, SQL Lob Editor, which was really great. It allowed you to point to specific tables and columns and download all of the content to the file system. Unfortunately my trial expired and I rarely need this functionality so I didn't think the $75 would be worth it.
So again today I set out on Google trying to find some free tool to get this xml data. A colleague mentioned that there is a way to do it from SQL Server natively. A few minutes of searching and I found the query:
select convert(varchar(8000), convert(binary(8000), xml_data)) from letter
It is a simple select query that converts the image to binary and then the binary to a varchar.
That's all folks!!!
Unless your XML is more than 8000 chars converted to binary, then you're SOL. As you can change the above "convert(varchar(8000)..." to "convert(varchar(max)..." you cannot change "convert(binary(8000)..." to "convert(binary(max)..."
ReplyDeleteVery efficiently written information. It will be beneficial to anybody who utilizes it, including me. Keep up the good work. For sure i will check out more posts. This site seems to get a good amount of visitors. Text Marketing Auto Reply
ReplyDeleteThank you for information.
ReplyDeletehttps://serveria.com