Having problem fetching data that is in XML file which is store in XML type column in SQL server.
The sample of the XML is : document1.txt
I tried to query it using the below sql:
SELECT a.STD_ID,
a.STD_NAME, a.xml_document
,Strand.value('(./Strand/@LevelCode)[1]','varchar(50)') AS STRAND_LEVEL_CD
,Strand.value('(./Strand/Name)[1]','varchar(50)') AS STRAND_Name
FROM test A
CROSS APPLY A.XML_document.nodes('/Framework') P(Strand)
I got an error where it complains about
Cannot atomize/apply data() on expression that contains type 'Name' within inferred type 'element(Name,#anonymous) ?'
Can some one help me in fixing this query?