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

Question

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

While asking a question, you need to provide a minimal, reproducible example. Please refer to the following link: stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT statements. (2) What you need to do, i.e. logic, and your code implementation of it. (3) Desired output based on the sample data. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Mar 3, 2020 at 14:07
SELECT PayLoad.value('(Head/RB)[1]', 'nvarchar(max)') FROM rlogs — MortimerCat
– MortimerCat, Commented Mar 3, 2020 at 14:24
learn.microsoft.com/en-us/sql/t-sql/xml/… Note that my answer ignores the <FL> -> You may need (FL/Head/RB) but add the slash to the final FL tag :) — MortimerCat
– MortimerCat, Commented Mar 3, 2020 at 14:25

tgolisch · Accepted Answer · 2020-03-03 14:35:50Z

1

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'

answered Mar 3, 2020 at 14:35

tgolisch

6,7743 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yitzhak Khabinsky · Accepted Answer · 2020-03-03 15:03:13Z

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 |
+--------+------+

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related