Posted by eduardasm on 03/21/07 11:56

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

[Back to original message]