Webservice-Result: save as XML-file or into a database

[mibock]

This is my first thread in this forum. I am programmer in VBA and PL/SQL. DotNet I have read many books, so I am beginner in praxis-things. Also I am not native to the English language. So I hope my question is not too trivial.

I use VB.NET in the Professional-Version 2008:

I attached successfull over WSDL a Webservice to my project. DotNet created automatically the non-user-code, so I can call and use all the methods and propertys of the webservice.

In this way I can use and fill all of the input-parameters of the webservice, call it and get back a encapsulated resultset , which I can write back to a database: recordset after recordset into the correct different database-tables of (in this case) an Access-Database. The tables are linked to the project as datasource-tableadapters.

So far so good... Works excellent... But... The writing of the resultset-data to the database is very very slow.

Example:

the DB-Table is named t_Message. WebserviceTabs_dotNetDataSetTableAdapters DotNet makes available because of the datasource. WSResult is the resultset of the Webservice.

Dim oM_A = New WebserviceTabs_dotNetDataSetTableAdapters.t_MessageTableAdapter

For lngI = 0 To WSResult.MESSAGES.Length - 1
With WSResult.MESSAGES(lngI)
oM_A.Insert(.TYPE, .MSG_NUMBER, .MESSAGE)
End With
Next


My questions:

1. How can I write the resultset of the webservice as a XML-file. I think the webservice still sends back the result as an XML, so how can I grab it in its native form, without putting DotNet the content in classes and propertys of automatically generated code. It would be nice to have a little code-snipped if it is difficult.
In general my question is: if you link an arbitrary Webservice automatically by WSDL and let DotNet generate the code to use it, how are you writing the result into a XML-file?

2. Perhaps a simular question is how to write the resulting data to a database in a speedy way? The method described above seems to be a mighty overhead but I do not know why.
I ask myself, if the data of the webservice isn't a kind of dataset I can connect/link with a databasetable to write it back in "one move"?
Or more general: how are you writing back mass-datas you got in a resultset from a webservice into a database? A little codesnippet would be fine.

I hope for several helpful answers. Sorry for my rusty English.

Yours, Michael

 

[cjard]

You mention PLSQL but dont claim to be using Oracle.. When I receive XML data in one of my processes here (I work with Oracle 100%) I have .net deserialize it into a datatable, and then use a tableadapter to write the data to a database; I don't find it particularly slow (several hundred records per second) but I'm not writing large amounts of data.. the XML is deser'd and stored with one attribute per DB column rather than being written to oracle as an xml document (which oracle can support, but turns out to be a nuisance to work with)

i.e.
<?xml...?>
<root>
<person><name>Mark</name><age>1</age></person>
<person><name>Luke</name><age>2</age></person>
</root>

Goes into a table:
NAME | AGE
------------
Mark | 1
Luke | 2

The code might look something like:

Dim ds as MyDataSet
ds.ReadXml(xml)
For Each ro as MyDataRow In ds.MyTable
  ro.SetAdded()
Next
myTa.Update(ds.MyTable)

 

[mibock]

Sorry for the confusion. I am not native English. The first sentence was just to inform what my basic work is. This question had nothing to do with Oracle, PL/SQL. Just a DotNet-problem.

I just try to give a better example. It is very trivial choosen, it has nothing to do with reality or a project! It is just to explain the nature of the problem.

I have two Tables: Persons und Pets, I want to write XML-Data to. In the pet-table is a foreign-key Person_ID, so the pet has a person as owner. A person can have many pets as you see with the first person in the XML-example who has two pets.

My XML looks like:

<Person>
<Person_ID>1</Person_ID>
<PersonName>Max Mustermann></PersonName>
<PersonAge>40</PersonAge>
<Pet>
<Pet_ID>1</Pet_ID>
<PetName>Bello</PetName>
<PetAge>5</PetAge>
</Pet>
<Pet>
<Pet_ID>2</Pet_ID>
<PetName>Hasso</PetName>
<PetAge>2</PetAge>
</Pet>
</Person>
<Person>
<Person_ID>2</Person_ID>
<PersonName>Lara Lustig></PersonName>
<PersonAge>53</PersonAge>
<Pet>
<Pet_ID>3</Pet_ID>
<PetName>Chucky</PetName>
<PetAge>0.4</PetAge>
</Pet>
</Person>

The tables look (f.e.) like this:

Table Person:

Person_ID int (primary key)
PersonName char(50)
PersonAge int

Table Pet:

Pet_ID int
PetName char(50)
PetAge int
Person_ID int (foreign-key)

How can I write back this XML-data in a fast way to the tables?

If you look at the XML you see that there is no XML-tag for the foreign-key Person_ID, because the "person"-XML-tag for the person "surrounds" the pet, so it is clear this pet belongs to the person and also it is technically clear that the primary-key of the person is the foreign-key needed for the pet.

Is there a way to tell a dataset that the data must be interpreted in such a way? Perhaps by XSL or another XML-thingy?

Yours, Michael.

 

[JohnH]

How can I write the resultset of the webservice as a XML-file.

You can serialize the data contained in the generated proxy class to xml.

Dim serv As New localhost.Service
Dim res = serv.GetSomeData
Dim ser As New Xml.Serialization.XmlSerializer(res.GetType)
Using file As New IO.FileStream("data.xml", IO.FileMode.Create, IO.FileAccess.Write)
    ser.Serialize(file, res)
End Using

Now there is xml data that can be read into a DataSet with the ReadXml method and forwarded all in to the db. (SetAdded is not needed btw, all rows read has already state Added.)
You can also use xsd.exe to generate a strongly typed dataset from the data.xml file, and add that to project. Then add tableadapter and queries to that in designer.

 

[mibock]

If I try to read it to a dataset it says "no root element".

 

[cjard]

Wrap your XML in a <People></People> tag pair