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.

more ▼

asked Dec 08, 2010 at 07:24 AM in Default

avatar image

Jeff Oresik
139 4 4 9

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Dec 08, 2010 at 07:30 AM

avatar image

26.2k 18 37 48

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.

Dec 08, 2010 at 08:17 AM Oleg

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.

Dec 08, 2010 at 11:58 PM WilliamD
(comments are locked)
10|1200 characters needed characters left

Read more about xml coversions at: http://msdn.microsoft.com/en-us/library/ms190965.aspx

more ▼

answered Dec 08, 2010 at 08:04 AM

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left

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...

more ▼

answered Dec 08, 2010 at 07:42 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

Sometimes a single 'live' document is over-written daily. Of course this can be archived. I'm just going with the worse-case-scenerio.

Dec 08, 2010 at 08:13 AM Jeff Oresik
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 08, 2010 at 07:45 AM

avatar image

ThomasRushton ♦♦
42.3k 20 57 53

  • for answer, -1 for incoherent answer ;)

Dec 08, 2010 at 08:07 AM Fatherjack ♦♦

Is shredding an xml data type difficult?

Dec 08, 2010 at 08:16 AM Jeff Oresik

Depends on the shredder. Try one of these: http://www.ssiworld.com/watch/

Dec 08, 2010 at 08:27 AM ThomasRushton ♦♦

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.

Dec 08, 2010 at 08:44 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 08, 2010 at 07:24 AM

Seen: 2804 times

Last Updated: Dec 08, 2010 at 07:26 AM

Copyright 2018 Redgate Software. Privacy Policy