Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Multiple Ways to READ XML DATA using SQL

In this puzzle you have to read XML using T-SQL

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

Id Name Date
1 Pawan 2018-01-01 00:00:00.000
2 Avtaar 2018-01-11 00:00:00.000
3 Kishan 2018-01-20 00:00:00.000

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 * 
FROM CTE
ORDER BY Dt DESC

--

Output-1

--                            

Id          Name                      Date
----------- ------------------------- -----------------------
1           Pawan                     2018-01-01 00:00:00.000
2           Avtaar                    2018-01-11 00:00:00.000
3           Kishan                    2018-01-20 00:00:00.000

(3 rows affected)

--

SOLUTION – 2

--


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 

SELECT
       Xmls.value('(//Id)[1]', 'INT') AS 'Id'
	  ,Xmls.value('(//Name)[1]', 'VARCHAR(25)') AS 'Name'
	  ,Xmls.value('(//Date)[1]', 'DATE') AS 'Date'	  	 
FROM @m
UNION ALL
SELECT
       Xmls.value('(//Id)[2]', 'INT') AS 'Id'
	  ,Xmls.value('(//Name)[2]', 'VARCHAR(25)') AS 'Name'
	  ,Xmls.value('(//Date)[2]', 'DATETIME') AS 'Date'	  	 
FROM @m
UNION ALL
SELECT
       Xmls.value('(//Id)[3]', 'INT') AS 'Id'
	  ,Xmls.value('(//Name)[3]', 'VARCHAR(25)') AS 'Name'
	  ,Xmls.value('(//Date)[3]', 'DATETIME') AS 'Date'	  	 
FROM @m

--

Output-2

--                            

Id          Name                      Date
----------- ------------------------- -----------------------
1           Pawan                     2018-01-01 00:00:00.000
2           Avtaar                    2018-01-11 00:00:00.000
3           Kishan                    2018-01-20 00:00:00.000

(3 rows affected)

--

SOLUTION – 3

--

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>'
 
SELECT
       T.N.value('Id[1]', 'INT') AS 'Id'
	  ,T.N.value('Name[1]', 'VARCHAR(25)') AS 'Name'
	  ,T.N.value('Date[1]', 'DATE') AS 'Date'	  	 
FROM @XML.nodes('/Items/Item') T(N)  	 


--

Output-3

--                            

Id          Name                      Date
----------- ------------------------- ----------
1           Pawan                     2018-01-01
2           Avtaar                    2018-01-11
3           Kishan                    2018-01-20

(3 rows affected)

--

Related XML Puzzles

1 https://msbiskills.com/2018/02/20/sql-puzzle-handling-special-characters-with-for-xml-path-puzzle/
2 https://msbiskills.com/2018/02/18/sql-puzzle-get-latest-record-from-xml/
3 https://msbiskills.com/2018/01/19/sql-puzzle-split-string-via-on-dot-using-xml/
4 https://msbiskills.com/2017/12/15/sql-puzzle-generate-xml-puzzle/
5 https://msbiskills.com/2017/10/27/sql-puzzle-the-xml-node-copy-puzzle/
6 https://msbiskills.com/2017/10/18/sql-puzzle-the-xml-explicit-puzzle/
7 https://msbiskills.com/2016/11/28/sql-puzzle-the-xml-path-puzzle/
8 https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-group-by-xml-path-puzzle/
9 https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-covert-comma-separated-values-to-a-table-using-cross-apply-xml-puzzle/

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.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

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