Select XML node that match name-CodePudding

My XML structure looks like this

From XML I need to select node HHQuoteData from KeyValuePair/Value collection

My code below doesn't return any data. What is wrong with my code?

SELECT
    k.kv.query('KeyValuePair/Value/HHQuoteData')
FROM DeclinedQuote.RequestLogXMLsToProcess aph
CROSS APPLY [PayloadXML].nodes('/server/requests/Session.setDocumentRq/session/data/policy/AdditionalData') A (ad)
CROSS APPLY a.ad.nodes('KeyValuePair') k (kv)
WHERE k.kv.exist('(/Value[contains(.,"HHQuoteData")])') = 1

Sample xml

CodePudding user response：

Yet another answer from @lptr in a comment...

with xmlnamespaces
(
'http://www.fc.com/quote/request/' as r, 
'http://www.fc.com/quote/' as q, 
'http://www.fc.com/householdaggregator/request' as hr
)
select a.d.query('.')
from @x.nodes('server/requests/Session.setDocumentRq/session/data/policy/r:AdditionalData/r:KeyValuePair/q:Value/hr:HHQuoteData') as a(d);

with xmlnamespaces('http://www.fc.com/quote/request/' as r, 'http://www.fc.com/quote/' as q)
select a.d.query('.')
from @x.nodes('server/requests/Session.setDocumentRq/session/data/policy/r:AdditionalData/r:KeyValuePair/q:Value/*:HHQuoteData') as a(d);

select a.d.query('.')
from @x.nodes('server/requests/Session.setDocumentRq/session/data/policy/*:AdditionalData/*:KeyValuePair/*:Value/*:HHQuoteData') as a(d);