Reading node value using condition in SQL server

Question

I have below XML and my table name is table1

<Details >
  <RelatedDetails>   
    <Name>name 1</Name>   
    <Position>User</Position>    
    <Relationship>User</Relationship>
    <Salary>5000</Salary>
    <Type>Company</Type>
  </RelatedDetails>
  <RelatedDetails>   
    <Name>name 2</Name>   
    <Position>User</Position>    
    <Relationship>Owner</Relationship>
    <Salary>7000</Salary>
    <Type>Company</Type>
  </RelatedDetails>
  <RelatedDetails>   
    <Name>name 3</Name>   
    <Position>User</Position>    
    <Relationship>Director</Relationship>
    <Salary>2000</Salary>
    <Type>Company</Type>
  </RelatedDetails>
</Details>

I would like to get the value of 'name' where the Salary > 6000 in SQL Server.

Please suggest how this can be achieved?

Ideally i should get 'name 2' as output.

https://learn.microsoft.com/en-us/sql/t-sql/xml/query-method-xml-data-type?view=sql-server-ver15 and https://stackoverflow.com/questions/13195922/how-to-query-xml-column-in-tsql — Ben, Oct 01 '20 at 13:07
the issue is, my column is not xml column I have nvarchar column, I am trying to convert the column to xml using cast, but am not getting any result — SA., Oct 01 '20 at 13:20
While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Oct 01 '20 at 13:32
@kawade, one more question. Does your XML that is stored in the MVARCHAR() column have XML prolog declaration with specified encoding? — Yitzhak Khabinsky, Oct 01 '20 at 14:06

score 0 · Answer 1 · answered Oct 01 '20 at 19:55

This should do what you want:

DECLARE @Table1 TABLE ( xmlText NVARCHAR(MAX) );
INSERT INTO @Table1 VALUES ( '<Details><RelatedDetails><Name>name 1</Name><Position>User</Position><Relationship>User</Relationship><Salary>5000</Salary><Type>Company</Type></RelatedDetails><RelatedDetails><Name>name 2</Name><Position>User</Position><Relationship>Owner</Relationship><Salary>7000</Salary><Type>Company</Type></RelatedDetails><RelatedDetails><Name>name 3</Name><Position>User</Position><Relationship>Director</Relationship><Salary>2000</Salary><Type>Company</Type></RelatedDetails></Details>' );

DECLARE @salary INT = 6000;

SELECT
    n.f.value( '../Name[1]', 'VARCHAR(50)' ) AS [Name]
FROM @Table1 t1
CROSS APPLY ( SELECT CAST( xmlText AS XML ) AS xt ) AS x
CROSS APPLY x.xt.nodes( '//Details/RelatedDetails/Salary[.>sql:variable("@salary")]' ) n(f);

Returns

+--------+
|  Name  |
+--------+
| name 2 |
+--------+

Reading node value using condition in SQL server

1 Answers1