One example on how to select values from XML in MS SQL:
declare @myXml xml = 
'
<params>
    <param>
        <value>
            <array>
                <data>
                    <value>
                        <string>NameOfValueOne</string>
                    </value>
                    <value>
                        <string>ValueOne</string>
                    </value>
                </data>
            </array>
        </value>
    </param>
    <param>
        <value>
            <array>
                <data>
                    <value>
                        <string>NameOfValueTwo</string>
                    </value>
                    <value>
                        <string>ValueTwo</string>
                    </value>
                </data>
            </array>
        </value>
    </param>
</params>
'

select 
	T.N.value('.', 'varchar(max)') Value
from @myXml.nodes('/params/param/value/array/data/value') T(N)
One more example:
declare @myXml xml = 
'
    <param>
        <value Name="TestNameValue1" Type="TestType1" Value="TestValue1"></value>
        <value Name="TestNameValue2" Type="TestType1" Value="TestValue2"></value>
    </param>
    <param>
        <value Name="TestNameValue1" Type="TestType2" Value="TestValue3"></value>
        <value Name="TestNameValue2" Type="TestType2" Value="TestValue4"></value>
    </param>
'

select 
	T.N.value('(value[@Name="TestNameValue1"]/@Value)[1]', 'varchar(max)') TestNameValue1
	, T.N.value('(value[@Name="TestNameValue2"]/@Value)[1]', 'varchar(max)') TestNameValue2
from @myXml.nodes('/param') T(N)

Another example with OpenXML:

declare @myXml xml =
'
    <params>
		<param>
			<value Name="TestNameValue1" Type="TestType1" Value="TestValue1"></value>
			<value Name="TestNameValue2" Type="TestType1" Value="TestValue2"></value>
		</param>
		<param>
			<value Name="TestNameValue1" Type="TestType2" Value="TestValue3"></value>
			<value Name="TestNameValue2" Type="TestType2" Value="TestValue4"></value>
		</param>
    </params>
'
 
declare @myXmlDoc int
exec sp_xml_preparedocument @myXmlDoc out, @myXml, '<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'

select TestNameValue1, 
       TestNameValue2
from openxml(@myXmlDoc, '/params/param', 2) 
  with (
         TestNameValue1  varchar(max) '(value[@Name="TestNameValue1"]/@Value)[1]'
		, TestNameValue2 varchar(max) '(value[@Name="TestNameValue2"]/@Value)[1]'
       )

exec sp_xml_removedocument @myXmlDoc

Here have I found:

As far as performance differences between OPENXML and the XML column type with the nodes() function, there doesn't seem to be a crystal clear answer. If you scan through the SQL Server XML posts on the internet you will find some threads where OPENXML is faster than the XML column's nodes() function and vice-versa. As always the best approach may vary depending on your individual circumstances so don't blindly choose the XML column type and the nodes() function over OPENXML.