question

Jeff Oresik avatar image
Jeff Oresik asked

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.
xml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sometimes a single 'live' document is over-written daily. Of course this can be archived. I'm just going with the worse-case-scenerio.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
3 Likes 3 ·
+2 for answer, -1 for incoherent answer ;)
0 Likes 0 ·
Is shredding an xml data type difficult?
0 Likes 0 ·
Depends on the shredder. Try one of these: http://www.ssiworld.com/watch/
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.