Help please with XML file

[dickbaker]

I've been trying to figure out how to get the item numbers and quantities of items from two different distributors into two separate tables in a database. The purpose is to allow me to quickly update what's in stock and what is not for my online store. There will be literally thousands of different products.

I got the first distributor's products just fine, as they provide an update once a day in Excel. I just imported the file into SQL Server.

The second distributor has 20 minute updates using an XML file. They told me the code below was for an XML feed.

My first problem is that I don't know how to create a feed, or how to get the data from the feed into my database. I've read several articles on how to do it, but I'm coming up empty for results.

If anyone could suggest a way to handle this, I'd much appreciate it. Below is the first several lines of thousands of lines of code:

<?xml version="1.0" encoding="utf-8"?>
<DataSet xmlns="http://www.MyDistributor'sSite.com/InventoryUpdates">
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="I" type="xs:decimal" minOccurs="0" />
<xs:element name="Q" type="xs:decimal" minOccurs="0" />
<xs:element name="P" type="xs:decimal" minOccurs="0" />
<xs:element name="C" type="xs:decimal" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<Table diffgr:id="Table1" msdata:rowOrder="0">
<I>3311</I>
<Q>0</Q>
<P>759.50</P>
<C>759.50</C>
</Table>
<Table diffgr:id="Table2" msdata:rowOrder="1">
<I>3766</I>
<Q>0</Q>
<P>88.97</P>
<C>88.97</C>
</Table>
<Table diffgr:id="Table3" msdata:rowOrder="2">
<I>3837</I>
<Q>11</Q>
<P>117.80</P>
<C>117.80</C>
</Table>

[rocknbil]

What language is your API, or is there one?

Generally there are libraries for perl, PHP, asp, etc. that make parsing XML fairly simple, allowing you access to all or nested elements. You'd the use a script to extract and insert.

[dickbaker]

I don't know what language it is, but I checked a bit more and found I could set up Excel to import the data from the feed file above. It takes time to import the data into Excel and then import the Excel data into a SQL Server database, but I guess that's the only way to do it.

Or is it?

[rocknbil]

Well, if given the choice, I'd probably write a script in Perl, or PHP if required.

- use curl, wget, or some other background function within program to get the data. This script would be set up as a cron job that gets the data in regular intervals, that interval based on balancing timeliness with server load.

- Immediately log all the data I "get" for review in case something goes wrong.

- the module XML::Simple makes parsing out the XML tree very easy, I'd simply extract the nodes wanted for the task.

- Once extracted, the DBI module is used to connect to the database, insert the data. This could even be done in a moderated or temporary mode to force review of the data before making it live, but this may not be necessary.

the data is immediately available for display using programs connecting to the database, if unmoderated.

A mirror script can be constructed in PHP to do the same thing. The idea is if you need it on your site, your site should do all the work, you shouldn't have to import it into Excel, manipulate, export from Excel, then upload.

An alternate method is similar to what is done getting real-time shipping quotes from the USPS, UPS, and other API's, as those are XML too. Instead of cron jobbing it, when the page loads, just get the data and parse it out, display it. This may not be a great idea if there is a lot of data as it sounds in your case, but for a small feed it would work.

[dickbaker]

rocknbill, I recognized the words you used in your post, but I didn't understand the sentences. ;)

Having a process that updates the database from the XML feed would be great, but I have no idea how to do it. How would the process handle the one distributor's Excel file?

Each distributor's table has about 15,000 rows, with just two columns: the item number, and the quantity on hand.

This is something I'd be interested in jobbing out if I knew what I was looking for.

[rocknbil]

Ah. You'll probably wind up with a PHP coder from a third world country. Not to dis' them, the proportion of good coders to bad are just as diverse as in the U.S., just the cost of living is so much less there. It's making me a thin man. :-)

At any rate, the simplest solution to the Excel file - you would export it as a delimited plain text file. File->Export, select CSV (Comma separated values). Use any delimiter you like, tab, comma, personally I like the pipe ¦ as it's the least likely to intrude on regular language usage when breaking apart the fields. A good coder can build an upload interface that you can just click and upload to your site, and the programming will do the parsing and inserting.

The only limitation with PHP is by default it is limited to 2 MB uploads, and you have to tweak the configuration globally on the server via the php.ini or do some .htaccess modification per directory (which is probably a little more safe.) You don't need to know what that means, a programmer will. Perl, on the other hand, has no such limitation.

For a good RFP, assess all the functions you'd require - whether just a simple insert of the data into the database and your cart takes over, or need to build a display script as well to display the data. It's really hard to map out an RFP for you without knowing all the details, but you're looking for an upload of XML and CSV data to your site to be inserted into your shopping cart database. Consider whether values need to be compared and overwritten, duplicates need to be checked, do you need some form of reporting, what kind of administrative display is required for uploaded data to make sure all went well, editing/deleting of individual records, whatever.

Skills required would be Perl, PHP, ASP, or dot net depending on your server, and agility with mySQL/postgres SQL or MSsql, also dependent on your data server.