SQL Puzzle | READ Multi Level XML DATA using SQL – 1

In this puzzle you have to read Multi Level XML DATA using T-SQL. Here basically for each action and for each data you have to fetch value from the XML data.

Please check the sample input and the expected output.

Sample Input

--

<Items>	
  <action name="transaction_id" package="sqlserver">
    <type name="int64" package="package0" />
    <value>0</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <type name="unicode_string" package="package0" />
    <value>exec proc1</value>
  </action>
  <data name="duration">
    <type name="uint64" package="package0" />
    <value>120003768</value>
  </data>
  <data name="physical_reads">
    <type name="uint64" package="package0" />
    <value>16</value>
  </data>
</Items>

--

Expected Output

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

--

DECLARE @XML AS XML = 
'<Items>	
  <action name="transaction_id" package="sqlserver">
    <type name="int64" package="package0" />
    <value>0</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <type name="unicode_string" package="package0" />
    <value>exec proc1</value>
  </action>
  <data name="duration">
    <type name="uint64" package="package0" />
    <value>120003768</value>
  </data>
  <data name="physical_reads">
    <type name="uint64" package="package0" />
    <value>16</value>
  </data>
</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 | USING OUTER APPLY AND UNION ALL

--

DECLARE @XML AS XML = '
<Items>	
  <action name="transaction_id" package="sqlserver">
    <type name="int64" package="package0" />
    <value>0</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <type name="unicode_string" package="package0" />
    <value>exec proc1</value>
  </action>
  <data name="duration">
    <type name="uint64" package="package0" />
    <value>120003768</value>
  </data>
  <data name="physical_reads">
    <type name="uint64" package="package0" />
    <value>16</value>
  </data>
</Items>'
DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML 
;WITH CTE AS
(
    SELECT
		 p.value(N'(@name)[1]',N'nvarchar(200)') AS [Nm]
		,p.value(N'(value)[1]',N'nvarchar(200)') AS [value]         
    FROM @m as x
    OUTER APPLY x.Xmls.nodes(N'Items/action') AS u(p) 
	UNION ALL
	SELECT
		 p.value(N'(@name)[1]',N'nvarchar(200)') AS [Nm]
		,p.value(N'(value)[1]',N'nvarchar(200)') AS [value]         
    FROM @m as x
	OUTER APPLY x.Xmls.nodes(N'Items/data') AS u(p) 
)
SELECT * FROM CTE

--

Output-1

--                            

Id         Vals       Name                 Dt                   ty
---------- ---------- -------------------- -------------------- --------------------
1          a          Pawan                2018/01/01           1
2          NULL       Avtaar               2018/01/11           1
3          c          Kishan               2018/01/20           1

(3 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