Reading from XML data from SQL Server reduces performance

Question

I am having a SQL view of the below script. As the table stores the data as XML (dbo.TEST.configuration), I have to select each column by reading the xml data. But when the record increases, it is getting too slow to load. Is there any method to improve its performance, please let me know?

WITH XMLNAMESPACES( DEFAULT 'http://tempuri.org/xmlConfiguration.xsd')

SELECT      dbo.TEST.uid, dbo.TEST.name, dbo.TEST.ClassUid, dbo.TEST.xmlData, 
                  CAST(REPLACE(CAST(dbo.TEST.configuration AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/batchUid)[1]', 'NVARCHAR(200)') AS batchUid, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/statusUid)[1]', 'NVARCHAR(200)') AS statusUid, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/subjectUid)[1]', 'NVARCHAR(200)') AS subjectUid, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/cultureUid)[1]', 'NVARCHAR(200)') AS cultureUid, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/format)[1]', 'NVARCHAR(200)') AS format, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/secondFormat)[1]', 'NVARCHAR(200)') AS secondFormat, 
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/standardUid)[1]', 'NVARCHAR(200)') AS standardUid
FROM         dbo.TEST INNER JOIN
                  dbo.batch ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/batchUid)[1]', 'NVARCHAR(200)') = dbo.batch.uid INNER JOIN
                  dbo.status ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/statusUid)[1]', 'NVARCHAR(200)') = dbo.status.uid INNER JOIN
                  dbo.vFormat ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/format)[1]', 'NVARCHAR(200)') = CONVERT(NVARCHAR(200), dbo.vFormat.uid) OR
                  CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/secondFormat)[1]', 'NVARCHAR(200)') = CONVERT(NVARCHAR(200), dbo.vFormat.uid) INNER JOIN
                  dbo.standard ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/standardUid)[1]', 'NVARCHAR(200)') = dbo.standard.uid INNER JOIN
                  mainDb.dbo.Class ON dbo.TEST.ClassUid = mainDb.dbo.Class.uid INNER JOIN
                  dbo.subject ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/subjectUid)[1]', 'NVARCHAR(200)') = dbo.subject.uid INNER JOIN
                  mainDb.dbo.culture ON CAST(REPLACE(CAST(dbo.TEST.xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') 
                  AS XML ).value('(/xmlConfiguration/cultureUid)[1]', 'NVARCHAR(200)') = mainDb.dbo.culture.uid

Thanks in advance.

Sample xml data is below;

 <?xml version="1.0" encoding="utf-8"?> 
 <xmlConfiguration xmlns="http://tempuri.org/xmlConfiguration.xsd">              
 <secondFormat>3bd3d9cc-ad0a-e611-b086-00e04c6804ad</secondFormat>               
 <batchUid>c7b4743b-4493-df11-981e-00221933d118</batchUid>               
 <statusUid>f0b159ec-4193-df11-981e-00221933d118</statusUid>               
 <subjectUid>d07b5d66-3b5b-de11-b569-001143e78e41</subjectUid>               
 <cultureUid>c6644752-93d7-df11-981e-00221933d118</cultureUid>               
 <name>test</name>               
 <standardUid>dc19869b-3ea9-df11-981e-00221933d118</standardUid>               
 <format></format>            
</ConnectorConfiguration>

Answer 1

;WITH XMLNAMESPACES( DEFAULT 'http://tempuri.org/xmlConfiguration.xsd')
SELECT
    x.uid,
    x.name,
    x.ClassUid,
    x.xmlData, 
    x.batchUid, 
    x.statusUid, 
    x.subjectUid, 
    x.cultureUid, 
    x.format, 
    x.secondFormat, 
    x.standardUid
FROM (
    SELECT *,
        x.value('(/xmlConfiguration/batchUid)[1]', 'NVARCHAR(200)') AS batchUid, 
        x.value('(/xmlConfiguration/statusUid)[1]', 'NVARCHAR(200)') AS statusUid, 
        x.value('(/xmlConfiguration/subjectUid)[1]', 'NVARCHAR(200)') AS subjectUid, 
        x.value('(/xmlConfiguration/cultureUid)[1]', 'NVARCHAR(200)') AS cultureUid, 
        x.value('(/xmlConfiguration/format)[1]', 'NVARCHAR(200)') AS format, 
        x.value('(/xmlConfiguration/secondFormat)[1]', 'NVARCHAR(200)') AS secondFormat, 
        x.value('(/xmlConfiguration/standardUid)[1]', 'NVARCHAR(200)') AS standardUid
    FROM (
        SELECT *, x = CAST(
                CAST(REPLACE(CAST(xmlData AS VARCHAR(MAX)), 'encoding="utf-8"', '') AS XML
            ).query('.') ---- .query('.')
        FROM dbo.TEST
    ) T
) x
JOIN dbo.batch ON x.batchUid = dbo.batch.uid
JOIN dbo.status ON x.statusUid = dbo.status.uid
JOIN dbo.vFormat ON CONVERT(NVARCHAR(200), dbo.vFormat.uid) IN (x.format, x.secondFormat)
JOIN dbo.standard ON x.standardUid = dbo.standard.uid
JOIN mainDb.dbo.Class ON dbo.TEST.ClassUid = mainDb.dbo.Class.uid
JOIN dbo.subject ON x.subjectUid = dbo.subject.uid
JOIN mainDb.dbo.culture ON x.cultureUid = mainDb.dbo.culture.uid