Hi Drew,
Thanks for the answer.  Unfortunatley it does not solve my problem.
The <ProductsSummaryItems> section can be repeated multiple times with different values.  This gives me multiple rows with mixed data.  For example if I try to select the Titles and Units using this:

SELECT

SELECT Title Title == Title Title..cc..valuevalue(('@Value''@Value',, 'nVarChar(max)''nVarChar(max)'),),Units Units == Units Units..cc..valuevalue(('@Value''@Value',, 'nVarChar(max)''nVarChar(max)'),),

FROMFROM Appraisals AppraisalsCROSSCROSS APPLYAPPLY aAppraisal aAppraisal..nodesnodes(('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title''/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Title')) Title Title((cc))CROSSCROSS APPLYAPPLY aAppraisal aAppraisal..nodesnodes(('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Units''/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/CollectionItems/cUnitSummaryData/Units')) Units Units((cc))

wherewhereaID aID == '9D07E6EA-C8E0-421F-A71F-197CDAECEC3C''9D07E6EA-C8E0-421F-A71F-197CDAECEC3C'

I get this result:
Title Units
A: 2B4P House 1
A: 2B4P House 1
B: 2B4P House 1
B: 2B4P House 1

There should be just one row for each Title.

Also I need to associate the Product Type with the Title to get something like this:
Title                   Units  Product
A: 2B4P House 1         Affordable Rent
B: 2B4P House 1         Shared Ownership

I have tried by add another Cross Apply "

CROSS APPLY aAppraisal.nodes('/cBusinessAppraisal/ProductsSummaryItems/CollectionItems/cUnitSummaryData/Children/ProductTypeID') Prod(c)"
But that then returns no rows.