Converting XML into Dataset/CSV as a Single Table/Sheet

Question

0.00/5 (No votes)

See more:

Hi All,

I have a weekly stock/product update that gets emailed to me from my suppliers in XML Format, and I'm struggling to get it converted/the data extracted into a Dataset or a CSV file that I can work with, to use in another program I've written up in VB.NET that updates my online shopping cart's product/stock list.

The format of the XML is like this:

XML

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<StockCatalogue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="DickerData_StockCatalogue.xsd" generated="2010-07-05T06:00:00">
  <StockItem>
    <StockCode>AF513A</StockCode>
    <StockType>Item</StockType>
    <QtyOnHand>0</QtyOnHand>
    <StockDescription>
      <StockDescriptionShort>HP 3PH 16A INTL Mod PDU</StockDescriptionShort>
      <StockDescriptionLong></StockDescriptionLong>
      <StockPrimaryCategory>SERVERS</StockPrimaryCategory>
      <StockSecondaryCategory>HP SERVER OPTION</StockSecondaryCategory>
      <StockTertiaryCategory>PDU</StockTertiaryCategory>
    </StockDescription>
    <StockVendor>
      <Vendor>HP</Vendor>
      <VendorPartCode>AF513A</VendorPartCode>
    </StockVendor>
    <Pricing>
      <RetailPriceEx>726.36</RetailPriceEx>
      <RetailPriceInc>799.00</RetailPriceInc>
      <ResellerPriceEx>600.25</ResellerPriceEx>
      <ResellerPriceInc>660.27</ResellerPriceInc>
      <CurrentPriceEx>600.25</CurrentPriceEx>
      <CurrentPriceInc>660.27</CurrentPriceInc>
    </Pricing>
    <StockSpecs>
    </StockSpecs>
  </StockItem>

Can someone please give me or assist me in writing up the source code to be able to get all the above data into a Single Table that I can work with, as at the moment, it seems to be creating up to 8 different tables when trying to convert it into a dataset

Your help is much appreciated

Cheers,
Ray.

Posted 10-Jul-10 7:01am

RayFrangie

Updated 10-Jul-10 10:31am

Sandeep Mewara

v2

Add a Solution

Comments

Sandeep Mewara 10-Jul-10 16:32pm

Formatted the code part correctly for you!

RayFrangie 11-Jul-10 10:06am

Thanks Sandeep... How did you do it? I had it in the code brackets but it didn't work :(

Cheers,
Ray.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RayFrangie · Answer 1 · 2010-07-11T04:10:00

If it helps... here's the XSD's code...

<pre><<pre lang="xml">?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="StockItem">
<xs:complexType>
<xs:sequence maxOccurs="unbounded">
<xs:element name="StockCode" type="xs:string"/>
<xs:element name="StockType">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="Bundle"/>
<xs:enumeration value="Item"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="StockBundle" minOccurs="0">
<xs:complexType>
<xs:sequence maxOccurs="unbounded">
<xs:element name="BundleComponentStockCode" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="QtyOnHand" type="xs:integer"/>
<xs:element name="StockDescription">
<xs:complexType>
<xs:sequence>
<xs:element name="StockDescriptionShort" type="xs:string"/>
<xs:element name="StockDescriptionLong" type="xs:string"/>
<xs:element name="StockPrimaryCategory" type="xs:string"/>
<xs:element name="StockSecondaryCategory" type="xs:string"/>
<xs:element name="StockTertiaryCategory" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="StockVendor">
<xs:complexType>
<xs:sequence>
<xs:element name="Vendor" type="xs:string"/>
<xs:element name="VendorPartCode" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Pricing">
<xs:complexType>
<xs:sequence>
<xs:element name="RetailPriceEx" type="xs:decimal"/>
<xs:element name="RetailPriceInc" type="xs:decimal"/>
<xs:element name="ResellerPriceEx" type="xs:decimal"/>
<xs:element name="ResellerPriceInc" type="xs:decimal"/>
<xs:element name="PromoPriceStart" type="xs:date" minOccurs="0"/>
<xs:element name="PromoPriceEnd" type="xs:date" minOccurs="0"/>
<xs:element name="CurrentPriceEx" type="xs:decimal"/>
<xs:element name="CurrentPriceInc" type="xs:decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="StockSpecs">
<xs:complexType>
<xs:sequence>
<xs:element name="SpecCategory" type="xs:string" minOccurs="0"/>
<xs:element name="StockSpecItem" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="SpecName" type="xs:string"/>
<xs:element name="SpecValue" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="StockCatalogue">
<xs:complexType>
<xs:sequence maxOccurs="unbounded">
<xs:element ref="StockItem"/>
</xs:sequence>
<xs:attribute name="generated" type="xs:dateTime"/>
</xs:complexType>
</xs:element>
</xs:schema></pre>
</pre>

RayFrangie · Answer 2 · 2010-07-11T04:07:00

Solution 3

Hi Andrew

Thanks for the reply...

Are you able to please show how in VB.NET? Source code would be great!

Cheers,
Ray.

Posted 11-Jul-10 4:07am

RayFrangie

Comments

Andrew Rissing 11-Jul-10 11:04am

I can't really help you here with that. It really depends on what exactly you're wanting to do with the data. The best advice I can give is to google around for "VB.NET DataTable" and go from there.

RayFrangie 11-Jul-10 20:36pm

Hi Andrew...

All I need is for the StockItem with all it's values below it, be put into a single row, for every StockItem there is in the document. I'll keep looking but havent't had much luck over the past few week...

Cheers,
Ray.

Andrew Rissing · Answer 3 · 2010-07-10T15:44:00

Solution 2

Look at your XML file's declaration for the XSD:

xsi:noNamespaceSchemaLocation="DickerData_StockCatalogue.xsd"

The data has multiple 'tables' in it. You will have to manually create a single table using code if that's what you're wanting or use some kind of XSL transformation to do so.

Posted 10-Jul-10 15:44pm

Andrew Rissing