• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

XML Import into SQL multiple levels towards different tables

Experts,

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

- <Contact>
  <Id>0FzcJ./1sjO/</Id>
  <Type>UN</Type>
  <PersonId>$00010200025</PersonId>
  <OrganisationId>$00010155648</OrganisationId>
  <EmployeeId>3827</EmployeeId>
  <Date>20050901</Date>
  <Time>100000</Time>
- <Products>
- <Product>
  <Id>TIK</Id>
  <Position>1</Position>
  </Product>
  </Products>
- <Samples>
- <Sample>
  <Id>52</Id>
  <Quantity>1</Quantity>
  </Sample>
- <Sample>
  <Id>52</Id>
  <Quantity>0</Quantity>
  </Sample>
  </Samples>
  <Deleted>N</Deleted>
  </Contact>

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
0
ITBenelux
Asked:
ITBenelux
  • 8
  • 7
1 Solution
 
Anthony PerkinsCommented:
Add this after the INSERT Contact:

--Insert  contact_prod(ContactId, ProductId, Position)     -- Uncomment this line when you have verified column names, data types, results and ready to Insert
Select      ContactId, ProductId, Position
From      OpenXml(@idoc, 'xMRK/Contacts/Contact/Products/Product', 2) With (
      ContactId char(25) '../../Id',
      ProductId varchar(25) 'Id',
      Position integer 'Position') x
      Left Join Contact e On x.Id = e.Id
Where      e.Id Is Null

-- Insert  contact_samples(ContactId, SampleId, Quantity)    -- Uncomment this line when you have verified column names, data types, results and ready to Insert
Select      ContactId, SampleId, Quantity
From      OpenXml(@idoc, 'xMRK/Contacts/Contact/Samples/Sample', 2) With (
      ContactId char(25) '../../Id',
      SampleId varchar(25) 'Id',
      Quantity integer 'Quantity') x
      Left Join Contact e On x.Id = e.Id
Where      e.Id Is Null
0
 
ITBeneluxAuthor Commented:
I got the following error:

Server: Msg 207, Level 16, State 3, Procedure usp_AddContact, Line 26
Invalid column name 'Id'.
Server: Msg 207, Level 16, State 1, Procedure usp_AddContact, Line 35
Invalid column name 'Id'.

on those lines:

  ContactId char(25) '../../Id',
  ContactId char(25) '../../Id',

0
 
Anthony PerkinsCommented:
>>I got the following error:<<
Of course you do.  How do you expect me to know your table structure for your tables contact_prod and contact_samples?  Did you not see my comment: "Uncomment this line when you have verified column names, data types, results and ready to Insert"

If you want the code written for you, than make sure to post all the details, including your table structure.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ITBeneluxAuthor Commented:
I should paste the lines after I changed them:

     ContactId char(25) 'Contacts/Contact/Id',
     SampleId varchar(25) 'Id',

between '' is the level within the XML file I assume, so when adding this, It result in the same error.  This is my structure of the contac_sample table

3      PrimaryKey      int      4      0
0      ContactId      char      25      1
0      SampleId      varchar      25      1
0      Quantity      int      4      1

So ContactId is existing and so is the XML level Contacts/Contact/Id,  I just trying to figure out which value the error refers to.
0
 
Anthony PerkinsCommented:
Is PrimaryKey an IDENTITY column?  Also post the struncture of your contact_prod table, so that I can write the exact code for you.
0
 
ITBeneluxAuthor Commented:
Yes it is.  This is the structure of Contact_Product:

3      PrimaryKey      int      4      0      
0      ContactId      char      25      1      
0      ProductId      char      25      1      
0      Position      int      4      1      

Also here is the PrimaryKey and an Auto nr. field.
0
 
Anthony PerkinsCommented:
First some minor corrections this line:
     Left Join Contact e On x.Id = e.Id

should read:
     Left Join Contact e On x.ContactId = e.Id

There is no Id in the OPENXML but rather ContactId

Secondly there you have called the tables first "contact_prod" and "contact_samples" but lately I see you are using "contac_sample" and "Contact_Product".  I have chosen to use the orginal names.
0
 
Anthony PerkinsCommented:
Here is the tested code:

-- Uncomment the following line when you have verified column names, data types, results and ready to Insert
-- Insert      contact_prod(ContactId, ProductId, Position)
Select      ContactId, ProductId, Position
From      OpenXml(@idoc, 'xMRKBEL_SGPBEL/Contacts/Contact/Products/Product', 2) With (
      ContactId char(25) '../../Id',
      ProductId varchar(25) 'Id',
      Position integer 'Position') x
      Left Join Contact e On x.ContactId = e.Id
Where      e.Id Is Null

-- Uncomment the following line when you have verified column names, data types, results and ready to Insert
--  Insert      contact_samples(ContactId, SampleId, Quantity)
Select      ContactId, SampleId, Quantity
From      OpenXml(@idoc, 'xMRKBEL_SGPBEL/Contacts/Contact/Samples/Sample', 2) With (
      ContactId char(25) '../../Id',
      SampleId varchar(25) 'Id',
      Quantity integer 'Quantity') x
      Left Join Contact e On x.ContactId = e.Id
Where      e.Id Is Null

EXEC sp_xml_removedocument @iDoc

Once again I urge you to test first.  When satisfied remove the commented INSERT statements.
0
 
ITBeneluxAuthor Commented:
Code is tested and has no errors.  So thanks for your effort on this one.  I am almost there, I'm just getting my input 3 times instead of one time.
so in my XML there is one contact an one underlying product, but I get 3 products instead of 1. so I try to figure this out.

Should you have any ideas, it would save some time.

Thx again !
0
 
Anthony PerkinsCommented:
>>Should you have any ideas, it would save some time.<<
Is it a problem with the Xml ?  If so, can it be changed?  If it cannot be changed, post the Xml and we may be able to change the query appropriately.
If it is not a porblem with the Xml, post the Xml document the result and the expected result.
0
 
ITBeneluxAuthor Commented:
I have still no idea where the problem is, but this is what happens:

in my XML part I have 2 samples for this contact (52 - 1 and 52 - 0)

 - <Contact>
  <Id>0FzcJ./1sjO/</Id>
  <Type>UN</Type>
  <PersonId>$00010200025</PersonId>
  <OrganisationId>$00010155648</OrganisationId>
  <EmployeeId>3827</EmployeeId>
  <Date>20050901</Date>
  <Time>100000</Time>
- <Products>
- <Product>
  <Id>EZE</Id>
  <Position>1</Position>
  </Product>
  </Products>
- <Samples>
- <Sample>
  <Id>52</Id>
  <Quantity>1</Quantity>
  </Sample>
- <Sample>
  <Id>52</Id>
  <Quantity>0</Quantity>
  </Sample>
  </Samples>
  <Deleted>N</Deleted>
  </Contact>

And this is the result in my table. the samples are there but 4 times the same

1      0FzcJ./1sjO/                   52      1
2      0FzcJ./1sjO/                   52      0
3      0FzcJ./1sjO/                   52      1
4      0FzcJ./1sjO/                   52      0
5      0FzcJ./1sjO/                   52      1
6      0FzcJ./1sjO/                   52      0
7      0FzcJ./1sjO/                   52      1
8      0FzcJ./1sjO/                   52      0
9      09t/l./ZOVl/                   53      0


0
 
Anthony PerkinsCommented:
I have no idea what is happening.  I just retested and I did not get that result.  I can only assume you have not emptied your table and are executing over and over again.
0
 
ITBeneluxAuthor Commented:
I tried to do some more testing during the weekend-  If I disable these lines

     Left Join Contact e On x.ContactId = e.Id
Where     e.Id Is Null

then the table is getting is values double (logical), but if these lines are active, then there is no information in the table.
0
 
Anthony PerkinsCommented:
>>but if these lines are active, then there is no information in the table.<<
Let me explain to you what these lines do:
If the Sample/Id or Product /Id already exists in Contact than do NOT add to the Sample and Product tables.
0
 
ITBeneluxAuthor Commented:
I see.. indeed I checked the XML data, and it is logical.  So problem solved.
Let me thank you again for all your help.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now