Requires A Singleton?

Facebooktwitterredditpinterestlinkedinmail

Have you ever got this error message when you tried to parse an XML string in SQL Server?

XQuery [value()]: ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *’

DECLARE	@XMLToParse  XML
 
-- Load the XML data in to a variable to work with.
-- This would typically be passed as a parameter to a stored proc
SET		@XMLToParse =	'
								Baboon
						 '

SELECT	
@XMLToParse.value('data(/Animal/AnimalName)', 'VARCHAR(MAX)') AS AnimalName

 
How do we fix this?

This error is saying that the value() function needs an xpath that only exits once (non-repeating node). You and I know that there will only be one instance of , but SQL Server does not know that. To solve this, we just need to tell SQL Server that it should use the first instance of this AnimalName node that it finds. We do this by added the [1] tag to the xpath. We want to add it after the closing parentheses. This will tell SQL Server to grab whatever is the first node with that name.

DECLARE	@XMLToParse  XML
 
-- Load the XML data in to a variable to work with.
-- This would typically be passed as a parameter to a stored proc
SET		@XMLToParse =	'
								Baboon
						 '

SELECT	
@XMLToParse.value('data(/Animal/AnimalName)[1]', 'VARCHAR(MAX)') AS AnimalName
-- Notice the [1] on the line above!

 
Extra Fun

If there are multiple nodes with the same name in your XML, you can adjust the number inside the square braces to match whatever node you want. For example:


	Baboon
	Cat
	Dog 

[1] would return Baboon
[2] would return Cat
[3] would return Dog

Order By On A Union?

Facebooktwitterredditpinterestlinkedinmail

Have you ever tried to add an ORDER BY clause to a UNION operator and have gotten this error message?

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘UNION’.

SELECT	AnimalName
FROM	Animal
ORDER	BY AnimalName

UNION

SELECT	AnimalName
FROM	Animal
ORDER	BY AnimalName

So what the heck is this and how do we fix it?

 
What The Error Says
The error basically says that you have an issue with your syntax. The ORDER BY clause should only be applied one time in your query. Logically SQL Server will pull the data requested from the two tables… then it will perform the sort operation on the data.

When people see the UNION operator, they think that it is combining two different result sets, thus they need two ORDER BY clauses. In fact, you only need to order the final result set. This is done by placing the ORDER BY command at the very end of the statement.

SELECT AnimalName
FROM Animal
-- Notice the removed ORDER BY here?

UNION

SELECT AnimalName
FROM Animal
ORDER BY AnimalName

 

Cannot Insert Explicit Value For Identity Column

Facebooktwitterredditpinterestlinkedinmail

If you try to insert in to a table with an ID column, you could get this error “Cannot insert explicit value for identity column in table ‘<<table>>’ when IDENTITY_INSERT is set to OFF.”  This is not the end of the world and is actually pretty easy to get around.

 
Why Are You Getting This Error?

The reason you’re getting this error is because you are trying to tell SQL Server what the ID value for a row in the table should be.  If you have an identity column on your table, SQL Server will want to generate that ID value for you automatically.  The error is essentially saying, “Hey, I’m supposed to generate the ID for you… you aren’t supposed to tell me what it is!”

 
How Can You Fix This?

There are two easy ways to get around this.

 
If You Want To Tell SQL Server What The ID Value Should Be

If want to specify what the ID values should be for the records you are inserting, then before you execute the INSERT statement, you need to run a small sql command to turn identity inserts on.  Identity inserts allow you to populate the value of an identity column with a specific value.

SET		IDENTITY_INSERT Animal ON

INSERT
INTO	Animal
		(AnimalID, AnimalName)
VALUES	(1,        'Horse'),
		(2,        'Pig'),
		(3,        'Cow')

SET		IDENTITY_INSERT Animal OFF

NOTE: You can only have identity insert on for one table at a time.  To insert in to a second table, you will need to turn the identity insert off on the first table before inserting in to the second table.

 
If You Want To Allow SQL Server To Generate The ID

If you don’t care about what the ID values are for these records, you should just allow SQL Server to generate the ID values for you.  To do this, just specify the columns that are in the table and leave off the identity column.

INSERT
INTO	Animal
		(AnimalName)
VALUES	('Horse'),
		('Pig'),
		('Cow')

 
Reference: http://msdn.microsoft.com/en-us/library/ms188059.aspx