[SOLVED] How to query a specific tag from XML field data in sql?

How to query a specific tag from XML field data in sql?

I have an xml field with the name Payload. It has data like this:

<FL>
    <Head>
      <TP>Nine11</TP>
      <RB>Test</RB>
    </Head>
<FL>

now I want to query the RB from Head where it's value is equal to e.g. 'Test.

I did this but beyond this I cannot figure out.

Select  rlogs.PayLoadfrom rlogs

it displays and I tried casting even.

Select CAST(rlogs.PayLoad as text) from RecordLogs rlogs

Solution

You can use XPath to query within your XML. If I understand your question correctly, you could (for instance) query TP or the entire XML from your RecordLogs table, and filter on the TP column like this:

Select rlogs.PayLoadFrom.value('(/FL/Head/TP)[1]', 'varchar(50)') TP, 
     CAST(rlogs.PayLoadFrom AS text) FL
FROM RecordLogs rlogs
WHERE rlogs.PayLoadFrom.value('(/FL/Head/RB)[1]', 'varchar(50)') = 'Test'