Fetch XML root

1

Im trying to read elements in a root of an XML file sored in a CRM database. I have cut the xml file down for security reasons

code
Declare @mosaicreportid as varchar(max) = 'BDDC8B2B-6F7A-EA11-9164-0050560105A1'

SELECT a.c.value('@status[1]', 'VARCHAR(10)') AS 'rstatus',
a.c.value('@submissionIdentifier[1]', 'VARCHAR(100)') AS 'submissionIdentifier'
FROM (SELECT CAST(REPLACE(CAST([new_submissionresult] AS VARCHAR(MAX)), 'encoding="utf-16"', '') AS XML) AS 'Xmlreport'
FROM [XXX_MSCRM].[dbo].[new_XXXXX]
WHERE CAST(new_bacssubmissionid AS VARCHAR(100)) = @mosaicreportid) d
CROSS APPLY Xmlreport.nodes('/SubmissionResults') AS a(c)

Had to replace the < and > with " to post

"SubmissionResults status="complete" submissionIdentifier="15864428077959YRfW_g89" "
"/SubmissionResults"

2

Sorted .. changed to CROSS APPLY Xmlreport.nodes('/*') AS a(c)

3

not sure what your question is, but if you have one, then can you please post DDL and SampleData?