DECLARE @X TABLE ( XXML XML )
INSERT @X
SELECT ‘<STUDENTINFO>
<student ID="1" name="Karthi">
<subject ID="1" Name="Electronics and Communication" />
<subject ID="2" Name="Circuit Analysis" />
<subject ID="3" Name="Mobile Communication" />
</student>
<student ID="2" name="Vikram">
<subject ID="4" Name="Data Structure" />
<subject Name="NULL" />
<subject ID="6" Name="Database Management System" />
</student>
</STUDENTINFO>‘
CREATE TABLE Subject_Master
(
ID INT NULL,
NAME NVARCHAR(30)
)
INSERT INTO Subject_Master
SELECT ISNULL(tab.col.value(‘@ID[1]‘, ‘INT‘),
NULL) SubjectID ,
ISNULL(tab.col.value(‘@Name[1]‘, ‘NVARCHAR(30)‘),
‘Attribute Is Missing‘) SubjectName
FROM @X
CROSS APPLY XXML.nodes(‘/STUDENTINFO/student/subject‘) tab ( col )
SELECT *
FROM Subject_Master
--DROP TABLE subject_master