Below is a simplified query that returns the records for a single engine (serializedpartid='14549') I'd like to modify it to return all engines in the Engines table, creating an engine node for each engine and single fleet parent node. Replacing 'serializedpartid = '14549' with 'serializedpartid = (Select serializedpartid from engines) returns all the parts but puts them all in a single engine node.
I realize I can make this a procedure and call it multiple times but was hoping for something more selfcontained. Thanks in advance for your help.

with PartTree (Serializedpartid,partnumber,sn) AS
(
select 
    serializedpartid,
    PartsMaster.PartNumber,
    sn
 from SerializedParts
    Inner Join PartsMaster on PartsMaster.PartId = SerializedParts.PartId   
    where SerializedPartId = '14549' 
UNION ALL
Select 
    serializedparts.serializedpartid,
    PartsMaster.PartNumber,
    SerializedParts.sn
    from SerializedParts Inner Join
PartTree on serializedparts.nhserializedpartid = PartTree.SerializedPartId
    Inner Join PartsMaster on PartsMaster.PartId = SerializedParts.PartId
)
SELECT (
SELECT  	partnumber as '@partnum',
    		sn AS '@sn' 
    FROM    PartTree AS Engine
    FOR XML PATH('Parts'),TYPE)AS Engine
For XML PATH(''),ROOT('Fleet')

Submitted under: Microsoft SQL Server · XML ·  ·  ·