Updating Dataset from XML

[Bill]

I'm hoping there is an easy solution for this, so I'll lay
it on you guys:

I have a single SQL Server database table called
Customers. Updates to this table are provided by an
external source via XML. These updates can be either
additions to the list of customers, or updates to existing
customers. Updates can be recognized by using an existing
customer id (in other words, the XML document has a
<CustomerID> element with a value that matches the
CustomerID column for one of the rows in the Customer
table. CustomerIDs are unique). I don't need to worry
about deleting customers from the table.

Seems pretty simple right? Is there an easy way to do this
using the DataSet? I've explored the Merge method but I
believe it can only ADD rows from the source dataset to
the target dataset, and doesn't support updates from
another DataSet. From what I understand, XML Web Services
help solve this problem by passing back a DiffGram from
the client that contains only the changed records, and
then Merge is smart enough to make the proper updates. But
is there a way to do that with plain old XML? Can I tell
the Merge method to add rows from the XML with new
CustomerIDs and update rows where the CustomerID already
exists in the table?

Thanks Guys.

 

[Cor Ligthert]

Bill,

A dataset is a document that uses XML, it is not a XML document.

By instance it has no attributes however only elements, I think that the
only way to go is to use the XML node reader to create an XML dataset from
your XML document.

A little sample of that what I once made

\\\\
Dim xmlString As String = "<department>" & _
"<employee name=""ABC"" age=""31"" sex=""male""/>" & _
"<employee name=""CDE"" age=""40"" sex=""male""/></department>"
Dim sr As New System.IO.StringReader(xmlString)
Dim doc As New Xml.XmlDocument
doc.Load(sr)
'or just in this case doc.LoadXML(xmlString)
Dim reader As New Xml.XmlNodeReader(doc)
While reader.Read()
Select Case reader.NodeType
Case Xml.XmlNodeType.Element
If reader.Name = "employee" Then
MessageBox.Show(reader.GetAttribute("name"))
End If
End Select
End While
///

I hope this helps?

Cor

 

[Bill]

Cor,

Thanks for your speedy reply. I can certainaly do
something similar, where I loop through the XML document
and add/update rows in the dataset accordingly. But I seem
to remember an article or two about the XMLDataDocument
that is a hybrid of both a DataSet and an XMLDocument. I
thought that maybe I could use that to load my XML
document, and then merge the data with the DataSet from
the database. Am I way off base here? Maybe I don't fully
understand what the Merge method of the DataSet really
does, and how it handles updates to the target database.

Maybe I'll just have to resort to a good old loop...

Thanks again,
Bill

 

[Cor Ligthert]

Bill,

I think you are inline and I thought that I have seen it somewhere,

However I thought that is one of those methods that take a lot of time to
investigate how and to make.

And in fact would they do of course as well nothing else than looping to the
tables and make the dataset.

So I go in this kind of situations just as you said,

Cor

 

[Bill]

Cor,

I imagine your right about what it's doing under the
covers. If this is possible, though, it would offer some
nice abstraction from the underlying code that I won't
have to worry about maintaining. Anyway, I'll conceed and
resort to looping. I probably could have been done by now
anyway

Have a good one Cor,
Bill