SQL query XML

             

Scenario: Retrieve the User(s) & their latest Addresses (address details stored as XML)

Solution:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SELECT DISTINCT Address.Details.value('@sequence', 'VARCHAR(max)') AS [Address ID],
       Detail
FROM   [User]
       CROSS APPLY [details].nodes('//*[@city="Mumbai"]/details') AS Address(Details)
       JOIN (SELECT a.AddressID,
                    Detail
             FROM   Address a
                    JOIN (SELECT DISTINCT AddressID,
                                          (SELECT MAX([ver])
                                           FROM   Address b
                                           WHERE  c.AddressID = b.AddressID
                                           GROUP  BY AddressID
                                           HAVING c.[ver] <= MAX(b.[ver])) AS [ver]
                          FROM   Admin.dbo.Address c) d
                      ON d.AddressID = a.AddressID
                         AND d.[Version] = a.[ver]) e
         ON e.AddressID = Address.Details.value('@sequence', 'VARCHAR(max)')