Sql Server - XML conditional checks

 

Scenario:

Get details of Roles for an user stored in XML and based on condition

Solution:

1. Query:

DECLARE @users TABLE
  (
     UserID    INT,
     [Name]    VARCHAR(100),
     [Role]    VARCHAR(100),
     [Company] VARCHAR(100)
  )

INSERT INTO @users
SELECT u.UserID,
       u.[Name],
       A.B.value('(config/@Role)[1]', 'VARCHAR(100)')    AS [Role],
       A.B.value('(config/@Company)[1]', 'VARCHAR(100)') AS Company
FROM   [User] u
       CROSS APPLY u.details.nodes('/details/detail/department') AS A(B)
WHERE  u.active = 1
       AND A.B.value('(@type)[1]', 'VARCHAR(100)') = 'Internal'
       AND A.B.value('(details/detail/@InOffice)[1]', 'VARCHAR(100)') IS NULL