x

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

Jeff Oresik gravatar image

Jeff Oresik
138 4 4 6

(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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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
more ▼

answered Dec 08, 2010 at 08:04 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

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

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x146

asked: Dec 08, 2010 at 07:24 AM

Seen: 2305 times

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