Import xml files (3000) into SQL Server using C#

Question

Possible Duplicate:
Import XML to SQL using C#

I just downloaded about 3000 XML files from soap web service and I want to import them into SQL Server. Here is an xml:

<my:SAP_kis_bil>
  <my:sap_code>2223M2F024153</my:sap_code>
  <my:sap_education>11</my:sap_education>
  <my:sap_kron>no</my:sap_kron>
  <my:sap_miltpos>soldier</my:sap_miltpos>
  <my:sap_miltgroup>2</my:sap_miltgroup>
  <my:sap_miltcat>CA</my:sap_miltcat>
  <my:sap_milttermdate>1970-11-20</my:sap_milttermdate>
  <my:sap_miltstartdate>1979-11-20</my:sap_miltstartdate>
  <my:sap_birthdate>1900-09-20</my:sap_birthdate>
  <my:sap_miltno>HZ 2931559</my:sap_miltno>
</my:SAP_kis_bil>

<my:LG_kis_bil>
  <my:lg_code>2223M2F024153</my:lg_code>
  <my:lg_name>John</my:lg_name>
  <my:lg_birthdate>1900-09-20</my:lg_birthdate>
  <my:lg_miltstatus>TECl</my:lg_miltstatus>
  <my:lg_drivingclass></my:lg_drivingclass>
  <my:lg_mobile1>+82316721233</my:lg_mobile1>
</my:LG_kis_bil>

About 3000 xml files like this one. So we have "lg_" and "sap_" and some of them should be sorted in SQL Server as multirow. I already have several sql tables like this:

CREATE TABLE [dbo].[TBL_SAP_XARICI](
    [code] [nvarchar](50) NULL,
    [orgname_x] [nvarchar](100) NULL,
    [orgdiv_x] [nvarchar](100) NULL,
    [orgplace_x] [nvarchar](100) NULL,
    [orgpos_x] [nvarchar](100) NULL,
    [orgstartdate_x] [date] NULL,
    [orgenddate_x] [date] NULL,
    [orgendreason_x] [nvarchar](100) NULL,
    [orgcountry_x] [nvarchar](50) NULL
) ON [PRIMARY]

Now I need to realize (import, transfer) this in C# console application. But this is my first experience. Please help to realize application in C# or just "show the way". Thank you.

So you want to use C# to parse the XML and then load to SQL Server? You have LOTS of options here. You need to add some specifics, this is really open-ended — Didaxis, Feb 19 '12 at 19:26
Typing your subject line into the search box turned up 57 similar questions. How does your question differ from them? — kdgregory, Feb 19 '12 at 19:28
Yes, I want parse and load to SQL. Which specifics should I add? — Delphi, Feb 19 '12 at 19:29

score 1 · Accepted Answer · answered Feb 19 '12 at 19:31

Here are the steps (roughly).

Add a web reference to your C# project from Visual Studio (right click web references, add). This will generate all proxy classes to deserialize your xml files.
Deserialize your XML files into objects (see http://msdn.microsoft.com/en-us/library/he66c7f1.aspx)
Persist your objects into the DB (see http://msdn.microsoft.com/en-us/library/ms233812(v=vs.80).aspx)

Note that step 1 will also create classes to consume the web service, but you don't really have to use them if you have the output from the SOAP calls already in files.

To deserialize 3000 xml file into object will I use any loop? — Delphi, Feb 19 '12 at 19:35

Import xml files (3000) into SQL Server using C#

1 Answers1