Read XMl file to SQL table

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  May 12, 2011 at 2:34 pm

  #236104

  I am trying to import xml file to sql table. Any help please! m new with XML, obviously!

  Sample XML file

  <CustomerDetails>

  <PersonalInfo>

  <CustID>1001</CustID>

  <CustLastName>Smith</CustLastName>

  <DOB>2011-05-05T09:25:48.253</DOB>

  <Address>

  <Addr1>100 Smith St.</Addr1>

  <City>New York</City>

  </Address>

  </PersonalInfo>

  </CustomerDetails>

  And the result table should look like

  CREATE TABLE #Cust

  (CustID INT, CustLastName VARCHAR(10)

  , DOB DATETIME, Addr1 VARCHAR(100), City VARCHAR(10))

  INSERT INTO #Cust

  VALUES (1001, 'Smith', '2011-05-05', '100 Smith St.', 'New York')

• SunitaBeck

  SSC Veteran

  Points: 277

  More actions

  May 12, 2011 at 5:47 pm

  #1324037

  Replace the C:\test.txt with your filename in the following.

  insert into #Cust

  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 'C:\test.txt',SINGLE_BLOB) as T1(c1)

  )as T2(c2)

  cross apply c2.nodes('/CustomerDetails/PersonalInfo') T3(c3)

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  May 13, 2011 at 8:50 am

  #1324351

  thanks much!

• sairam_2007

  SSC Journeyman

  Points: 87

  More actions

  January 12, 2012 at 1:20 am

  #1432282

  Hi Can u plz explain the code i cant get

• brendan woulfe

  Hall of Fame

  Points: 3888

  More actions

  January 12, 2012 at 7:37 am

  #1432510

  See if one of these links helps you.

  http://msdn.microsoft.com/en-us/library/ms191184.aspx

  http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

• markntejas

  SSC Journeyman

  Points: 80

  More actions

  May 25, 2016 at 11:26 am

  #1881402

  thx