If you want a specific section, use the path that gives you that specific section.


SELECT Title = Title.c.value('@Value', 'nVarChar(max)')
FROM Appraisals
CROSS APPLY aAppraisal.nodes('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title') Title(c)

Also, does this really need to be NVARCHAR(MAX)?

Drew