Properly pivot an XML column with XQuery
I'm probably missing the point of your question but here is what I would do to get the result you are looking for.
with xmlnamespaces('ModelDescriptive' as dsc)
select M.X.value('@ref', 'varchar(max)') as ref,
       M.X.value('@baseRef', 'varchar(max)') as baseRef,
       M.X.value('@type', 'varchar(max)') as type,
       M.X.value('@productionGroup', 'varchar(max)') as productionGroup
from @xmlDescriptive.nodes('//dsc:Material') as M(X);

Your use of local-name() and //*[text()], //@*' suggests you want some kind of generic pivot but you use Material and the attribute names hard coded in the query so I don't know why you went down that road.