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]

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

<ContextItem Key=”ID” Value=”45″ />

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 tag=”” prefix=”AND”>
<operand group=”Site” name=”ChannelID” />
<operation name=”==” />
<expectation value=”58″ />
<condition tag=”” prefix=”AND”>
<operand group=”KarmaOrdersHistory” name=”DBNaiscEmployeeSize” />
<operation name=”betweenOp” />
<expectation value=”1,49″ />

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

Hope it has helped you!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s