XML Import into SQL multiple levels towards different tables
Posted on 2006-06-12
I am importing (multiple) XML files into my SQL Database, so far I got a lot of help from here to get it working, but I encountered a new issue and I can't solve it:
This is a part of my XML
per contact there are multiple products and/or samples, so I need to change my Insert command in such a way that the products and samples are send to the table contact_prod and contact_samples.
But the Id of the contact has to be set in each line, so I can create queries later.
This is my current insert command, hope someone can help me changing the insert.
Insert Contact(Id, EmployeeId, OrganisationId, PersonId,Date, Time, Type, Deleted)
Select x.Id, x.EmployeeId, x.OrganisationId, x.PersonId, x.Date, x.Time, x.Type, x.Deleted
From OpenXml(@idoc, 'xMRK/Contacts/Contact', 2) With (
Id char(25) 'Id',
EmployeeId varchar(25) 'EmployeeId',
OrganisationId char(25) 'OrganisationId',
PersonId char(25) 'PersonId',
Date smalldatetime 'Date',
Time char(20) 'Time',
Type char(10) 'Type',
Deleted char(3) 'Deleted') x
Left Join Contact e On x.Id = e.Id
Where e.Id Is Null
Thanks in advance