Thursday, 23 May 2013

import data from xml file to sql server table

Sample xml file- 1001 Sagar 2011-05-05T09:25:48.253
c40 noida
1002 himalaya 2011-05-05T09:25:48.253
c6. noida
The table needs to be created having column names same as tags name of xml file Here as per example xml file- create TABLE XMLTABLE (CustID INT, CustLastName VARCHAR(10) , DOB DATETIME, Addr1 VARCHAR(100), City VARCHAR(10)) then run the following query to import data- insert into XMLTABLE select c3.value('CustID[1]','int'), c3.value('CustLastName[1]','varchar(10)'), c3.value('DOB[1]','DATETIME'), c3.value('(Address/Addr1)[1]','VARCHAR(100)'), c3.value('(Address/City)[1]','VARCHAR(10)') from ( select cast(c1 as xml) from OPENROWSET (BULK 'D:\sample.txt',SINGLE_BLOB) as T1(c1) –the path is to be changed to where the xml file is placed )as T2(c2) cross apply c2.nodes('/CustomerDetails/PersonalInfo') T3(c3) –these tags are the most exterior tags, that enclose the data in xml file

No comments:

Post a Comment