Database

In one of my earlier post Creating a Delimited list from a column of a table, we had seen how to generate CSV (demilited string) from column values in a table using XML PATH and COALESCE method. These methods though solved our problem, but are not generic.

In this post we will look at how to generalize the solution by using SQLCLR aggregates.

Before we look at the actual CLR aggregate let’s look at what a SQLCLR aggregate comprises of. A SQLCLR Aggregate is defined as a STRUCTURE in .NET. It consists of 4 methods

To support multi-value params, many times we use XML data type as input param.

There are multiple ways to parse XML data in SQL Server. Lets have a look at them

We will use the following XML data

<ShoppingCart>
<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="99" Price="25.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="32" Price="12.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="11" Price="90.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="7" Price="50.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="8" Price="67.35" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="45" Price="29.99" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="54" Price="49.49" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
</ShoppingCart>

OPENXML
To parse the XML using OPENXML we use the following code

In this script post, we will look at different ways to get the list of user defined tables in a particular database, along with their rowcount and size information.

Method 1: Using count(*)

In this method we will get the rowcount for each table using the sp_msforeachtable in combination with count(*) function. Note using this method we will only get the rowcount for each table.

IF OBJECT_ID('tempdb..#tableRowCount') IS NOT NULL
DROP TABLE #tableRowCount
CREATE TABLE #tableRowCount
(
name       VARCHAR(512),
ROWS       INT
)
insert into #tableRowCount
exec sp_msforeachtable 'select ''?'' ,COUNT(*) FROM ?'
select * from #tableRowCount

Method 2: Using sp_spaceused

Here, we will get the rowcount for each table using the sp_msforeachtable in combination with sp_spaceused stored procedure. Using this method we will get both the rowcount as well as spaceused in KB for all the tables.

In this script we will look at various ways we can design a string split function in SQL Server to split a delimited string in to individual rows.

We will use the following csv delimited string for building our split function.

DECLARE @csv_str VARCHAR(8000)
SET @csv_str = 'cat,dog,rat,ant,tiger'

Method 1 : USING charindex function

In this method we will use the system string function charindex. We loop through the string looking for the delimiter and parse the string accordingly.

The Cloud-based content storage ecosystem

Cloud-based content storages have quickly spread out as an obvious benefit for applications to make data available across platforms. They leverage tool capabilities beyond basic file storages with ad-hoc file viewers, flourishing edition features up to full online suites. The evolution of such file repositories makes them henceforward essential in any productivity toolsets. Online content repositories are not a productivity objective but a means to enable productivity. Many major solutions and handy little apps quickly fall into your productivity hall of fame. As a downside, however, you face proprietary content repositories preventing straight data sharing.