Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

XQuery Labs 69-Writing a Recursive query that walks through an XML Hierarchy

Nov 11 2013 4:37PM by Jacob Sebastian   

I came across this question which I thought is interesting enough to include in the XQuery Labs series. The requirement is to write a query that processes an XML document and present the information in a hierarchical manner.

Sample XML input:

<root>
	<elem10 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
		<elem101010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
			<elem10101010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10101020 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
		</elem101010>
		<elem101020 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
			<elem10102010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10102020 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10102030 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10102040 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10102050 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
		</elem101020>
	</elem10>
	<elem15 Attr01="0.08" Attr02="-0.05" Attr03="9.00" Attr04="9.00" Attr05="7.00" Attr06="4.00">
		<elem151010 Attr01="0.03" Attr02="-0.01" Attr03="9.00" Attr04="9.00" Attr05="2.00" Attr06="1.00">
			<elem15101010 Attr01="0.02" Attr02="-0.01" Attr03="9.00" Attr04="9.00" Attr05="1.00" Attr06="1.00" />
			<elem15101020 Attr01="0.01" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="1.00" Attr06="0.00" />
			<elem15101030 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00" />
			<elem15101040 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00" />
			<elem15101050 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00" />
		</elem151010>
		<elem151020 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00">
			<elem15102010 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00" />
		</elem151020>
		<elem151030 Attr01="0.02" Attr02="-0.02" Attr03="9.00" Attr04="9.00" Attr05="3.00" Attr06="1.00">
			<elem15103010 Attr01="0.01" Attr02="-0.02" Attr03="9.00" Attr04="9.00" Attr05="1.00" Attr06="1.00" />
			<elem15103020 Attr01="0.01" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="2.00" Attr06="0.00" />
		</elem151030>
	</elem15>
	<elem20 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
		<elem201010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
			<elem20101010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
		</elem201010>
		<elem201020 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
			<elem20102010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
		</elem201020>
	</elem20>
</root>

Expected results:

ElementLV1      ElementLV2      ElementLV3      Attr01   Attr02    Attr03   Attr04   Attr05   Attr06 
--------------- --------------- --------------- -------- --------- -------- -------- -------- -------
elem10          NULL            NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101010      NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101010      elem10101010    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101010      elem10101020    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102010    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102020    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102030    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102040    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102050    9.00     9.00      9.00     9.00     9.00     9.00
elem15          NULL            NULL            0.08     -0.05     9.00     9.00     7.00     4.00
elem15          elem151010      NULL            0.03     -0.01     9.00     9.00     2.00     1.00
elem15          elem151010      elem15101010    0.02     -0.01     9.00     9.00     1.00     1.00
elem15          elem151010      elem15101020    0.01     0.00      9.00     9.00     1.00     0.00
elem15          elem151010      elem15101030    0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151010      elem15101040    0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151010      elem15101050    0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151020      NULL            0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151020      elem15102010    0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151030      NULL            0.02     -0.02     9.00     9.00     3.00     1.00
elem15          elem151030      elem15103010    0.01     -0.02     9.00     9.00     1.00     1.00
elem15          elem151030      elem15103020    0.01     0.00      9.00     9.00     2.00     0.00
elem20          NULL            NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem20          elem201010      NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem20          elem201010      elem20101010    9.00     9.00      9.00     9.00     9.00     9.00
elem20          elem201020      NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem20          elem201020      elem20102010    9.00     9.00      9.00     9.00     9.00     9.00

While reviewing this, I thought of writing a recursive query that travels through the nodes of the input document and presents the information in the required format. Here is the code that does this.

DECLARE @x XML
SELECT @x = '
<root>
	<elem10 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
		<elem101010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
			<elem10101010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10101020 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
		</elem101010>
		<elem101020 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
			<elem10102010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10102020 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10102030 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10102040 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
			<elem10102050 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
		</elem101020>
	</elem10>
	<elem15 Attr01="0.08" Attr02="-0.05" Attr03="9.00" Attr04="9.00" Attr05="7.00" Attr06="4.00">
		<elem151010 Attr01="0.03" Attr02="-0.01" Attr03="9.00" Attr04="9.00" Attr05="2.00" Attr06="1.00">
			<elem15101010 Attr01="0.02" Attr02="-0.01" Attr03="9.00" Attr04="9.00" Attr05="1.00" Attr06="1.00" />
			<elem15101020 Attr01="0.01" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="1.00" Attr06="0.00" />
			<elem15101030 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00" />
			<elem15101040 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00" />
			<elem15101050 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00" />
		</elem151010>
		<elem151020 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00">
			<elem15102010 Attr01="0.00" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="0.00" Attr06="0.00" />
		</elem151020>
		<elem151030 Attr01="0.02" Attr02="-0.02" Attr03="9.00" Attr04="9.00" Attr05="3.00" Attr06="1.00">
			<elem15103010 Attr01="0.01" Attr02="-0.02" Attr03="9.00" Attr04="9.00" Attr05="1.00" Attr06="1.00" />
			<elem15103020 Attr01="0.01" Attr02="0.00" Attr03="9.00" Attr04="9.00" Attr05="2.00" Attr06="0.00" />
		</elem151030>
	</elem15>
	<elem20 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
		<elem201010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
			<elem20101010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
		</elem201010>
		<elem201020 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00">
			<elem20102010 Attr01="9.00" Attr02="9.00" Attr03="9.00" Attr04="9.00" Attr05="9.00" Attr06="9.00" />
		</elem201020>
	</elem20>
</root>'

;WITH cte AS (
	SELECT
		v1.value('local-name(.)','VARCHAR(15)') AS ElementLV1,
		CAST('' AS VARCHAR(15)) AS ElementLV2,
		CAST('' AS VARCHAR(15)) AS ElementLV3,
		v1.value('@Attr01','DECIMAL(5,2)') AS Attr01,
		v1.value('@Attr02','DECIMAL(5,2)') AS Attr02,
		v1.value('@Attr03','DECIMAL(5,2)') AS Attr03,
		v1.value('@Attr04','DECIMAL(5,2)') AS Attr04,
		v1.value('@Attr05','DECIMAL(5,2)') AS Attr05,
		v1.value('@Attr06','DECIMAL(5,2)') AS Attr06,
		CAST('000' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT '')) AS VARCHAR) AS VARCHAR(100)) Sort,
		v1.query('.') AS data,
		1 AS lvl
	FROM @x.nodes ('/root/*') a(v1)
	UNION ALL
	SELECT
		cte.ElementLV1,
		CASE 
			WHEN cte.lvl = 1 THEN v1.value('local-name(.)','VARCHAR(15)') 
			ELSE ElementLV2
		END AS ElementLV2,
		CASE 
			WHEN cte.lvl = 2 THEN v1.value('local-name(.)','VARCHAR(15)') 
			ELSE ElementLV3
		END AS ElementLV3,
		v1.value('@Attr01','DECIMAL(5,2)') AS Attr01,
		v1.value('@Attr02','DECIMAL(5,2)') AS Attr02,
		v1.value('@Attr03','DECIMAL(5,2)') AS Attr03,
		v1.value('@Attr04','DECIMAL(5,2)') AS Attr04,
		v1.value('@Attr05','DECIMAL(5,2)') AS Attr05,
		v1.value('@Attr06','DECIMAL(5,2)') AS Attr06,
		CAST(cte.sort + '000' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT '')) AS VARCHAR) AS VARCHAR(100)),
		v1.query('.') AS data,
		cte.lvl + 1 AS lvl
	FROM cte
	CROSS APPLY cte.data.nodes ('*/*') a(v1)
	WHERE lvl < 3
)

SELECT 
	NULLIF(ElementLV1,'') AS ElementLV1,
	NULLIF(ElementLV2,'') AS ElementLV2,
	NULLIF(ElementLV3,'') AS ElementLV3,
	Attr01,
	Attr02,
	Attr03,
	Attr04,
	Attr05,
	Attr06
FROM cte
ORDER BY sort

/*
ElementLV1      ElementLV2      ElementLV3      Attr01   Attr02    Attr03   Attr04   Attr05   Attr06 
--------------- --------------- --------------- -------- --------- -------- -------- -------- -------
elem10          NULL            NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101010      NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101010      elem10101010    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101010      elem10101020    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102010    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102020    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102030    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102040    9.00     9.00      9.00     9.00     9.00     9.00
elem10          elem101020      elem10102050    9.00     9.00      9.00     9.00     9.00     9.00
elem15          NULL            NULL            0.08     -0.05     9.00     9.00     7.00     4.00
elem15          elem151010      NULL            0.03     -0.01     9.00     9.00     2.00     1.00
elem15          elem151010      elem15101010    0.02     -0.01     9.00     9.00     1.00     1.00
elem15          elem151010      elem15101020    0.01     0.00      9.00     9.00     1.00     0.00
elem15          elem151010      elem15101030    0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151010      elem15101040    0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151010      elem15101050    0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151020      NULL            0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151020      elem15102010    0.00     0.00      9.00     9.00     0.00     0.00
elem15          elem151030      NULL            0.02     -0.02     9.00     9.00     3.00     1.00
elem15          elem151030      elem15103010    0.01     -0.02     9.00     9.00     1.00     1.00
elem15          elem151030      elem15103020    0.01     0.00      9.00     9.00     2.00     0.00
elem20          NULL            NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem20          elem201010      NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem20          elem201010      elem20101010    9.00     9.00      9.00     9.00     9.00     9.00
elem20          elem201020      NULL            9.00     9.00      9.00     9.00     9.00     9.00
elem20          elem201020      elem20102010    9.00     9.00      9.00     9.00     9.00     9.00
*/

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

Tags: 


Jacob Sebastian
1 · 100% · 32235
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"XQuery Labs 69-Writing a Recursive query that walks through an XML Hierarchy" rated 5 out of 5 by 1 readers
XQuery Labs 69-Writing a Recursive query that walks through an XML Hierarchy , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]