RealityDysfunction RealityDysfunction - 20 days ago 7
SQL Question

SQL Server : error validating the default for a XML column

I am trying to set the default value for a new column I just added. In properties I am trying to set '

Default Value or Binding'
to a simple XML along the lines of:

<root>
<title>Welcome</title>
<body>Thank you for your time.</body>
</root>


However, when I click away, I get an error:


SQL error validating the default for column


I set this value in other numerical columns and it worked fine. What gives?

Answer

Not sure, but the visual designers in SQL Server Management Studio often don't quite work properly...

Just use a simple T-SQL statement to achieve the same thing:

ALTER TABLE dbo.YourTableName
ADD CONSTRAINT DF_YourTable_YourXmlColumn
    DEFAULT '<root><title>Welcome</title><body>Thank you for your time.</body></root>' 
    FOR YourXmlColumn

and you're done!

(You didn't specify your table and column names - so I just made up placeholders - do replace those with your actual table and column names! Also: for the default constraint, I'd always recommend specifying an explicit name - makes it easier to disable and/or drop the constraint later, if you ever need to; my default naming convention is shown - again: adapt to your needs / likes)