Database Programming: SET Options That Impact XML Access In SQL Server 2005

The information in this post comes from Books OnLine (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6bb3bfaa-0ddd-423a-ac0d-e9bafcc1ad76.htm) and this online update.

There are a number of SET options that impact access to data contained in the XML datatype, as well as the ability to construct and modify primary and secondary XML indexes. Here's the list:

  • ANSI_NULLS: ON
  • ANSI_PADDING: ON
  • ANSI_WARNINGS: ON
  • ARITHABORT: ON
  • CONCAT_NULL_YIELDS_ABORT: ON
  • NUMERIC_ROUNDABORT: OFF
  • QUOTED_IDENTIFIER: ON

If these options are not set as shown, you'll get runtime errors when you attempt to access or modify XML data.

Thanks to Remus Rusanu, who provided the MSDN link, which was the first I'd heard that these limitations extended to XML access as well as index creation.

     -wp