Xml to SqlServer

In this example we have a xml file like this

and a table is created in a database as below

Query to import xm file into SQL Server,for this

INSERT INTO xx(rno,name)
SELECT X.a.query(‘rno’).value(‘.’, ‘numeric(18,0)’),
X.a.query(‘name’).value(‘.’, ‘VARCHAR(50)’)
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK ‘C:\mssql\xml_test.xml’,
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes(‘records/record’) AS X(a);

Importing XML file into SQL Server implementing SSIS