Transactional replication in SQL Server 2005\2008 can handle the XML datatype just fine with few exceptions - one in particular being when the XML value is blank. I'll save the argument about whether or not a blank (or empty string if you prefer) value is well formed XML for another day because the point is that SQL Server allows it. Consider the following table:
CREATE TABLE [dbo].[XMLReplTest]
(
[XMLReplTestID] [int] IDENTITY(1, 1) NOT FOR REPLICATION
NOT NULL ,
[SomeXML] [xml] NOT NULL ,
CONSTRAINT [PK_XMLReplTest] PRIMARY KEY CLUSTERED
( [XMLReplTestID] ASC ) ON [PRIMARY]
)
ON [PRIMARY]
GO
Execute the following statement and you'll see that SQL Server handles it just fine:
INSERT INTO dbo.XMLReplTest
( SomeXML )
VALUES ( '' )
Now let's add this table to a transactional replication publication:
-- Adding the transactional publication
EXEC sp_addpublication @publication = N'XML Replication Test',
@description = N'Sample publication to demonstrate blank XML gotcha',
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'false',
@enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@allow_subscription_copy = N'false', @add_to_active_directory = N'false',
@repl_freq = N'continuous', @status = N'active',
@independent_agent = N'true', @immediate_sync = N'false',
@allow_sync_tran = N'false', @autogen_sync_procs = N'false',
@allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1,
@allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'
GO
EXEC sp_addpublication_snapshot @publication = N'XML Replication Test',
@frequency_type = 1, @frequency_interval = 0,
@frequency_relative_interval = 0, @frequency_recurrence_factor = 0,
@frequency_subday = 0, @frequency_subday_interval = 0,
@active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@active_start_date = 0, @active_end_date = 0, @job_login = NULL,
@job_password = NULL, @publisher_security_mode = 1
GO-- Adding the transactional articles
EXEC sp_addarticle @publication = N'XML Replication Test',
@article = N'XMLReplTest', @source_owner = N'dbo',
@source_object = N'XMLReplTest', @type = N'logbased', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x00000000080350DF,
@identityrangemanagementoption = N'manual',
@destination_table = N'XMLReplTest', @destination_owner = N'dbo',
@status = 8, @vertical_partition = N'false',
@ins_cmd = N'CALL [dbo].[sp_MSins_dboXMLReplTest]',
@del_cmd = N'CALL [dbo].[sp_MSdel_dboXMLReplTest]',
@upd_cmd = N'SCALL [dbo].[sp_MSupd_dboXMLReplTest]'
GO
Assume we've created the publication, added a subscriber, taken & applied the snapshot, and we're ready to start changing data. Let's throw a monkey wrench into the works by executing the insert statement with the blank XML again and watch what happens to the log reader agent:
That's not a very nice error (or resolution)! I've been able to reproduce this behavior in SQL 2005 & 2008 but I have not tried it in 2008 R2. I've entered a Connect bug report so hopefully this is fixed in a forthcoming cumulative update. In the meantime there is a simple workaround - add a check constraint. Since we're working with the XML datatype the only option for checking length with a scalar function is DATALENGTH. The DATALENGTH for a blank xml value is 5 so we want to check that any inserted or updated value is greater than 5:
ALTER TABLE dbo.XMLReplTest ADD CONSTRAINT
CK_XMLReplTest_SomeXML CHECK (DATALENGTH(SomeXML) > 5)
GO
If you are affected by this behavior please consider taking a moment to go vote for it on Connect.