Reading XML data from MSSQL Table

An handy query to read data from table having XML datatype columns.

We will be reading this XML and will be retriving the value which is 342. Considering that this XML is stored in a column called ActionColumn and table called MyTable.

When we use @, we also need to tell SQL at which position in the XML tree it should look at, which in this case is first i.e [1]

<Actions>
<Action Type=”Data” ActionData=””>
<ContextItems>
<ContextItem Key=”ID” Value=”342″ />
</ContextItems>

<ContextItems>
<ContextItem Key=”ID” Value=”45″ />
</ContextItems>
</Action>
</Actions>

SELECT ActionColumn.value(‘(/Actions/Action/ContextItems/ContextItem[@Key=”ID”]/@Value)[1]’ ,’nvarchar(100)’) as ContentMsgID FROM MyTable

Lets look at another example, over here we are interested to retreive expectation value. Expection value has been repeated three times in the XML tree, in order to retreive the third value “1,49” we need to tell explicitly mention [3] in our query.

<expression tag=””>
<condition tag=”” prefix=”NONE”>
<operand group=”Site” name=”CountryID” />
<operation name=”==” />
<expectation value=”164″ />
</condition>
<condition tag=”” prefix=”AND”>
<operand group=”Site” name=”ChannelID” />
<operation name=”==” />
<expectation value=”58″ />
</condition>
<condition tag=”” prefix=”AND”>
<operand group=”KarmaOrdersHistory” name=”DBNaiscEmployeeSize” />
<operation name=”betweenOp” />
<expectation value=”1,49″ />
</condition>
</expression>

SELECT
ActionColumn.value(‘(/expression/condition/expectation/@value)[3]’ ,’nvarchar(100)’)
FROM MyTable

Hope it has helped you!

TSQL ISNULL VS COALESCE

One of the difference between the two is that ISNULL is just checking the first argument null ability. As for COALESCE is it used to return the first non-nullable value in the argument list.

Another important thing to note is that ISNULL result type is based on the first argument type. On the other hand CAOLESCE will return the result type based on the argument type.

DECLARE
@x as varchar(2) = NULL
,@y AS VARCHAR(3) = '123'

select coalesce(@x,@y)

Returns : 123

SELECT ISNULL(@x,@y)

Returns: 12

Using Pivot in SQL Server

Its a great feature for showing the data accross the rows(In a 3 dimensional way).The result provid us with a nice looking report for analysis.

select * from
(
select Membercode,left(datename(month,transactiondate),3) as mmonth,Amount from AccountTransaction
) as a
PIVOT
(SUM(amount) FOR [mmonth] IN (May,Jun,Jul))
AS pp

 

pivot

Rigid Relationship affects on SSAS Update Process Dimension

Attribute relationship play an important role on cube performance , it tell the SSAS engine how the aggregation on the cube dimension will be done.Recently i came across an issue where we have defined the attribute relation within one of our dimension as Rigid. At the time of defining this we didn’t gave a thought if the dimension data will be updated or deleted.

Later on for the sake of improving Cube performance we changed the Processing type for all the dimension to Update Process , and there we hit an error sayingRigid relationships between attributes cannot be changed during incremental processing of a dimension“.

This happens because the data has changed within our dimension and we defined our Attribute relationship as Regid, which by right should be defined as Flexible, another reason for this error was the Dimension processing type which was Update Process. In order to resolve this issue you may either change the attribute relationship to Flexible or change the Dimension Processing type to Full Process.

The point to note here is that when you define your Dimension Processing type as Update Process and the dimension attribute relationship is defined as Rigid, SSAS expects that the dimension data will remain static and the attribute relationship will not change.

Full Processing of a Cube/Dimension in SQL Analysis Services – SSAS

Following changes in your cube or dimensions can be make your Cube unprocessed and that requires you to Fully-Process your cube or dimensions.

  • New measure group
  • New measure
  • Edit measure aggregation method
  • Edit dimension usage

Here are other actions that will make your dimension unprocessed (and therefore all cubes connected to the dimension become unprocessed):

  • Add an attribute to a dimension
  • Change the order by property of an attribute
  • Edit the attribute relationship
  • Add or delete dimension translation

Please note that Fully Processing the Cube and Dimension makes in unavailable and requires down time.