Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

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

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-

https://docs.microsoft.com/en-us/sql/relational-databases/xml/load-xml-data?view=sql-server-2017

Related XML Puzzles

1 https://msbiskills.com/2018/02/21/sql-puzzle-read-data-from-xml-1/
2 https://msbiskills.com/2018/02/20/sql-puzzle-handling-special-characters-with-for-xml-path-puzzle/
3 https://msbiskills.com/2018/02/18/sql-puzzle-get-latest-record-from-xml/
4 https://msbiskills.com/2018/01/19/sql-puzzle-split-string-via-on-dot-using-xml/
5 https://msbiskills.com/2017/12/15/sql-puzzle-generate-xml-puzzle/
6 https://msbiskills.com/2017/10/27/sql-puzzle-the-xml-node-copy-puzzle/
7 https://msbiskills.com/2017/10/18/sql-puzzle-the-xml-explicit-puzzle/
8 https://msbiskills.com/2016/11/28/sql-puzzle-the-xml-path-puzzle/
9 https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-group-by-xml-path-puzzle/
10 https://pawankkmr.wordpress.com/2015/03/25/t-sql-query-covert-comma-separated-values-to-a-table-using-cross-apply-xml-puzzle/
11 https://msbiskills.com/2018/03/09/sql-puzzle-read-multi-level-xml-data-using-sql-1/

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

Advertisements