Friday, January 24, 2014

Xml to SqlServer

In this example we have a xml file like this
http://www.aspnetcodes.com/2014/01/xml-to-sqlserver.html
and a table is created in a database as below
http://www.aspnetcodes.com/2014/01/xml-to-sqlserver.html
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

No comments:

Post a Comment