eduardasm
3/21/2007 11:57:00 AM
Hello,
I have a problem with XML schema update for one XML column (problem
exists in both SP1 and SP2 for SQL Server 2005).
1. I have a table that looks like this:
CREATE TABLE [dbo].[tProduct](
[ProductId] [uniqueidentifier] NOT NULL DEFAULT (newid()),
[Caption] [nvarchar](500) NOT NULL,
[XMLName] [xml] NULL,
[XMLBlob] [xml](CONTENT [dbo].[ProductInputXml]) NULL,
[SyncDate] [timestamp] NULL,
CONSTRAINT [PK_tProduct] PRIMARY KEY CLUSTERED
(
[ProductId] ASC
))
2. I have to update the xml schema for XMLBlob. The datalength inside
XMLBlob is bigger than 8060, that is why it is stored as follows::
EXEC sp_tableoption N'dbo.tProduct', 'large value types out of row',
1;
3. I create a backup table, move XML to the backup table, alter the
column data type to regular xml, drop schema and create a new one.
Then I bind the column to the xml schema collection. (Here I do not
care about XMLName column)
CREATE TABLE dbo.tProductBackup(
[ProductId] [uniqueidentifier] NOT NULL,
[XMLBlob] [xml] NULL
)
GO
EXEC sp_tableoption N'dbo.tProductBackup', 'large value types out of
row', 1;
GO
INSERT INTO dbo.tProductBackup(ProductId, XMLBlob)
SELECT ProductId, XMLBlob
FROM dbo.tProduct;
GO
UPDATE dbo.tProduct
SET XMLBlob = NULL, XMLName = NULL;
GO
ALTER TABLE dbo.tProduct
ALTER COLUMN XMLBlob XML
GO
IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s
WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' +
quotename(c.name)) = N'[dbo].[ProductInputXml]')
DROP XML SCHEMA COLLECTION [dbo].[ProductInputXml]
GO
CREATE XML SCHEMA COLLECTION [dbo].[ProductInputXml]
AS '.... xsd ....'
GO
ALTER TABLE dbo.tProduct
ALTER COLUMN XMLBlob XML(dbo.ProductInputXml)
GO
EXEC sp_tableoption N'dbo.tProduct', 'large value types out of row',
1;
GO
4. When I try to update the original table I get the error message:
UPDATE dbo.tProduct
SET XMLBlob = b.XMLBlob
FROM dbo.tProduct, dbo.tProductBackup b
WHERE dbo.tProduct.ProductId = b.ProductId
GO
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8064 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
Can anybody see what is wrong here?
/Ed