SQL Server 2005: Recursive Hierarchies to XML
Suppose we have a recursive hierarchy stored in a relational database and we want to write it to XML. This might be for a variety of reasons – e.g. as a pre-cached input to a UI control, to export to another system using a pre-defined format, etc.
In SQL Server 2000, in order to get it straight to XML using FOR XML EXPLICIT, we would have to know the depth of the lowest node beforehand (without doing some very ugly dynamic SQL), so this does not help us.
It would be useful to access the data in the same order that it will appear in the XML. I.e.
Node1
Node2
Node3
Node4
Node5
Getting at the data in this order will allow us to iterate through the nodes in sequential order. This avoids using the DOM and is significantly quicker and more efficient as it avoids loading the whole structure into memory.
We could achieve this in SQL Server 2000 using a recursive table-valued UDF. In SQL Server 2005, we also have the option of using a recursive Common Table Expression (CTE) to achieve the same functional result. Let’s compare the two ways of doing it.
A CTE is a temporary named resultset referenced by a subsequent “outer query”. They can provide similar functionality to views and derived tables, but their real value is in recursive queries. Recursive CTE’s contain an “anchor member” and a “recursive member”, which are connected by a UNION ALL operator. They can be encapsulated by UDFs for reusability.
Data Preparation
Let’s create a table and insert hierarchical values.
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT F
相关文档：
Google dorks sql injection:
inurl:index.php?id=
inurl:trainers.php?id=
inurl:buy.php?category=
inurl:article.php?ID=
inurl:Play_old.php?id=
inurl:declaration_more.php?decl_id=
inurl:Pageid=
inurl:game ......
Cross Apply使表可以和表值函数结果进行join, 这样表值函数的参数就可以使用一个结果集，而不是一个标量值，下面是book online的原文，有例子，有解释。
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function act ......
MySQL:
SELECT column from table
ORDER BY RAND()
LIMIT 1
PostgreSQL:
SELECT column from table
ORDER BY RANDOM()
LIMIT 1
Microsoft SQL Server:
SELECT TOP 1 column from table
ORDER BY NEWID()
IBM DB2
SELECT column, RAND() as IDX
from table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Thanks Ti ......
I'm continually trying to track down what service packs are installed on various SQL Servers I support. I can never find the right support page on Microsoft's site. So here's an article with all the SQL Server version information I can track down. If you know of any older versions or can help me fil ......
#Region " 命名空间 "
Imports System.Data
Imports System.Data.SqlClient
#End Region
Public Class DBCommon
Implements IDisposable
#Region " 成员变量 "
Private conn As SqlConnection
Private cmd As SqlCommand
Private trans As SqlTransaction
#End Region
#Region " 构造函数 "
......
