Convert character field to xml data type
Importing contents of xml documents into varchar(max) field to be used by an outside application. No immediate plans to really query or change the data in SQL Server, but that could change. Would we be able to convert to an xml data type? Is it better to change the data type of the field, coppy to a different field that is an xml type? Anyting to be concerned about? Note: the original xml document files would not be available to import again. Edit: could use xml data type but it is not necessary for the external application.
Best Answer
If you use the XML data type, you would need to make sure they are valid XML AFAIK, so taking portions of an XML and importing them would need to be done carefully.
XML data type allows you to do native XML processing, although you could always cast the VARCHAR(MAX) data to XML when you want to do XML based processing.
I don't think you would see a great performance difference if you only plan to store the data as is.
I would like to add a word of caution regarding importing the contents of xml documents into varchar(max) field. If you do have some sort of front end code which uses XmlReader to read the contents of a given document, then you will be inevitably subjected to ask the reader to ReadOuterXml() and feed the resulting string as a value of the respective procedure parameter. If the size of your document goes to 100s MB then XmlReader might throw **out of memory exception**. In a mean time, if you instead opt to use the path to the xml file then there is no limit, and the speed of execution is considerably faster, i.e.
declare @xml xml;
select
@xml = t.BulkColumn
from openrowset(bulk 'some_unc_path', single_blob) t;
update dbo.YourTable
set
YourColumn = @xml -- cast(@xml as varchar(max))
where some_condition_is_met;
Microsoft strongly recommends using single_blob when populating xml variables (it is much more efficient as it matches how xml is stored internally).
Using xml instead of varchar(max) will allow you to better utilize a full text indexing if needed.
It also goes without saying that as William already noted, native xml is easy to process.
Excellent point Oleg +1 - I was hoping you would add something to this question, you seem to be the resident XML doctor, and as usual have provided a great explanation.
Read more about xml coversions at: [
http://msdn.microsoft.com/en-us/library/ms190965.aspx][1] [1]:
http://msdn.microsoft.com/en-us/library/ms190965.aspx
Dumb question but why wont the original XML source stay around? Are there too many to zip, encrypt and store on a DVD somewhere? May give you flexibility in the future ... Just a thought...
Sometimes a single 'live' document is over-written daily. Of course this can be archived. I'm just going with the worse-case-scenerio.
There are also problems when running queries about different XML formats - I've had to write queries that ripped out the XML encoding information before querying the string, which was a pain.
Sorry - not coherent. Brain melting.
Depends on the shredder. Try one of these:
http://www.ssiworld.com/watch/
From what I know about xml shredding with plain T-SQL, I find it easy yet powerful, extremely well implemented, and making perfect sense. It just takes some time to get used to it. Essentially, xml is just like a somewhat denormalized table, which one can shred into many properly related sets.
