Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

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

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

  • Comments 3
  • Likes

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

Comments
  • Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored...

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment