SQL Server 2008 XML Query method - retrieve text() value where another text() value exists?

Question

Please consider the following:

DECLARE @xml  XML
SET @xml =
'<Capture>
<Data><DataType>Card Number</DataType><Value>1234567898765</Value></Data>
<Data><DataType>Expiry Date</DataType><Value>1010</Value></Data>
</Capture>'

SELECT @xml.query('//*[text()="Expiry Date"]/text()')

Returns:

Expiry Date

Rather than retrieving the <DataType/> text node how can I retrieve the <Value/> text node where the text node value of <DataType/> is "Expiry Date"?

Answer 1

Try this:

SELECT 
  @xml.value('(//Data[DataType="Expiry Date"]/Value/text())[1]', 'varchar(50)')

You select any <Data> node which has the <DataType>Expiry Date</DataType>, and for that node, you select it's <Value> inner text.