does SQL Server 2005 have something similar to Oracle's START WITH CONNECT
BY sql syntax, for returning hierarchical records?

Yes, look up recursive CTEs [SQL Server] in the BOL.

Another option you can look at..
http://omnibuzz-sql.blogspot.com/2006/06/recursive-function-with-cross-apply-vs.html

The Oracle syntax is a hidden cursor; if you want to avoid good SQL
coding you can use an explicit cursor that does the same thing.

Get a copy of TREES & HIERARCHIES IN SQL for sevreal diffrent ways of
doing this.  The recursive CTE approach has a lot of overhead; Nested
sets is good for computations; enumerated path is good for locating
sub-tree structures; etc.

The real trick in all of these models is the (complicated) CHECK()
constraints needed to assure that you actually have a tree and not a
more general graph.

column dataType
Joining 2 select statement with like fields
Views and CTEs performance problem
Adding a space in a field
Can't use Shared Memory protocol to connect