5

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

NDraskovic
  • 706
  • 2
  • 22
  • 50
  • Why don't you do the manipulation in database . Using Linq to XML u can read the XML file and then store it a collection and send it as an xml input to the Stored procedure where you do all the manipulation . – praveen May 29 '12 at 08:49
  • You can use an XPathNavigator object to parse the XML, and then use an SqlCommand object to insert the data into the tables. There are lots of code samples for both objects on the Internet. – eMi May 29 '12 at 08:50
  • If you are using any MS BI tool then u can probably think of using SSIS to load the data from XML ,do the manipulation and then dump the data in sql server . – praveen May 29 '12 at 08:51

6 Answers6

5

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

Habib
  • 219,104
  • 29
  • 407
  • 436
  • Yes, I am using C#. Which SQL server do I need? I have the SQL Server 2005 installed on my computer and I cannot find the tools that are listed in the link you gave me (Import and export wizard). I only have the configuration tools. – NDraskovic May 29 '12 at 09:02
  • @NDraskovic Check out this link http://msdn.microsoft.com/en-us/sqlserver/bb671393.aspx, Also see if you could find DTS or Data Transformation services – Habib May 29 '12 at 09:23
  • @NDraskovic, see http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm I have also updated my answer – Habib May 29 '12 at 09:28
  • Ok, I'll see what I can do. Thanks for your answer – NDraskovic May 29 '12 at 09:31
2

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.

Remco
  • 1,713
  • 1
  • 13
  • 14
2

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.

xxfelixxx
  • 6,512
  • 3
  • 31
  • 38
RAVI KAIPA
  • 21
  • 1
1

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

Arnab Dey
  • 11
  • 2
1

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.

Juan M. Elosegui
  • 6,471
  • 5
  • 35
  • 48
0

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.

Steve B
  • 36,818
  • 21
  • 101
  • 174