DECLARE @XMLData XML
SET @XMLData ='
<STUDENTS>
<STUDENT>
<StudentID>1</StudentID>
<Name>John Smith</Name>
<Marks>200</Marks>
</STUDENT>
<STUDENT>
<StudentID>2</StudentID>
<Name>Mark Johnson</Name>
<Marks>300</Marks>
</STUDENT>
<STUDENT>
<StudentID>3</StudentID>
<Name>Nitin Tyagi</Name>
<Marks>400</Marks>
</STUDENT>
</STUDENTS>'
SELECT StudentID = Node.Data.value('(StudentID)[1]', 'INT')
, [Name] = Node.Data.value('(Name)[1]', 'VARCHAR(MAX)')
, [Marks] = Node.Data.value('(Marks)[1]', 'INT')
FROM @XMLData.nodes('/STUDENTS/STUDENT') Node(Data)
No comments:
Post a Comment