Working With Xml DataTypes

DECLARE @StudentDetails XML='<StudentsDetails><StudentHeader Code="lt8990" JoiningDate="12 Nov 2014" OpeningDate="12 Nov 2014" ExpiryDate="12 Nov 2014" RollNumber="ref333">

</StudentHeader></StudentsDetails>'

DECLARE @StudentDetail AS TABLE 

(

Code NVARCHAR(200), 

JoiningDate DATETIME, 

OpeningDate DATETIME, 

ExpiryDate DATETIME, 

RollNumber NVARCHAR(300)

)

INSERT INTO @StudentDetail

(

Code, 

JoiningDate, 

OpeningDate, 

ExpiryDate, 

RollNumber

)

SELECT

Students.value('(@Code)', 'NVARCHAR(200)'),

Students.value('(@JoiningDate)', 'DateTime'),

Students.value('(@OpeningDate)', 'DateTime'),

Students.value('(@ExpiryDate)', 'DateTime'),

Students.value('(@RollNumber)', 'NVARCHAR(200)')

FROM @StudentDetails.nodes('/StudentsDetails/StudentHeader') 

AS Data(Students)

SELECT * FROM @StudentDetail

2 

DECLARE @StudentDetails XML='<StudentsDetails>

       <StudentHeader>lt8990</StudentHeader>

       <JoiningDate>12 Nov 2014</JoiningDate>

       <OpeningDate>12 Nov 2014</OpeningDate>

   </StudentsDetails>'

   DECLARE @StudentDetail AS TABLE 

(

StudentHeader NVARCHAR(200), 

JoiningDate DATETIME, 

OpeningDate DATETIME

)

INSERT INTO @StudentDetail

(

StudentHeader, 

JoiningDate, 

OpeningDate

)

SELECT

Students.value('(StudentHeader[1])', 'NVARCHAR(200)'),

Students.value('(JoiningDate[1])', 'DateTime'),

Students.value('(OpeningDate[1])', 'DateTime')

FROM @StudentDetails.nodes('/StudentsDetails') AS Data(Students)

SELECT * FROM @StudentDetail