Link to home
Start Free TrialLog in
Avatar of maverick0728
maverick0728Flag for United States of America

asked on

Import XML File into MS SQL table

I have a SQL table with the fieldnames the same as the node names in the attached xml file.

I need to write  a SQL script to import this XML file into the table:  TimeSch

I have tried the following code, but it doesn't finish executing the query.timesch1.xml  Note:  I was just testing with the first 3 fields in the XML file.

Any idea how to get this working?

insert into TimeSch (PositionID, LastName, FirstName)
select
   c3.value('PositionID[1]','varchar(255)'),
   c3.value('LastName[1]','varchar(255)'),
   c3.value('FirstName[1]','varchar(255)')
from
(
   select 
      cast(c1 as xml)
   from 
      OPENROWSET (BULK 'C:\TimeSch1.xml',SINGLE_BLOB) as T1(c1)
)as T2(c2)
cross apply c2.nodes('/ROWSET/ROW') T3(c3)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of zc2
zc2
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of maverick0728

ASKER

Thank you.  Works perfect!
You are welcome.