title: Get specific nodes path from root tsql
body: <p>I want set data in table with tree structure.</p>
  
  <pre><code>DECLARE @temp TABLE 
  (
        Id INT
      , Name VARCHAR(50)
      , Parent INT
  )
  
  INSERT @temp 
  SELECT 1,' Great GrandFather Thomas Bishop', null UNION ALL
  SELECT 2,'Grand Mom Elian Thomas Wilson' , 1 UNION ALL
  SELECT 3, 'Dad James Wilson',2 UNION ALL
  SELECT 4, 'Uncle Michael Wilson', 2 UNION ALL
  SELECT 5, 'Aunt Nancy Manor', 2 UNION ALL
  SELECT 6, 'Grand Uncle Michael Bishop', 1 UNION ALL
  SELECT 7, 'Brother David James Wilson',3 UNION ALL
  SELECT 8, 'Sister Michelle Clark', 3 UNION ALL
  SELECT 9, 'Brother Robert James Wilson', 3 UNION ALL
  SELECT 10, 'Me Steve James Wilson', 3 
  </code></pre>
  
  <p>How can I get path of node from root for specific nodes? </p>
  
  <p>For example result for <code>Id IN (2, 5, 10)</code> is :</p>
  
  <pre><code>Id   Result
   2   Great GrandFather Thomas Bishop -&gt; Grand Mom Elian Thomas Wilson
   5   Great GrandFather Thomas Bishop -&gt; Grand Mom Elian Thomas Wilson -&gt; Aunt Nancy Manor
  10   Great GrandFather Thomas Bishop -&gt; Grand Mom Elian Thomas Wilson -&gt; Dad James Wilson -&gt; Me Steve James Wilson
  </code></pre>
  
  <p>For one id I use this T-SQL code, Please complete it :</p>
  
  <pre><code>;WITH cte AS 
  (
      SELECT *, t = 1
      FROM @temp
      WHERE Id = 10 -- &lt;-- your id
  
      UNION ALL
  
      SELECT t2.*, t + 1
      FROM cte t
      JOIN @temp t2 ON t.Parent = t2.Id
  )
  SELECT STUFF((
      SELECT ' -&gt; ' + Name
      FROM cte
      ORDER BY t DESC
      FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 4, '')
  Go
  </code></pre>
  
  <p>When I use <code>FOR XML PATH('')</code> speed is low, how can I use your T-SQL code without it?</p>