Create and read from xml file to database

[netangel]

I'm doing an interface between my supplier database and my company's. My supplier already has an interface (webservice) that waits for a request of information in a xml package and sends the requested info in a xml package.

All I need to do is insert the returned xml file into my database. Is there any easy and clean way of doing this?

The returned xml has a standard format like:


<?xml version=&quot;1.0&quot; encoding=&quot;ISO-8859-1&quot;?>
<QML version=&quot;1.0&quot; timestamp=&quot;2001-03-09T18:39:09-08:00&quot; xml:lang=&quot;pt-PT&quot;>
<Header>
<From>....</From>
<To>...</To>
<Sender>
<Login>...</Login>
</Sender>
</Header>
<RequestList>
<Application businessOperation=&quot;ARTICLES&quot;>
<ARTICLES>
<ARTICLESHEADER>
<BRAND>99</BRAND>
<NUMBER>ART001</NUMBER>
<DESCRIPTON>My 1st Article</DESCRIPTON>
</ARTICLESHEADER>
<ARTICLESHEADER>
<BRAND>88</BRAND>
<NUMBER>ART002</NUMBER>
<DESCRIPTON>My 2nd Article</DESCRIPTON>
</ARTICLESHEADER>
</ARTICLES>
</Application>
</RequestList>
</QML>

I've managed to get the '<ARTICLES>' into a datatable
Assuming my table structure is the same as the xml file, is there any way of saving this to the database without having to loop for each entry of the datatable?

Also I have to do the oposite: The suplier's interface asks for some products and I have to build a xml file with this very same structure. Is there any easy way?

Thanks.


NetAngel

 

[jcisco]

i know sql server has a XML parser that will take in xml and write that data to a table. and you can do a select statement and have it return XML.
but if you use a dataset. that dataset is already in xml form. why not just send that?

example
public function send(blah blah.) as string
'returns the table as xml
return DataSet11.GetXml()
end function

--------------

 

[jasonsalas]

This is fairly easy - just keep in mind that the DataSet object is a container for one or more DataTables. You can then run through each of the DataTable's DataColumns, and populate an INSERT statement this way.

Example/pseudocode:
1) If your XML structure is a saved file on disk, then use the DataSet's ReadXml() method to read the XML data into a new DataTable within the DataSet. This automatically puts everything into rows and fields, as in a DB.
2) Run a foreach... statement on the DataTable, taking the value of each DataColumn and populate the fields of an INSERT statement or, better yet, a stored procedure.

TIP: the first time out, you'll want to DataBind the DataSet to a DataGrid web server control to see the exact schema of the DataTable(s) the ReadXml() method creates for you. Because some of the nodes are nested, this might take some easy manipulation.

If you need the specific code, let me know...I've done this a few times before.

 

[netangel]

As I said before, I already have the xml file into a datatable. The datatable has 3 columns: 'brand', 'number' and 'description'. The 3 columns are automaticly recognized as char(50). I need to save them into a database table with the same 3 columns, but with different data types: integer,varchar(6),varchar(50). This means I cant'n update the datatable directly into the database.

I can't use the sql server parser, because I need to validate the file and run it every other hour.

All I want is a way of 'mapping' the fields with a datatable that I can merge into the database. I dont'n want to use code loops (nor 'for each' nor 'while' nor 'do').

Thanks anyway for your help.


NetAngel