XML and QUTEO_IDENTIFIER setting in SSMS and SQLCMD

Here is one demo script that uses XML:

USE  AdventureWorks2012

GO

IF  OBJECT_ID(‘usp_XML_Test’ IS  not NULL DROP PROC usp_XML_Test;

go

CREATE PROCEDURE usp_XML_Test

AS

BEGIN

    WITH XMLNAMESPACES (DEFAULT http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription’)

    SELECT

        CatalogDescription.value(‘(/ProductDescription/@ProductModelID)[1]’, ‘int’) as ModelID,

        CatalogDescription.value(‘(/ProductDescription/@ProductModelName)[1]’, ‘varchar(30)’) as ModelName

    FROM Production.ProductModel

    WHERE ProductModelID = 19

END

GO

 

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:

Enjoy!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s