Jeff, once again you were exactly correct. Not that I doubted you, but I had to see for myself. I had a terrible time understanding what the procedure was doing, since the BOL description does not have an example. Anyhow, Andy, I think this will work for you. At least it worked for me with the sample .xls you gave. Its simply a very small adaptation of the BOL example suited for your data. It is assuming you dump your BOM file into a table called BOM. Sorry for the lack of comments, but I do not really understand it myself, but only fooled with it until it produced the desired results.

ALTER PROCEDURE expand (@current char(20)) as

SET NOCOUNT ON

DECLARE @level int, @line char(20)

CREATE TABLE #stack (item char(20), level int)

CREATE TABLE #BOM (Field1 varchar(200)) --Added this to hold @current

INSERT INTO #stack VALUES (@current, 1)

SELECT @level = 1

WHILE @level > 0

BEGIN

IF EXISTS (SELECT * FROM #stack WHERE level = @level)

BEGIN

SELECT @current = item

FROM #stack

WHERE level = @level

SELECT @line = space(@level - 1) + @current

--PRINT @line + 'line' --dashed this out, did not want to see it

INSERT INTO #BOM SELECT @Current

DELETE FROM #stack

WHERE level = @level

AND item = @current

INSERT #stack

SELECT Field3,@level + 1

FROM BOM

WHERE Field1 = @current

AND SUBSTRING(Field6,1,3) = 'S/A'

AND Field2 IN ('M','P')

IF @@ROWCOUNT > 0

SELECT @level = @level + 1

END

ELSE

SELECT @level = @level - 1

END -- WHILE

SELECT

b1.Field1,

b2.Field2,

b2.Field3,

b2.Field5,

b2.Field6

FROM #BOM b1,

BOM b2

WHERE b1.field1 = b2.field1

AND SUBSTRING(b2.field6,1,10) = 'S/A OF PCB'

Then try EXEC expand 'TX5922.AA'

Greg