SQL Puzzle | Get latest record from XML

In this puzzle you have to read XML using T-SQL and get the latest record from XML. The criteria for the latest record is the date column.

Please check the sample input and the expected output.

Sample Input

<Items>
  <Item>
    <Id>1</Id>
    <Name>Pawan</Name>
    <Date>2018/01/01</Date>
  </Item>
  <Item>
    <Id>2</Id>
    <Name>Avtaar</Name>
    <Date>2018/01/11</Date>
  </Item>
  <Item>
    <Id>3</Id>
    <Name>Kishan</Name>
    <Date>2018/01/20</Date>
  </Item>
</Items>

Expected Output

Use below script to create table and insert sample data into it.

--

DECLARE @XML AS XML = '
<Items>
    <Item>        
        <Id>1</Id>
        <Name>Pawan</Name>        
        <Date>2018/01/01</Date>
    </Item>
    <Item>        
        <Id>2</Id>
        <Name>Avtaar</Name>        
        <Date>2018/01/11</Date>
    </Item>
    <Item>        
        <Id>3</Id>
        <Name>Kishan</Name>        
        <Date>2018/01/20</Date>
    </Item>
</Items>'

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--


DECLARE @XML AS XML = '
<Items>
    <Item>        
        <Id>1</Id>
        <Name>Pawan</Name>        
        <Date>2018/01/01</Date>
    </Item>
    <Item>        
        <Id>2</Id>
        <Name>Avtaar</Name>        
        <Date>2018/01/11</Date>
    </Item>
    <Item>        
        <Id>3</Id>
        <Name>Kishan</Name>        
        <Date>2018/01/20</Date>
    </Item>
</Items>'

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT p.value(N'(Id/text())[1]',N'nvarchar(10)') AS [Id]
          ,p.value(N'(Name/text())[1]',N'nvarchar(20)') AS [Name]
          ,p.value(N'(Date/text())[1]',N'nvarchar(20)') AS [Dt]          
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Items/Item') AS u(p)
)
SELECT TOP 1 * 
FROM CTE
ORDER BY Dt DESC

--

Output-1

--                            


Id         Name                 Dt
---------- -------------------- --------------------
3          Kishan               2018/01/20

(1 row affected)


--

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com