title: generate structured (xml) document from hierarchical table data (T-SQL)
body: <p>I have a table like this (simplified):</p>
  
  <pre><code>ID   |   Name  |   Parent
  ---------------------------------
  1    |  IND    |   NULL
  2    |  INS    |   5
  3    |  CON    |   NULL
  4    |  AUT    |   1
  5    |  FIN    |   NULL
  6    |  PHA    |   1
  7    |  CFIN   |   5
  8    |  CMRKT  |   7
  </code></pre>
  
  <p>DDL:</p>
  
  <pre><code>CREATE TABLE [dbo].[tblIndustryCodes](
          [IdIndustry] [int] IDENTITY(1,1) NOT NULL,
          [IndustryCode] [nvarchar](5) NULL,
          [IndustryName] [nvarchar](50) NULL,
          [ParentId] [int] NULL,
   CONSTRAINT [PK_tblIndustryCodes] PRIMARY KEY CLUSTERED (       [IdIndustry] ASC))
  </code></pre>
  
  <p>INSERTS:</p>
  
  <pre><code>INSERT INTO [tblIndustryCodes]
             ([IndustryCode]
             ,[IndustryName]
             ,[ParentId])
       VALUES
             ('IND','Industry',NULL),
             ('PHARM','Pharmacy',1),
             ('FIN','Finance',NULL),
             ('CFIN','Corporate Finance',3),
             ('CMRKT','Capital Markets',4)
  </code></pre>
  
  <p>And I'd like to generate a xml file from it which is structured according to the parent IDs</p>
  
  <p>like this (simplified)</p>
  
  <pre><code>&lt;IND&gt;
     &lt;AUT&gt;
     &lt;PHA&gt;
  &lt;CON&gt;
  &lt;FIN&gt;
     &lt;CFIN&gt;
        &lt;CMRKT&gt;
  </code></pre>
  
  <p>I believe its done maybe with some kinda recursion or something like that, but I don't know how. Any help is greatly appreciated!</p>
  
  <p>edit: Its a SQL Server Express 2008</p>
  
  <p>I don't really care if it's valid XML or not, because I only use it to populate a treeview control.</p>
  
  <p>edit2: I would probably use "FOR XML EXPLICIT" but I don't really understand the syntax when <strong>there is no fixed maximum depth of the tree.</strong> </p>
  
  <p>edit3: for easier understanding of the task, I added the DDL for the table</p>