tree.sql

CREATE FUNCTION [dbo].[GetSubAssemblyTree](@JobAssemblyId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
  (SELECT	jar.id as "@Id", 
			jar.Sequence AS '@Sequence',
			jar.Quantity AS '@Quantity',
			jar.Drawing AS '@Drawing',
			ChildJobAssembly_Id AS 'Node/@JobAssembly_Id',
			ja.Reference AS 'Node/@Reference',
			ja.ReferenceSequence AS 'Node/@ReferenceSequence',
			ja.PrefixLength AS 'Node/@PrefixLength',
			ja.ChildAssemblyPrefix AS 'Node/@ChildAssemblyPrefix',
			ja.Short_Description AS 'Node/@ShortDescription',
			CASE WHEN ja.Description <> '' THEN ja.Description ELSE NULL END AS 'Node/Description',
			jat.Code AS 'Node/Type/@Code',
			jat.Description AS 'Node/Type/@Description',
			(SELECT pp.Reference AS 'Reference',
			
					CASE WHEN pp.Inventory_Flag = 1 THEN pp.QtyPieces END AS 'Inventory/PieceInfo/@Quantity',
					CASE WHEN pp.Inventory_Flag = 1 THEN pp.Length END AS 'Inventory/PieceInfo/@Length',
					CASE WHEN pp.Inventory_Flag = 1 THEN pp.Width END AS 'Inventory/PieceInfo/@Width',
					CASE WHEN pp.Inventory_Flag = 1 THEN pp.CoatingPercent END AS 'Inventory/PieceInfo/@CoatingPercent',
					CASE WHEN pp.Inventory_Flag = 1 THEN pa.Code END AS 'Inventory/Part/@Code',
					CASE WHEN pp.Inventory_Flag = 1 THEN pa.Description END AS 'Inventory/Part/@Description',
					CASE WHEN pp.Inventory_Flag = 0 THEN Qty END AS 'NonPieceInfo/Quantity', 
					CASE WHEN pp.Inventory_Flag = 0 THEN un.Code END AS 'NonPieceInfo/Unit/@Code' 
			FROM tblJobPlanPart pp
				LEFT JOIN tblPart pa ON pa.Id = pp.Part_Id
				LEFT JOIN tblUnit un ON un.Id = pa.Unit_Id
			WHERE pp.Assembly_Id = ja.Id
			FOR XML PATH('Part'), TYPE) ,
			CASE WHEN JobAssembly_Id=@JobAssemblyId THEN dbo.GetSubAssemblyTree(ChildJobAssembly_id) END
   FROM tblJobAssemblyRelation jar
	LEFT JOIN tblJobAssembly ja ON ja.Id = jar.ChildJobAssembly_Id
	LEFT JOIN tblJobAssemblyType jat ON jat.Id = ja.JobAssemblyType_Id
   WHERE JobAssembly_Id=@JobAssemblyId
   FOR XML PATH('Link'), TYPE)
END