SQL PUZZLE | Fetch ALL Nodes & VALUES dynamically from XML? | Advanced SQL

In this you have to go through the entire XML and get all the nodes and their values by just passing the node name. Please see the sample input and expected output.

Sample-1

--

<?xml version="1.0"?>
<Gate>	
	<MyMethods>
		<Cell>3264532</Cell>
		<Email>Pawan@MSBISKills.com</Email>
		<WebSite>MSBISKills.com</WebSite>
		<Fax></Fax>		
	</MyMethods>	
</Gate>

--

Expected output-1

Script – DDL and INSERT sample data

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate>	
	<MyMethods>
		<Cell>3264532</Cell>
		<Email>Pawan@MSBISKills.com</Email>
		<WebSite>MSBISKills.com</WebSite>
		<Fax></Fax>		
	</MyMethods>	
</Gate>'

--

SOLUTION – 1

--

DECLARE @XML AS XML = '<?xml version="1.0"?>
<Gate>	
	<MyMethods>
		<Cell>3264532</Cell>
		<Email>Pawan@MSBISKills.com</Email>
		<WebSite>MSBISKills.com</WebSite>
		<Fax></Fax>		
	</MyMethods>	
</Gate>'

DECLARE @m TABLE  (Xmls XML)
INSERT INTO @m SELECT @XML
;WITH CTE AS
(
	SELECT 
		 p.value('local-name(.)','VARCHAR(100)') Nm
		,p.value('.[1]','VARCHAR(MAX)') Vals		
	FROM @m x
	OUTER APPLY x.Xmls.nodes(N'/Gate/MyMethods/*') AS u(p)
)
SELECT * FROM CTE

--

Output – 1

--

Nm                     Vals
---------------------- -------------------------
Cell                   3264532
Email                  Pawan@MSBISKills.com
WebSite                MSBISKills.com
Fax                    

(4 rows 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.

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