SQL Server read XML attribute

Suppose you have a XML value stored in a table

<?xml version="1.0" encoding="utf-8"?>
<rootElement>
  <param name="p1" value="v1" />
  <param name="p2" value="v2" />
  ...
</rootElement>

 

We can get the value of the p1 parameter as follows

SELECT
  columnNAame.value('(/rootElement/param[@name="p1"]/@value)[1]', 'varchar(50)')
FROM  
  tableName

Notice that the correct datatype should be chosen within the query. If we have a integer value we have to use int as the datatype. In addition if long text is stored for the value proper data length needs to be given.

Cheers

Samitha