title: Hierarchy in SQL server 2005 with XML
body: <p>I wonder is there anyway to select hierarchy in SQL server 2005 and return xml format?
  I have a database with a lot of data (about 2000 to 3000 records), and i am now using a function in SQL server 2005 to retrieve the data in hierarchy and return an XML but it seems not perfect because it's too slow when there is a lot of data</p>
  
  <p>Here is my function</p>
  
  <p><strong>Database</strong></p>
  
  <pre><code>ID      Name      Parent       Order
  </code></pre>
  
  <p><strong>Function</strong></p>
  
  <pre><code> CREATE FUNCTION [dbo].[GetXMLTree]
  (
      @PARENT bigint
  )
  RETURNS XML
  AS
      BEGIN
      RETURN /* value */
  (SELECT [ID] AS "@ID",
          [Name] AS "@Name",
          [Parent] AS "@Parent",
          [Order] AS "@Order",
          dbo.GetXMLTree(Parent).query('/xml/item')
      FROM MyDatabaseTable
      WHERE [Parent]=@PARENT
      ORDER BY [Order]
      FOR XML PATH('item'),ROOT('xml'),TYPE)
      END
  </code></pre>
  
  <p>I would like to use XML in hierarchy because with me there's alot of thing to do with it :)
  Any best solutions plzzzzz</p>