Creating an xsd to export xls to xml

D

Diane

I have a spreadsheet with a specific format:

B14: Customer No.
B15: Name
B16: Address
B17: City
B18: State
B19: Zip

A20 (header) B20 (header)
Item No. Qty Ordered

A21 ... A100 B21 ... B100
Column of Item Nos Qty of Each Item No ordered

All fields above B14 should be ignored (they are just visual stuff)

I want to create an XSD to this spreadsheet so I can save the document as
xml I can use and import elsewhere

How can I create the xsd so that I can map the spreadsheet? I guess my
question is - what is my next step so that I can save this as xml in the
proper format. I believe I need to create the xsd so I can map this and then
I can export it as xml.

Am I totally off base - or heading in the right direction?

Any help would be greatly appreciated.

Regards,
Diane
 
C

Chip Pearson

You could use a schema like the following:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
targetNamespace="tempuri.com/export"
xmlns:ex="tempuri.com/export"<xs:complexType name="TOrder">
<xs:sequence>
<xs:element name="ItemNumber"></xs:element>
<xs:element name="Quantity"></xs:element>
</xs:sequence>
</xs:complexType>

<xs:complexType name="TOrders">
<xs:sequence>
<xs:element
name="Order"
type="ex:TOrder"
minOccurs="0"
maxOccurs="unbounded">
</xs:element>
</xs:sequence>
</xs:complexType>

<xs:element name="ExportList">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerNumber"/>
<xs:element name="Name"/>
<xs:element name="Address"/>
<xs:element name="City"/>
<xs:element name="State"/>
<xs:element name="Zip"/>
<xs:element name="Orders"
type="ex:TOrders"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>



This would allow you create an XML file like

<?xml version="1.0" encoding="UTF-8"?>
<ex:ExportList xmlns:ex="tempuri.com/export"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="tempuri.com/export Orders.xsd">
<ex:CustomerNumber>1234</ex:CustomerNumber>
<ex:Name>Chip Pearson</ex:Name>
<ex:Address>1020 W 56 St</ex:Address>
<ex:City>Kansas City</ex:City>
<ex:State>MO</ex:State>
<ex:Zip>64112</ex:Zip>
<ex:Orders>
<ex:Order>
<ex:ItemNumber>321</ex:ItemNumber>
<ex:Quantity>10</ex:Quantity>
</ex:Order>
<ex:Order>
<ex:ItemNumber>4321</ex:ItemNumber>
<ex:Quantity>20</ex:Quantity>
</ex:Order>
</ex:Orders>
</ex:ExportList>

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top