SQL Server 2012 came up with a lot of new enhancements, breaking changes & behavioral changes. In my earlier article, I had discussed about one of the nice behavior changes in exists function. Today, we will discuss another behavioral change in Sqlcmd Utility when XML mode is on. This utility is one of the handy utilities when you wish to execute transact SQL or any other SQL operations on command prompt.
Given below are sqlcmd utility behavioral changes in earlier version SQL Server vs 2012.
|
S.No |
Behavior in Earlier versions |
Behavior in SQL Server 2012 |
|
1 |
If any string data having a single quote will be replaced with the & apos; escape sequence, it will not remain a valid XML and an XML parser will not give the same result. |
If any string data having a single quote will not be replaced with & apos; escape sequence, it will remain a valid XML and an XML parser will give the same result. |
|
2 |
If any column of a table having money data values with no decimal value, it will be converted to integer. |
If any column of a table having money data values with no decimal value, it will show the 4 decimal digits. |
Let me create an example to demonstrate this behavior changes.
--Create table
CREATE TABLE [dbo].[tbl_Employee]
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[First Name] [nvarchar](50) NULL,
[Last Name] [nvarchar](50) NULL,
[Salary] [money] NOT NULL
)
GO
--Insert record into table
INSERT INTO dbo.tbl_Employee ([First Name],[Last Name],[Salary])
VALUES ('Reuben','D''sa',5000)
GO
--Browse table
SELECT [Last Name],[Salary] FROM dbo.tbl_Employee
--OUTPUT
Lets execute the above query in sqlcmd utility with XML mode on as shown in the given below picture.
Reference : MSDN
SQL Server Portal 