SQL Puzzle | Read and PIVOT XML Data while reading in SQL

In this puzzle you have to read XML and count how many Fails are there and how many people got passed using T-SQL

Please check the sample input and the expected output.

Sample Input

<Items>
	<Item Fail="2" Pass="10"/>
	<Item Fail="3" Pass="1"/>
</Items>

Expected Output

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

--

DECLARE @XML AS XML = 
'<Items>
	<Item Fail="2" Pass="10"/>
	<Item Fail="3" Pass="1"/>
</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 xml = '
<Items>
	<Item Fail="2" Pass="10"/>
	<Item Fail="3" Pass="1"/>
</Items>
'
;WITH CTE AS
(
	SELECT 
		 a.value('local-name(.)','VARCHAR(10)') Attr
		,a.value('.','INT') Vals
	FROM  
		@XML.nodes('/Items/Item ')  as C1(u)
	CROSS APPLY C1.u.nodes('./@*') as C2(a)
)
SELECT Attr,SUM(Vals) Vals
FROM CTE
GROUP BY Attr

--

Output-1

--                            

Attr       Vals
---------- -----------
Fail       5
Pass       11

(2 rows affected)


--

Related XML Puzzles

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