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