title: Using TSQL to generate XML
body: <p>I am trying to generate some XML using TSQL and having some trouble, I was able to get to traverse 2 levels but getting stuck at 3.  How it should work in the example I have posted you have to Purchase Orders, the first purchase order only has one part with one release for that part.  The second Purchase order as 2 parts each part with its own release.  At the end I should have 2 XML documents that traverse 3 levels, release/part/purchase order.  I think I am getting confused when linking to the next stage up as well as the group by.The example only shows 2 parts but there can be many parts to one purchase order but only ever one release per part, any help would be much appreciated.  </p>
  
  <pre><code>DECLARE @sdv AS TABLE    
  (
  PID INT IDENTITY(1,1) PRIMARY KEY,
  PurchaseOrder varchar(20),
  PartNo int,
  Release int
  )
  
  
  INSERT INTO @sdv values ('PURC123', 111, 1)
  INSERT INTO @sdv values ('PURC333', 222, 1)
  INSERT INTO @sdv values ('PURC333', 333, 1)
  
  
  select 
      PurchaseOrder,
  (
  select
      PartNo,
  (
  select
      Release
  from
  @sdv a3
  where 
  a3.Release = a2.Release and
  a3.PartNo = a2.PartNo and 
  a3.PurchaseOrder = a1.PurchaseOrder
  for xml path('Release'), type
  ) 
  from 
  @sdv a2
  where 
  a2.PartNo = a1.PartNo
  group by a2.PartNo
  for xml path('part'), type
  ) from @sdv a1
  group by PurchaseOrder
  for xml path('Purchase')
  </code></pre>