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