SQL PUZZLE | How to INSERT XML file into SQL Server & fetch data? | SQL Interview Question

In this puzzle you have to read the xml file from a location and insert the complete XML file into a SQL Server table. After that you have to fetch the data from SQL Server table. Please read the sample input and the expected output.

Sample Input

--

<?xml version="1.0"?>
<MyGate sVersion="0.20">
	<Header>
	<SenderID>A</SenderID>
	<ReceiverID>B</ReceiverID>
	<TypeID>MasterClass</TypeID>
	<Count>10</Count>
	<Dt type="SQLSERVER">12/12/2017 11:32</Dt>
	</Header>
</MyGate>

--

Expected output

–Table 1 after Insertion

--
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>myXML</th><th>InsertionDate</th></tr></thead><tbody>
 <tr><td><MyGate sVersion="0.20"><Header><SenderID>A</SenderID><ReceiverID>B</ReceiverID><TypeID>MasterClass</TypeID><Count>10</Count><Dt type="SQLSERVER">12/12/2017 11:32</Dt></Header></MyGate></td><td>2018-04-18 12:52:54.873</td></tr>
</tbody></table>
--

–Fetched data

Script – DDL and INSERT sample data

--

--Create a new table to insert the data
CREATE TABLE myXMLData
(
	 myXML XML
	,InsertionDate DATETIME DEFAULT GETDATE()
)
GO

--XML data--

<?xml version="1.0"?>
<MyGate sVersion="0.20">
	<Header>
	<SenderID>A</SenderID>
	<ReceiverID>B</ReceiverID>
	<TypeID>MasterClass</TypeID>
	<Count>10</Count>
	<Dt type="SQLSERVER">12/12/2017 11:32</Dt>
	</Header>
</MyGate>

--

SOLUTION – 1 | Bulk loading XML data

--

--Create a new table to insert the data
CREATE TABLE myXMLData
(
	 myXML XML
	,InsertionDate DATETIME DEFAULT GETDATE()
)
GO

--Insert the complete XML file into the database table using Bulk loading XML data
INSERT INTO myXMLData(myXML)
SELECT xCol  
FROM    
(
	 SELECT *      
		FROM OPENROWSET (BULK 'E:\SampleXMLFile.xml', SINGLE_CLOB)   
	 AS xCol
)AS R(xCol)  
GO

--Read the Data
SELECT * FROM myXMLData
GO

--

OUTPUT – 1

--

myXML
--------
<MyGate sVersion="0.20"><Header>
<SenderID>A</SenderID>
<ReceiverID>B</ReceiverID><TypeID>MasterClass</TypeID>
<Count>10</Count>
<Dt type="SQLSERVER">12/12/2017 11:32</Dt>
</Header></MyGate>
(1 row affected)

--

SOLUTION – Selecting Data

--

--SELECT the data from the SQL Server Table
;WITH CTE AS
(
    SELECT 
		    p1.value(N'(SenderID)[1]',N'nvarchar(30)') AS [SId]
		   ,p1.value(N'(ReceiverID)[1]',N'nvarchar(30)') AS [RId]
		   ,p1.value(N'(TypeID)[1]',N'nvarchar(30)') AS [TypeID]
		   ,p1.value(N'(Count)[1]',N'nvarchar(30)') AS [Ct]		      
		   ,p1.value(N'(Dt)[1]', 'DATETIME') AS [Dt]		   
    FROM myXMLData as xm  
	OUTER APPLY xm.myXML.nodes(N'/MyGate/Header') AS u1(p1)
)
SELECT * FROM CTE

--

OUTPUT

--

SenderId       ReceiverId    TypeID          Count    Header_Date
-------------- ------------- --------------- -------- -----------------------
A              B             MasterClass     10       2017-12-12 11:32:00.000

(1 row affected)

--

Reference-

Related XML Puzzles

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.

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