CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vanapandi Narayanan
Vanapandi Narayanan

Reputation: 109

SQL Server XML with where clause and select

ID  Status  LOAD
1   Active  <root><Loan>1001</Loan><Atty>False</Atty></root>
2   Failed  <root><Loan>1021</Loan><Atty>True</Atty></root>
3   Failed  <root><Loan>1004</Loan><Atty>True</Atty></root>
4   Active  <root><Loan>1034</Loan><Atty>True</Atty></root>

Here , I want to get the status ,ID and LOAD where loan number =1004. How do we can create sql query ? Load column is XML type .

Upvotes: 1

Views: 51

Answers (1)

marc_s
marc_s

Reputation: 754268

How about

SELECT
    ID, [Status], [Load]
FROM
    dbo.YourTableNameHEre
WHERE
    Load.value('(/root/Loan)[1]', 'int') = 1004

Upvotes: 3

Related Questions