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 *’

 
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.

 
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:

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

How To Create A Date Table

Facebooktwitterredditpinterestlinkedinmail

Many times when developing SQL Server databases you have the requirement to show all dates in a range. One way to do this is with a date table. One of the most common reasons that I use a date table is when I have a reconciliation report. In a reconciliation report I want to show all of the days in a date range, even if they don’t have data on those days. SQL Server doesn’t have a function to generate dates in a range. So to accomplish this, I create a date table using the following script.

 

SQL Server Quarter From Date

Facebooktwitterredditpinterestlinkedinmail

One question that we get a lot is how to get the quarter from a date.  SQL Server has a very easy way to get the quarter from a date (since SQL 2005) using the DATEPART command.

 
To get the quarter from the current date

 
To get the quarter from a different date

 

SQL Server Truncate Date

Facebooktwitterredditpinterestlinkedinmail

A common function that people need to do when dealing with datetimes is extracting the date from the datetime.  Oracle has a built-in function to do this called TRUNC.  SQL Server does not have this (yet).  However… you can accomplish this very easily.  Here are a few ways to perform a truncate date function in SQL Server.