Fadıl

Recently we faced a situation where the server hosting our Analysis Services ran out of the space on the primary C drive. After looking at the files stored on C drive, we noticed that the Data folder of Analysis service %Installation folder%\OLAP\Data was occupying max disk space. This is because by default when you deploy the Analysis Service Project, the cube data (dimensions and Facts data) is stored in the above default directory.

We as database developers, many times have to export data into csv files and send them across to the Business users. The data the needs to be exported can be a retrieved by executing an adhoc-query or a stored procedure based on the users requirements. In this article we will look at a SQL CLR Stored procedure which can be used to export data into CSV from within the Database

Many times when we normalize our database, we end up storing multi-values attributes as separate rows in table. For eg. Consider we are storing Photos and a set of tags associated with the photos.

Assume the following table structure

Photo_ID   Tag
  1	   Beach
  1	   Sand
  2	   Mountain
  2	   Waterfall
  3	   Island
  3	   White Sand
  3	   Blue waters

Now while retrieving the tags for photos we need only one record per photo with all the tags as comma separated in the output.

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