SQL PUZZLE | The Child XML Node Puzzle | SQL Interview Question

24 Tuesday Apr 2018

Posted by Pawan Kumar Khowal in SQL Concepts, SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

SQL PUZZLE | The Child XML Node Puzzle | SQL Interview Question

There are two table here names Parents and TheChildren. In this puzzle you have to generate the XML using T-SQL, the catch here is that if any parent didn’t have any children then for that parent do not show the children node. Please see the sample input and expected output.

Sample Input

Parents

Id	Vals
2	Pawan
1	Avtaar

TheChildren

ChildId	ParentId	ChildName
1	2	Avika

Expected Output

--

<Families>
  <Parent>
    <ParentName>Pawan</ParentName>
    <Chilren>
      <Childs>
        <ChildName>Avika</ChildName>
      </Childs>
    </Chilren>
  </Parent>
  <Parent>
    <ParentName>Avtaar</ParentName>
  </Parent>
</Families>

--

Script – DDL and INSERT Sample Data

--

CREATE TABLE Parents
(
	  Id INT
	, Nm VARCHAR(10)
)
GO

INSERT INTO Parents VALUES
(2,'Pawan'),
(1,'Avtaar')
GO

CREATE TABLE TheChildren
(
	  ChildId INT
	, ParentId INT
	, ChildName VARCHAR(10)
)
GO

INSERT INTO TheChildren VALUES
(1,2,'Avika')
GO

SELECT * FROM Parents
GO

SELECT * FROM TheChildren
GO

--

SOLUTION – 1

--

SELECT
(
    SELECT p.Nm ParentName,
    (
        SELECT c.ChildName
        FROM   TheChildren c
        WHERE  p.Id = c.ParentId
        FOR XML PATH('Childs'),TYPE
    ) AS Chilren
    FROM Parents P
    FOR XML PATH('Parent'), TYPE
) FOR XML PATH('Families')
GO

--

Output – 1

--

<Families>
  <Parent>
    <ParentName>Pawan</ParentName>
    <Chilren>
      <Childs>
        <ChildName>Avika</ChildName>
      </Childs>
    </Chilren>
  </Parent>
  <Parent>
    <ParentName>Avtaar</ParentName>
  </Parent>
</Families>

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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

SQL PUZZLE | Fetch ALL Nodes & VALUES dynamically from XML? | Advanced SQL

23 Monday Apr 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 1 Comment

Tags

SQL PUZZLE | Fetch ALL Nodes & VALUES dynamically from XML? | Advanced SQL

In this you have to go through the entire XML and get all the nodes and their values by just passing the node name. Please see the sample input and expected output.

Sample-1

--

<?xml version="1.0"?>
<Gate>	
	<MyMethods>
		<Cell>3264532</Cell>
		<Email>Pawan@MSBISKills.com</Email>
		<WebSite>MSBISKills.com</WebSite>
		<Fax></Fax>		
	</MyMethods>	
</Gate>

--

Expected output-1

Nm	Vals
Cell	3264532
Email	Pawan@MSBISKills.com
WebSite	MSBISKills.com
Fax

Script – DDL and INSERT sample data

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate>	
	<MyMethods>
		<Cell>3264532</Cell>
		<Email>Pawan@MSBISKills.com</Email>
		<WebSite>MSBISKills.com</WebSite>
		<Fax></Fax>		
	</MyMethods>	
</Gate>'

--

SOLUTION – 1

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate>	
	<MyMethods>
		<Cell>3264532</Cell>
		<Email>Pawan@MSBISKills.com</Email>
		<WebSite>MSBISKills.com</WebSite>
		<Fax></Fax>		
	</MyMethods>	
</Gate>'

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML
;WITH CTE AS
(
	SELECT 
		 p.value('local-name(.)','VARCHAR(100)') Nm
		,p.value('.[1]','VARCHAR(MAX)') Vals		
	FROM @m x
	OUTER APPLY x.Xmls.nodes(N'/Gate/MyMethods/*') AS u(p)
)
SELECT * FROM CTE

--

Output – 1

--

Nm                     Vals
---------------------- -------------------------
Cell                   3264532
Email                  Pawan@MSBISKills.com
WebSite                MSBISKills.com
Fax                    

(4 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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

SQL PUZZLE | Fetch ALL attributes & values dynamically from XML? | Advanced SQL

19 Thursday Apr 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

SQL PUZZLE | Fetch ALL attributes & values dynamically from XML? | Advanced SQL

In this you have to go through the XML and get all the attributes names and values without providing the node names. Please see the sample input and expected output.

Sample-1

--

<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>

--

Expected output-1

Nm	Vals
specVersion	3.20
UserId	PawanWithSQLServer
type	Cell
type	Email
type	WebSite
type	Fax

Script – DDL and INSERT sample data

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>'

--

SOLUTION – 1

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>'

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML
SELECT
		p.value('local-name(.)', 'VARCHAR(100)') As Nm,
		p.value('.','NVARCHAR(MAX)') As Vals		
FROM @XML.nodes('//node()//@*') AS u(p)

--

Output – 1

--

Nm                       Vals
------------------------ -------------------------
specVersion              3.20
UserId                   PawanWithSQLServer
type                     Cell
type                     Email
type                     WebSite
type                     Fax

(6 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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

SQL PUZZLE | The OPTIONAL XML Reading? | Advanced SQL

18 Wednesday Apr 2018

≈ Leave a comment

Tags

SQL PUZZLE | The OPTIONAL XML Reading? | Advanced SQL

In this you have to fetch the data from the XML. The catch here is that the position of Method type=”Email” is not fixed. It also may or may not come. We have to prepare for all the contingencies. If the Method type=”Email” tag is present we should fetch the value else we should get blank. Please see the sample input and expected output.

Sample-1

--

<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>

--

Expected output-1

UserName	Cell	Email	WebSite	Fax
PawanWithSQLServer	3264532	Pawan@MSBISKills.com	MSBISKills.com

Sample-2 | POSITION Changed for Method type=”Email”

--

<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Fax"></Method>	
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
	</MyMethods>
	</User>
</Gate>

--

Expected output-2

UserName	Cell	Email	WebSite	Fax
PawanWithSQLServer	3264532	Pawan@MSBISKills.com	MSBISKills.com

Sample-3 | Method type=”Email” is not present

--

<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Fax"></Method>			
		<Method type="WebSite">MSBISKills.com</Method>
	</MyMethods>
	</User>
</Gate>

--

Expected output-3

UserName	Cell	Email	WebSite	Fax
PawanWithSQLServer	3264532		MSBISKills.com

Script – DDL and INSERT sample data

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>'

--

SOLUTION – 1

--

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT 
		     p1.value(N'(@UserId)[1]',N'nvarchar(30)') AS [UserName]					
			,IIF(p.value(N'(Method/@type="Cell")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Cell"])[1]',N'nvarchar(30)'),'') AS [Cell]
			,IIF(p.value(N'(Method/@type="Email")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Email"])[1]',N'nvarchar(30)'),'') AS [Email]
			,IIF(p.value(N'(Method/@type="WebSite")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="WebSite"])[1]',N'nvarchar(30)'),'') AS [WebSite]
			,IIF(p.value(N'(Method/@type="Fax")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Fax"])[1]',N'nvarchar(30)'),'') AS [Fax]       
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Gate/User/MyMethods') AS u(p)
	OUTER APPLY x.Xmls.nodes(N'/Gate/User') AS u1(p1)
)
SELECT * FROM CTE
GO

--

Testing – 1

--


DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
		<Method type="Fax"></Method>		
	</MyMethods>
	</User>
</Gate>'

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT 
		     p1.value(N'(@UserId)[1]',N'nvarchar(30)') AS [UserName]						
			,IIF(p.value(N'(Method/@type="Cell")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Cell"])[1]',N'nvarchar(30)'),'') AS [Cell]	
			,IIF(p.value(N'(Method/@type="Email")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Email"])[1]',N'nvarchar(30)'),'') AS [Email]	
			,IIF(p.value(N'(Method/@type="WebSite")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="WebSite"])[1]',N'nvarchar(30)'),'') AS [WebSite]	
			,IIF(p.value(N'(Method/@type="Fax")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Fax"])[1]',N'nvarchar(30)'),'') AS [Fax]			       
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Gate/User/MyMethods') AS u(p)
	OUTER APPLY x.Xmls.nodes(N'/Gate/User') AS u1(p1)
)
SELECT * FROM CTE
GO

--OUTPUT

UserName                 Cell          Email                      WebSite            Fax      
--------------------- -- ---------- -- ----------------------- -- ---------------- - -------- 
PawanWithSQLServer       3264532       Pawan@MSBISKills.com       MSBISKills.com              
                                                                                              
(1 row affected)  
--

Testing – 2 | POSITION Changed for Method type=”Email”

--


DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Fax"></Method>	
		<Method type="Email">Pawan@MSBISKills.com</Method>
		<Method type="WebSite">MSBISKills.com</Method>
	</MyMethods>
	</User>
</Gate>'

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT 
		     p1.value(N'(@UserId)[1]',N'nvarchar(30)') AS [UserName]						
			,IIF(p.value(N'(Method/@type="Cell")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Cell"])[1]',N'nvarchar(30)'),'') AS [Cell]	
			,IIF(p.value(N'(Method/@type="Email")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Email"])[1]',N'nvarchar(30)'),'') AS [Email]	
			,IIF(p.value(N'(Method/@type="WebSite")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="WebSite"])[1]',N'nvarchar(30)'),'') AS [WebSite]	
			,IIF(p.value(N'(Method/@type="Fax")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Fax"])[1]',N'nvarchar(30)'),'') AS [Fax]			       
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Gate/User/MyMethods') AS u(p)
	OUTER APPLY x.Xmls.nodes(N'/Gate/User') AS u1(p1)
)
SELECT * FROM CTE

GO

--OUTPUT


UserName                 Cell          Email                      WebSite            Fax      
--------------------- -- ---------- -- ----------------------- -- ---------------- - -------- 
PawanWithSQLServer       3264532       Pawan@MSBISKills.com       MSBISKills.com              
                                                                                              
(1 row affected) 
--

Testing – 3 | Method type=”Email” is not Present

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate specVersion="3.20">
	<User UserId="PawanWithSQLServer">
	<MyMethods>
		<Method type="Cell">3264532</Method>
		<Method type="Fax"></Method>			
		<Method type="WebSite">MSBISKills.com</Method>
	</MyMethods>
	</User>
</Gate>'

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT 
		     p1.value(N'(@UserId)[1]',N'nvarchar(30)') AS [UserName]						
			,IIF(p.value(N'(Method/@type="Cell")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Cell"])[1]',N'nvarchar(30)'),'') AS [Cell]	
			,IIF(p.value(N'(Method/@type="Email")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Email"])[1]',N'nvarchar(30)'),'') AS [Email]	
			,IIF(p.value(N'(Method/@type="WebSite")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="WebSite"])[1]',N'nvarchar(30)'),'') AS [WebSite]	
			,IIF(p.value(N'(Method/@type="Fax")',N'nvarchar(30)') = 'true'
			,p.value(N'(Method[@type="Fax"])[1]',N'nvarchar(30)'),'') AS [Fax]			       
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'/Gate/User/MyMethods') AS u(p)
	OUTER APPLY x.Xmls.nodes(N'/Gate/User') AS u1(p1)
)
SELECT * FROM CTE
GO

--OUTPUT

UserName              Cell         Email      WebSite             Fax     
--------------------  ---------- - -------- - ----------------- - ------- -
PawanWithSQLServer    3264532                 MSBISKills.com              
                                                                          
(1 row affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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

SQL Puzzle | Multiple Ways to READ XML DATA using SQL

21 Wednesday Feb 2018

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 7 Comments

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

Improving my SQL BI Skills

Tag Archives: sql server parse xml column

SQL PUZZLE | The Child XML Node Puzzle | SQL Interview Question

SQL PUZZLE | Fetch ALL Nodes & VALUES dynamically from XML? | Advanced SQL

SQL PUZZLE | Fetch ALL attributes & values dynamically from XML? | Advanced SQL

SQL PUZZLE | The OPTIONAL XML Reading? | Advanced SQL

SQL Puzzle | Multiple Ways to READ XML DATA using SQL

Share this

Share this

Share this

Share this

Share this