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
3
-
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– praveen2012-05-29 08:49:53 +00:00Commented May 29, 2012 at 8: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– eMi2012-05-29 08:50:13 +00:00Commented May 29, 2012 at 8: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– praveen2012-05-29 08:51:12 +00:00Commented May 29, 2012 at 8:51
Add a comment
|
6 Answers
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
4 Comments
NDraskovic
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.
Habib
@NDraskovic Check out this link msdn.microsoft.com/en-us/sqlserver/bb671393.aspx, Also see if you could find DTS or Data Transformation services
Habib
@NDraskovic, see databasejournal.com/features/mssql/article.php/3580216/… I have also updated my answer
NDraskovic
Ok, I'll see what I can do. Thanks for your answer
Try looking at the SSIS tooling from microsoft Sql Server Intergration Services
http://msdn.microsoft.com/en-us/library/ms141026.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.
Comments
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.
Comments
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
Comments
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.
Comments
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 :
- define a persistance agnostig class representing your data
- read the xml to load a list of this class (pagination may be required on very heavy volumes
- 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.
