Here is one demo script that uses XML:
IF OBJECT_ID(‘usp_XML_Test’) IS not NULL DROP PROC usp_XML_Test;
CREATE PROCEDURE usp_XML_Test
WITH XMLNAMESPACES (DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription’)
CatalogDescription.value(‘(/ProductDescription/@ProductModelID)’, ‘int’) as ModelID,
CatalogDescription.value(‘(/ProductDescription/@ProductModelName)’, ‘varchar(30)’) as ModelName
WHERE ProductModelID = 19
Now I save it as G:\SQL\XML\usp_XML_Test.sql. After running it in SSMS, I am able to execute this stored procedure:
What if I run this script through SQLCMD. It also completes successfully, but what happens when I execute same stored procedure? I got an error message:
Of course I can add SET QUOTED_IDENTIFIER in my stored procedure, but wait a second. This is interesting – I didn’t change anything in my query what would I get this error?
The difference came from default setting between SSMS and SQLCMD. Let’s take a look at them.
First, let’s take a look at SSMS default setting. When I open a new query window, I use DBCC USEROPTIONS and here is what I see:
Quoted_Identifier is SET.
What about SQLCMD? Let’s see in SQLCMD:
Unfortunately SQLCMD doesn’t set QUOTED_IDENTIFIER by default and that causes XML queries to fail.
How can we fix it? Do we have to add SET QUOTED_IDENTIFIER in our script file? The answer is NO, we don’t have to. SQLCMD provides a parameter to set QUOTED_IDENTIFIER:
-I is the parameter that set QUOTE_IDENTIFIER and our stored procedure works perfectly after.
Furthermore, default user settings are customizable, they can be found under Tools -> Option -> Query Execution -> SQL Server -> ANSI: