Parsing XML from returned field in SQL

You did not accept any answer yet, might be, that the existing answers did not really help...

If I get this correctly, you want to read the value (=<v>) to a given key (=<k>). Try it like this:

DECLARE @XML XML = 
  N'<p>
      <k>Attribute Name</k>
      <v>Attribute Value</v>
    </p>
    <p>
      <k>Attribute Name 2</k>
      <v>Attribute Value 2</v>
    </p>
    <p>
      <k>Attribute Name 3</k>
      <v>Attribute Value 3</v>
    </p>';

DECLARE @Search NVARCHAR(100)=N'Attribute Name 2';

SELECT @xml.value(N'(/p[k/text()=sql:variable("@Search")]/v/text())[1]',N'nvarchar(max)');

The key-name you are looking for is set to a variable @Search.

The XQuery is looking for a <p>, where there is a <k> which has got the text you are looking for.

Then the <v> below the <p> is read and returned.

Use xml.nodes('/p') as p(x) to retrieve all of the p elements, then use p.x.value(...) to retrieve the key and value.

If you are only looking to extract specific keys, use XPath to filter and retrieve the value: /p[k[1]/text() = "foo"]/v[1]/text()

You can use the XQuery syntax to query the table directly and the get value out of the xml.

Example :

-- using a table variable for the example
declare @TestTable table (id int identity(1,1), [xml] XML);
insert into @TestTable ([xml]) values (N'<p>
  <k>Attribute Name</k>
  <v>Attribute Value</v>
</p>
<p>
  <k>Attribute Name 2</k>
  <v>Attribute Value 2</v>
</p>
<p>
  <k>Attribute Name 3</k>
  <v>Attribute Value 3</v>
</p>'),
(N'<p>
  <k>Attribute Name 4</k>
  <v>Attribute Value 4</v>
</p>');

DECLARE @AttributeName nvarchar(30) = N'Attribute Name 2';

select id, [xml].query('p[k/text() = sql:variable("@AttributeName")]/v').value('.','nvarchar(max)') as [value]
from @TestTable
where [xml].query('p[k/text() = sql:variable("@AttributeName")]').value('.','nvarchar(max)') != '';

Returns :

id value 
-- -------------------
1  Attribute Value 2 

There are multiple ways to achieve this.

Filtering key with predicate on xquery expression

Use an xquery expression with a predicate to filter to <p> nodes that contain a <k> child node with the desired string literal (i.e. "Attribute Name 2") using the value() function:

SELECT m.xml.value('(/p[k/text()="Attribute Name 2"]/v/text())[1]',N'varchar(max)')
from m

See it demonstrated in this sqlfiddle.

And if there is a possibility of unicode characters in the data, prefix the xquery string with N and select the value as nvarchar(max):

SELECT m.xml.value(N'(/p[k/text()="Attribute Name 2"]/v/text())[1]',N'nvarchar(max)')
from m

Select key and value as fields and apply WHERE condition

Borrowing from the second query in this answer, CROSS APPLY can be used in a sub-query with .nodes() to select the text of the <k> and <v> nodes as fields using value().

Then in the outer-query, a WHERE condition can be added to restrict <p> nodes to only those with a <k> with text Attribute Name 2.

select [key], value
from (
    select x.value('(k/text())[1]','varchar(max)') as [key],
    x.value('(v/text())[1]','varchar(max)') as value
    FROM m
    CROSS APPLY xml.nodes('//p') as p(x)
) as Result
where [key] = 'Attribute Name 2'

See a demonstration of this in this SQL fiddle.

This could be simplified by removing the sub-query and just selecting the text from the <v> node where the text of the <k> node matches the search string:

SELECT x.value('(v/text())[1]','varchar(max)') as value
FROM m
CROSS APPLY xml.nodes('//p') as p(x)
WHERE x.value('(k/text())[1]','varchar(max)') = 'Attribute Name 2'

See this demonstrated in this SQL fiddle.

Maybe something like this:

DECLARE @xml xml
SET @xml =
'<p>
  <k>Attribute Name</k>
  <v>Attribute Value</v>
<p>
<p>
  <k>Attribute Name 2</k>
  <k>Attribute Value 2</k>
</p>
<p>
  <k>Attribute Name 3</k>
  <k>Attribute Value 3</k>
<p>
<p>... and so on ...</p>'

SELECT
      a.value('@k[1]', 'varchar(max)') 
FROM @xml as a