I have this Sql table setup in Sql Server that contains a XML field and I need to count the number of times a xml node appears in the xml. In practice, it could be the table contains user data and the xml contains a list of cities the user has visited and we need to find out how many cities the user has visited.
June 24, 2015
July 25, 2014
Select Top 1 or Top n basically returns the first n rows of data based on the sql query. Select Top 1 1 or Top n s will return the first n rows with data s depending on the sql query.
April 29, 2014
Few days back I ran into a issue where my website failed with the message that
Initially I thought the database was out space but then it sounded strange as I was not expecting the database to run out of space. After checking the database space I noticed that it only had a few MBs of data remaining for the primary while the log file had a huge data capacity. To fix the issue, update the max size of the database so it can save the data. Also, make sure the server has sufficient space to cater for the increased space.
However, sooner or later you are going to run out of space and therefore it's a good idea to increase the max size to unlimited but you need to make sure the server can cater for it.
April 14, 2014
I have a Sql table with a XML column and I want to do a count of rows of data grouped by an attribute value of the XML column. We might need these kind of queries when the XML attribute refer to a type of data. For example, I might have country code as AU and US that refers to two different countries where the data came from. Ok, so, how can I do that?
First, we have a sql table with an XML column to hold the data. I will use the same table as in my previous post on how to query csv from XML column. Here is a copy of the example data that I have.
To query this table, I will perform the usual sql select operation on the column and get the attribute values and then perform sql count operation from an outer sql query. Here's the script on how I did it.
April 11, 2014
Here is a situation where I will need to select and compare values from a table in sql server which stores data in a comma separated list. Well selecting data is not a problem but we want to run a query and compare the values within the list. In my scenario, the list contains numbers only but the solution will work for both numbers and strings. One solution will be to compare using LIKE and then check if the number exist but this won't necessarily be correct as ,2, and ,22, and 12, might all return the same result and the list does not contain commas at the start or at the end.
April 7, 2014
I have this scenario where I need to insert xml data to a xml column in Sql Server that already has an empty string in it. The xml column I have allows null and I need to update both null and and empty xml to a default value. When I started writing the script finding the null value was really easy - I could write something like this.
But finding the xml column with empty string was difficult. First of all, I could not write the following
as it returned error
Msg 402, Level 16, State 1, Line 2
The data types xml and varchar are incompatible in the equal to operator.
So, to solve the issue, I used the inbuilt function DataLength() on the xml column and compared if the value is 5 or not. Data length for empty string is 5. Based on this comparison, I updated the xml column.
March 18, 2014
I recently created a sql server database which was huge and so I wanted to find out individual sizes of the tables. So, with the help of few suggestions on the internet I ended up writing a script that displays the size of individual tables in KB, MB and the number of rows those tables have.
Here’s the script that I wrote.
And here is a screen shot of the results produced when run on Adventure Works database.
March 3, 2014
I am trying to drop a database but is getting the following error – “Cannot drop database as it is currently in use”. This really means that there are open connections to the database that needs to be closed first. To do so, we can use the SP_WHO and KILL commands.
SP_WHO will provide a list of connections to the database servers. Check for the spid and the dbname and note the corresponding spid for the database you are trying to drop.
Once we got the spids which will be numbers, run the command KILL spid. For example, if you receive 51 and 52 as the spids. Then run
KILL 51; KILL 52;
Then, run the command to drop the database and it should work.
January 10, 2014
sSql Server has the option to store XML data. This is great as it means we can store xml data and also run sql scripts on it. Yes, we can run sql scripts to retrieve certain fields within the xml. Ti start with, let's use the Customer table that I created in previous post and add a new field Person that is of type xml.