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
On
While waiting for your reply, here is an answer based on some assumptions.
By the way, your XML is not well-formed. I had to fix it.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, payload XML);
INSERT INTO @tbl (payload)
VALUES
(N'<FL>
<Head>
<TP>Nine11</TP>
<RB>Test</RB>
</Head>
</FL>');
-- DDL and sample data population, end
DECLARE @rb VARCHAR(20) = 'Test';
SELECT c.value('(TP/text())[1]', 'VARCHAR(30)') AS tp
, c.value('(RB/text())[1]', 'VARCHAR(30)') AS rb
FROM @tbl AS tbl
CROSS APPLY tbl.payload.nodes('/FL/Head[RB=sql:variable("@rb")]') AS t(c);
Output
+--------+------+
| tp | rb |
+--------+------+
| Nine11 | Test |
+--------+------+
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: