Geek asks and answers

Import data from an XML file into an SQL database

Is it possible to import data from an XML file into an SQL database, and if so, how to do it. I have an XML file containing about 50,000 records, and I have to create an application that will manipulate this data (mainly reading and comparing), so I am concerned that this data volume is manipulated (and there is a very likely possibility that in the future there will be even more) will be very slow and inefficient. If there is another option that you think would be better, consult. Thanks

+5
source share
6 answers

You can use the SQL Server Import and Export Wizard . You can also look at SQL Server Integration Services . If you want to use C #, then the SQL server supports the XML data type. You can use it.

You can also try reading the data in the dataset and then use BulkInsert to insert the data into 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 the Import and Export SQL Server 2005 Wizard

+4
source

, Snaplogic, "", , XML , , . Snaplogic.

+1

:

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>

.

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)

, , sql.

+1

xml ( , ..).

SSIS , , # .

:

  • agnostig persistance, .
  • xml (
  • drag and drop data into dabase using ado or linq in sql (maybe Entity infrastructure, but maybe too big for importing packages easily

An alternative way could also use a simple powershell script that does the same job.

0
source

More articles:


All Articles