I went through the functions and realized they are not needed as advanced as they are in this query.

"Fixed" it by using this:

SELECT

.

.

.

, ISNULL(CAST(NULLIF(PR.RetailPrice,0.00) AS VARCHAR(20)), 'N/A') RetailPrice

,

(

SELECT TOP 1

(LocalPrice * 1.08) LocalPrice

FROM LocalPriceList

WHERE RecordReference = T1.Key

ORDER BY LocalPriceList ASC

) LocalPrice

.

.

.

FROM

TABLE1 T1

INNER JOIN dbo.TABLE2 T2 ON T1.Key = T2.Key

LEFT JOIN RetailPriceList PR ON PR.Key = T1.Key

FOR XML AUTO, ROOT ('BS')

Guess its probably not the best, but performance wise its WAY better