CODEWITHSUNDEEP
c#sqlxmldatabase
NDraskovic
NDraskovic

Reputation: 706

Importing data from XML file to SQL database

Is it possible to import data from XML file to a SQL database, and if yes, how would that be done. I have a XML file that contains about 50 000 entries and I have to make an application that would manipulate that data (mostly reading and comparing) - so my concern is that the manipulation with that amount of data (and there is a very likely possibility that in the future there will be even more) would be very slow and inefficient. If there is some other option that you think would be better, please advise. Thanks

Upvotes: 5

Views: 26917

Answers (6)

Juan M. Elosegui
Juan M. Elosegui

Reputation: 6981

Another option to consider is:

Given the following xml

<?xml version="1.0" standalone="yes" ?>
<Clients>
  <Client>
    <Id>1</Id>
    <FirstName>FirstName1</FirstName>
    <LastName>LastName1</LastName>
  </Client>
  <Client>
    <Id>2</Id>
    <FirstName>FirstName2</FirstName>
    <LastName>LastName2</LastName>
  </Client>
</Clients>

You could use the following query to select the content.

select 
    x.a.query('Id').value('.', 'int') as Id
    , x.a.query('FirstName').value('.', 'nvarchar(50)') as FirstName
    , x.a.query('LastName').value('.', 'nvarchar(50)') as LastName  
from 
( 
    select CAST(x AS XML) from OPENROWSET
    (BULK 'C:\Users\jmelosegui\Desktop\Clients.xml', SINGLE_BLOB) as T(x)
) T(x)
cross Apply x.nodes('Clients/Client') as x(a)

Once that is done you could join with other tables or do your match after insert the data, and all from sql server side.

Upvotes: 1

RAVI KAIPA
RAVI KAIPA

Reputation: 21

There are products such as Snaplogic that have "snaps" meaning connectors to read an xml file, parse it and then move it into a Database without needing to write any lines of code. Have a look at Snaplogic.

Upvotes: 2

Arnab Dey
Arnab Dey

Reputation: 11

In situation I got requirement like read data from xml file and send that xml file as parameter to store procedure Useful link http://www.aspdotnet-suresh.com/2012/12/aspnet-send-xml-file-as-parameter-to.html

Upvotes: 1

Habib
Habib

Reputation: 223392

You can use SQL Server Import and Export Wizard. You can also look in SQL Server Integration Services. If you want to use C# then SQL server does support XML data type. You can make use of that.

You can also try to read the data in data set and then use BulkInsert to insert data in SQL Server

DataSet reportData = new DataSet();
reportData.ReadXml(Server.MapPath("yourfile.xml"));
SqlConnection connection = new SqlConnection("DB ConnectionSTring");
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = "yourXMLTable";

EDIT: For SQL Server 2005 check SQL Server 2005 Import / Export Wizard

Upvotes: 5

Steve B
Steve B

Reputation: 37710

it greatly depends on the complexity of the xml files (one depth of objects versus nested objects, kind and size of data, etc.).

While SSIS can be a solution if you have knowledge, a simple c# application can also do the job.

The application should :

  1. define a persistance agnostig class representing your data
  2. read the xml to load a list of this class (pagination may be required on very heavy volumes
  3. push the data to the dabase using ado or linq to sql (Entity framework is possible but probably too much for a simple batch import

An alternative way could also to use a simple powershell script that performs the same kind of job.

Upvotes: 0

Remco
Remco

Reputation: 1933

Try looking at the SSIS tooling from microsoft Sql Server Intergration Services

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

http://www.microsoft.com/sqlserver/en/us/solutions-technologies/enterprise-information-management/integration.aspx

With this tooling you can create save and run import packages, with filtering and custom selections . Possibly you can use this as a total solution for your problem.

Upvotes: 2

Related Questions