Text to XML data type for existing table

Question

Post reply

Text to XML data type for existing table

simon_s

Ten Centuries

Points: 1354
More actions
November 15, 2013 at 5:10 am

#301274

Hi
I have a table of 2 million rows + that has been around some while (not quite dawn of time but somewhere around then) . The problem I have with it is it stores XML in a text datatype , so I try the following
simple alter column (two hours )
create clone with xml data type, insert from original, drop original , rename clone (1 hour)
Neither of which will be acceptable for the window I have to achieve this
Any other approaches/words of wisdom ?
thanks simon

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Hi

Is there a clustered index on the table?

Are there any constraints?

You'd better post the table definition with all the constraints...

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

simon_s Ten Centuries Points: 1354 More actions · Answer 2

sure, 1.6 mil rows in

CREATE TABLE [dbo].[MyTable](

[MyID] [int] NOT NULL,

[MyCode] [varchar](15) NOT NULL,

[MyNumber] [decimal](2, 0) NOT NULL,

[MyDate] [datetime] NOT NULL,

[My_XML] [text] NOT NULL,

[MyGuid] [uniqueidentifier] NOT NULL,

CONSTRAINT [PKey_MyTable] PRIMARY KEY NONCLUSTERED

(

[MyID] ASC,

[MyNumber] ASC

)

My_XML needs to be come an xml datatyppe, no FKs or DFs etc exist

LutzM SSC Guru Points: 107049 More actions · Answer 3

how did you test the two options?

Using Management Studio and the table design wizard or a query window and the T-SQL command?

If the former (design wizard) then you should lok at the script generated: it's basically creating a a new table with the new data type, then copies all data over to this table, delete the old one and renames the new one.

One option might be to add the additional column together with an INSTEAD OF INSERT / UPDATE trigger that would insert/update both columns.

Then run a sproc that would update the column in batches (e.g. 1000 at at time).

Finally, get a lock on the table, disable the trigger and rename/delete the column.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

simon_s Ten Centuries Points: 1354 More actions · Answer 4

LutzM (11/15/2013)
how did you test the two options?
Using Management Studio and the table design wizard or a query window and the T-SQL command?
If the former (design wizard) then you should lok at the script generated: it's basically creating a a new table with the new data type, then copies all data over to this table, delete the old one and renames the new one.
One option might be to add the additional column together with an INSTEAD OF INSERT / UPDATE trigger that would insert/update both columns.
Then run a sproc that would update the column in batches (e.g. 1000 at at time).
Finally, get a lock on the table, disable the trigger and rename/delete the column.

tested them using T-SQL ... I'll have a look at that otipion re the additional column thank you

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

1) If you can rebuild your primary non-clustered key to a clustered key, then it'll be better.

If you decide this, then you can do that with ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF for the clustered index. This will speed up the updates, selects and etc. But the locking sub-system work is 'violated' in a way. You can do this on your clone table, as you tried once...

Next time you can rebuild the index with ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCKS = ON.

2) If the recovery model is FULL, than changing it to SIMPLE or BULK_LOGGED will bring speed too.

If the above does not show improvement, then you can make another work around. That is to export the data into multiple files, let say 10, and than use 10 parallel process for importing to your clone table. You can take a look on this link:

http://blogs.msdn.com/b/sqlcat/archive/2006/05/19/602142.aspx and see how it's possible to load 1TB in relatively short time.

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

simon_s Ten Centuries Points: 1354 More actions · Answer 6

the main bulk of the time is the insert into the 'clone' table from the source table ..that takes 45 mins..recreating the keys etc only takes a couple of minutes

Is it validating each row of XML as valid before rit inserts it into the XML data type I wonder

I tried bcp , but exporting the data is fine whereas importing it errors 🙁

exec xp_cmdshell 'bcp "mydatabase..mytable" out "e:\bcp\mytable.txt" -n -S"myserver" -U"sa" -P"1234"'

exec xp_cmdshell 'bcp "mydatabase..tmp_mytable" in "e:\bcp\mytable.txt" -n -S"myserver" -U"sa" -P"1234"'

results in

NULL

Starting copy...

SQLState = 22001, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

SQLState = 22001, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

SQLState = 22001, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

SQLState = S1000, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file

SQLState = 37000, NativeError = 9420

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]XML parsing: line 1, character 2198, illegal xml character

NULL

BCP copy in failed

NULL

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 7

It could be the text-xml is not a valid xml. Try to copy your text XML over to a temp table (column defined as text) and attempt to cast it to xml. If it succeeds than it's not the problem.

It could be the line terminators. Windows has CRLF( (slash)r (slash)n) and UNIX has "(slash)n".

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com