Some times there will  be a necessity of import data from a xml file and import it  into SQL Server, this can be done in 2 ways,

1)  query

2) ssis

in this example we have a xml file like this

ssis_1

and a table is created in a database as below

ssis_2

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:mssqlxml_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

Leave a Reply

Your email address will not be published. Required fields are marked *