Extract value from XML column and use it inside WHERE clause

Question

I have a below xml type data in both oracle and MSSQL database.

<row id="TT1308143CNF" xml:space="preserve">
  <c1>10</c1>
  <c2>1001</c2>
  <c3>DEBIT</c3>
  <c4>USD</c4>
  <c6>USD1000110010001</c6>
  <c7>60000.00</c7>
</row>

The column type is of XMLTYPE and i would like to do the selection in the XML based on the extract Value.

SELECT xmlrecord
FROM xxxx
WHERE extractvalue(xmlrecord,'/row/c4') = 'USD';

The above select query is working fine in Oracle database, could you please help us to achieve the same in MSSQL database.

Answer 1

You can use the value() function like so:

SELECT *
FROM @t
WHERE xmlrecord.value('(/row/c4)[1]', 'VARCHAR(100)') = 'USD'

It assumes that xmlrecord is a column of type xml (or you can CAST it).