Is ansi_padding 'on' officially required for working with xml columns in SQL Server?

Question

When you use SQL Server Management Studio to generate a script for creating a table, the last thing it does is set ansi_padding off.

I've run into a problem where this statement is causing an issue further down the line when working with xml.

The error message implies that the ansi_padding must be on when using the XQuery features, and googling confirms. However, I'd really like to see some official documentation stating this as a requirement, to make sure there isn't anything else we're taking for granted.

I understand the implications of this option and have since removed the statement, but I still want to know:

Is ansi_padding 'on' officially required for working with xml columns in SQL Server?

Answer 1

Yes. I looked around too and didn't find any good documentation on it but the exception messages actually specify that it is required for XML and I suppose that is about as official as it gets:

Msg 1934, Level 16, State 1, Procedure svrTrgNewDB, Line 11 CONDITIONAL failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Answer 2

Yes, it is officially required.

http://msdn.microsoft.com/en-us/library/ms188285.aspx

Quoted from above link:

When querying xml type columns or variables using xml data type methods, the following options must be set as shown.

SET Options

Required Values

ANSI_NULLS

ON

ANSI_PADDING

ON

ANSI_WARNINGS

ON

ARITHABORT

ON

CONCAT_NULL_YIELDS_NULL

ON

NUMERIC_ROUNDABORT

OFF

QUOTED_IDENTIFIER

ON

If the options are not set as shown, queries and modifications on xml data type methods will fail.