Fun with T-SQL

Visual XML tree in SQL

Created using recursive CTEs and SQL spatial geometry. Run in SQL 2008.

See other SQL fractals

Visual XML tree in SQL

T-SQL Source

declare @j xml
set @j = '
<rss version="2.0">
<channel>
	<title>RSS Example</title>
	<description>This is an example of an RSS feed</description>
	<link>http://www.domain.com/link.htm</link>
	<lastBuildDate>Mon, 28 Aug 2006 11:12:55 -0400 </lastBuildDate>
	<pubDate>Tue, 29 Aug 2006 09:00:00 -0400</pubDate>
	<item>
		<title>Item Example</title>
		<description>This is an example of an Item</description>
		<link>http://www.domain.com/link.htm</link>
		<guid isPermaLink="false"> 1102345</guid>
		<pubDate>Tue, 29 Aug 2006 09:00:00 -0400</pubDate>
	</item>
	<item>
		<title>Item Example</title>
		<description>This is an example of an Item</description>
		<link>http://www.domain.com/link.htm</link>
		<guid isPermaLink="false"> 1102345</guid>
		<pubDate>Tue, 29 Aug 2006 09:00:00 -0400</pubDate>
	</item>
</channel>
</rss>'
;
with tree(node,[path],parent,name,depth) as
(
  select @j, cast('0' as nvarchar(max)), cast(null as nvarchar(max)), @j.value('fn:local-name(*[1])','nvarchar(max)'),0
  union all
  select res.elem.query('.'),
    t.[path]+'.'+ cast(10000 + row_number() over (order by res.elem.value('fn:local-name(*[1])','nvarchar(max)')) as nvarchar(max)),
    t.[path],
    res.elem.value('fn:local-name(.[1])','nvarchar(max)'),
    t.depth+1
  from tree t cross apply t.node.nodes('*/*') res(elem)
),
points (x,y,name,[path],[parent]) as
(
  select
	depth, 
	-row_number() over (order by [path]),
	name, [path], parent
  from tree
),
points2 (x,y,name,yparent) as 
(
  select t.x, t.y, t.name, p.y
  from points t
  join points p on p.[path] = t.parent
)
select geometry::STGeomFromText('LINESTRING(' +
	convert(varchar,x) + ' ' + convert(varchar,y) + ',' +
	convert(varchar,x) + ' ' + convert(varchar,yparent) + ')',0),
	'' Name
	from points2
union all
select geometry::STGeomFromText('LINESTRING(' +
	convert(varchar,x) + ' ' + convert(varchar,y) + ',' +
	convert(varchar,x+5) + ' ' + convert(varchar,y) + ')',0),
	name Name
	from points2

	

See other SQL fractals