ansaurus

Question

Transferring data from XML to SQL

Answer 1

+1 A:

Question Is : How to append New Value to SQL Db....

Answer is: with how you did for first value...

Im not sure your question is clear btw.

Serkan Hekimoglu 2010-10-08 17:57:49

ya u r correct,,,but i set SNo (Pkey) In SqlDb Table;

kummsp 2010-10-08 18:01:38

First time its inserted..then second time it does not allow duplication,,,

kummsp 2010-10-08 18:02:33

i want to append only new data.

kummsp 2010-10-08 18:03:17

Whats is SNo? And what is first and second value of SNo?

Serkan Hekimoglu 2010-10-08 18:06:51

SQL doesnt allow insert, if your PK is not unique. So, be careful abour your PK SNo. It must have unique value. I suggest you to use AutoIncrement int, or uniqueIdentifier (Guid).

Serkan Hekimoglu 2010-10-08 18:12:52

No. In XML FILe i didnt Have SNo.....For My Usage I Create SNo Field in SQL Table. SNO(PKey),Patient(Varchar),Id(Int)..

kummsp 2010-10-08 18:32:13

THree fileds in SQl Table.I want to store Patient,Id in Sql Db from XML file.

kummsp 2010-10-08 18:33:30

If your SNo is unique, sql allows that insert. What is your SNo field type?

Serkan Hekimoglu 2010-10-08 18:33:44

I want to SNo filed to be automaticaly increase;;;when new data can be appended from xml file...

kummsp 2010-10-08 18:34:26

SNo(Int)(PKey).

kummsp 2010-10-08 18:35:51

So you must make your SNo field type INT, and set Auto Increment property TRUE.

Serkan Hekimoglu 2010-10-08 18:36:02

K i done ,,its increasing. automatically.

kummsp 2010-10-08 18:37:36

So SQL must allow insertion now.

Serkan Hekimoglu 2010-10-08 18:38:43

But I inserted Second time the Values from XML File repeated storing in Database...{ I want to Just Append New Value from XML File}

kummsp 2010-10-08 18:39:13

can you write your code pls.

Serkan Hekimoglu 2010-10-08 18:40:25

int sno = 1; XmlDocument xmlDoc = new XmlDocument(); //Load the Xml File xmlDoc.Load("D:\\sample.xml"); //Specify the nodes in Xml File. XmlNodeList dataNodes = xmlDoc.SelectNodes("/information/data");

kummsp 2010-10-08 18:49:07

foreach (XmlNode node in dataNodes) { int id = Convert.ToInt32(node.SelectSingleNode("id").InnerText); string name = node.SelectSingleNode("name").InnerText; int age = Convert.ToInt32(node.SelectSingleNode("age").InnerText);

kummsp 2010-10-08 18:49:42

connectionstringmethod(); SqlCommand cmd = new SqlCommand("INSERT INTO XmlToSql (SNo,Id,Name,Age) Values(@sno,@id,@name,@age)", connection); {cmd.Parameters.AddWithValue("@sno", sno); cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@age", age); cmd.ExecuteNonQuery();

kummsp 2010-10-08 18:50:22

}sno++; } .. Its Coding to Insert XMl Nodes into SQL

kummsp 2010-10-08 18:51:25

Please remove int sno =1; and sno++;. Because if you use auto increment int in sql fields, you dont need to use PK value like in here. Remove it please.

Serkan Hekimoglu 2010-10-08 19:33:23

Answer 2

A:

Two easy ways:

1) Do a regular update:

UPDATE myTable SET myvalue = myValue + @newXML WHERE myID = @id

2) Update the XML using SQL XML commands:

Example (From egghead cafe link):

update [News].[News] set

Article.modify('replace value of(/NewsArticle/@Title)[1] with
sql:variable("@Title")') where ID=@ID;

http://www.eggheadcafe.com/software/aspnet/33280808/update-xml-column-in-a-single-statement.aspx

http://whyiamright.wordpress.com/2008/01/02/updating-xml-column-in-sql-server-2005/

http://www.15seconds.com/issue/050818.htm

Ryan Ternier 2010-10-08 18:12:17

Answer 3

A:

If the XML can be imported into DataSet using ReadXml(...) method, you may store it into a database in very elegant way.

Schematically:

// Create and fill dataset
DataSet set = ...;
set.LoadXml(...);

// Open sql connection, make command and adapter
SqlDataAdapter adapter = ...;
adapter.InsertCommand = new SqlCommand("your sql command", connection);

// Save the dataset
adapter.Update(set, "TableName");

Dmitry Karpezo 2010-10-08 20:36:49

Answer 4

A:

Maybe this will help in gettin you started, I agree with Lucero that you need to look closely at your xml structure...

DECLARE @xml xml
SET @xml = '<Patients/>'

DECLARE @insertedPatient nvarchar(45)
SET @insertedPatient = 'Nads McSteamy'

DECLARE @insertedID int
SET @insertedID = 1


SET @xml.modify
            ('
            insert element Patient {sql:variable("@insertedPatient")} into 
            (/Patients)[1]
            ')

SET @xml.modify
            ('
            insert element ID {sql:variable("@insertedID")} after 
            (/Patients/Patient)[1]
            ')

select @xml

imlovinit 2010-10-08 20:58:33

ansaurus

tags:

views:

answers:

Transferring data from XML to SQL

related questions