Filter table by an XML column where they have equal values - SQL Server 2017

Question

I need to filter the records of a table where the values of an XML component are equal.

TempTable

ID	noteID	columnXML
1	342342	xml1
2	235987	xml2

and the structure of the xml is the following:

<ArrayOfNoteParameterDC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <NoteParameterDC>
    <ParameterEnum>License</ParameterEnum>
    <Value>100299</Value>
    <ParameterName>License</ParameterName>
  </NoteParameterDC>
  <NoteParameterDC>
    <ParameterEnum>Vehicle</ParameterEnum>
    <Value>Unknown</Value>
    <ParameterName>Vehicle</ParameterName>
  </NoteParameterDC>
  <NoteParameterDC>
    <ParameterEnum>DestinationAccount</ParameterEnum>
    <Value>543152065</Value>
    <ParameterName>DestinationAccount</ParameterName>
  </NoteParameterDC>
  <NoteParameterDC>
    <ParameterEnum>SourceAccount</ParameterEnum>
    <Value>543152065</Value>
    <ParameterName>SourceAccount</ParameterName>
  </NoteParameterDC>
  <NoteParameterDC>
    <ParameterEnum>CustomerVehicleId</ParameterEnum>
    <Value>27104593</Value>
    <ParameterName>CustomerVehicleId</ParameterName>
  </NoteParameterDC>
</ArrayOfNoteParameterDC>

My goal is to be able to filter the records that have the same number of "value" of "DestinationAccount" and the "value" of "SourceAccount"

I don't have much knowledge with XML queries, what occurs to me is to do something like this:

select * from TempTable n 
    where n.columnXML.value('(/ArrayOfNoteParameterDC/NoteParameterDC/Value/text())[1]', 'varchar(max)') = ...

I don't know how to differentiate the nodes "SourceAccount" and "DestinationAccount".

I appreciate any help

Answer 1

If I understood correctly, what do you need:

select * from TempTable
where columnXML.value('(/ArrayOfNoteParameterDC/NoteParameterDC[ParameterEnum = ''DestinationAccount'']/Value/text())[1]', 'varchar(max)') =
      columnXML.value('(/ArrayOfNoteParameterDC/NoteParameterDC[ParameterEnum = ''SourceAccount'']/Value/text())[1]', 'varchar(max)')