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!

Advertisements