save data from xml into database?

i have an xml file, it looks like this:

<?xml version="1.0" encoding="ISO-8859-1"?>
<shiporder orderid="889923" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>My Title</title>
<quantity>1</quantity>
<price>10.12</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>

how can i save this into a database(Sql) using a procedure

or..there is another way to do this?

im a newbie

There are couple of ways to do this, Load this XML into a dataset and use SQLBulkCopy feature to save it to the database or pass the whole XML to the Stored procedure and use XML feature of SQL server 2005 [look into the documentation ] and save it to the database.

|||

ok..i have this

DataSet dataset = new DataSet();

dataset.ReadXML("d:\\shiporder.xml",XMLReadMode.Auto)

SqlBulkCopy d = new SqlBulkCopy

what i need to do further?

it`s necesarry to create a destination database?

|||

i have something like this

DataSet dataset = new DataSet();

dataset.ReadXML("d:\\shiporder.xml",XMLReadMode.Auto);

SqlBulkCopy s = new SqlBulkCopy;

what i have to do nextt?

it`s necesarry to create a destination table?

please help:D

|||

DataTable dataTable = mydataset.Tables[0];

bulkCopy.DestinationTableName = yourtableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn myCol in dataTable.Columns)
bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);
bulkCopy.WriteToServer(dataTable);

|||DataSet mydataset =newDataSet();

mydataset.ReadXml("d:\\shiporder.xml",XmlReadMode.Auto);

string cs ="DataSource=.\\SQLExpress; Integrated Security = True; Initial Catalog = Shiporder";

SqlConnection dest =newSqlConnection(cs);

DataTable dataTable = mydataset.Tables[0];

SqlBulkCopy bulkCopy =newSqlBulkCopy(dest);

bulkCopy.DestinationTableName ="dbo.ShipOrder";

bulkCopy.ColumnMappings.Clear();

foreach (DataColumn myColin dataTable.Columns)

bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);

bulkCopy.WriteToServer(dataTable);

it gives me this error:

WriteToSever requires an open and avaible Connection. The connection`s current state is closed.

how should i fixed this?
what`s wrong?

|||

Looks like you are not opening the connection.

DataSet mydataset =newDataSet();

mydataset.ReadXml("d:\\shiporder.xml",XmlReadMode.Auto);

string cs ="DataSource=.\\SQLExpress; Integrated Security = True; Initial Catalog = Shiporder";

SqlConnection dest =newSqlConnection(cs);

DataTable dataTable = mydataset.Tables[0];

SqlBulkCopy bulkCopy =newSqlBulkCopy(dest);

dest.Open();

bulkCopy.DestinationTableName ="dbo.ShipOrder";

bulkCopy.ColumnMappings.Clear();

foreach (DataColumn myColin dataTable.Columns)

bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);

bulkCopy.WriteToServer(dataTable);