CODEWITHSUNDEEP
xmlexcelxsd
Random
Random

Reputation: 467

Generate XSD file from MS Excel file definition

Hi, I have an Excel that contains fields names and type like the following example.

 ID : INT
 First_Name : String
 Last_Name : String
 Phone number : String

I would like to generate an XSD from the description that I have.

Is there a tool for this?

Upvotes: 4

Views: 22401

Answers (2)

tom redfern
tom redfern

Reputation: 31750

Note: there is no solution using just Excel.

This was true in 2013 when the OP asked the question, and is still true now, as far as I can tell.

The answers provided on this page, though they use external tooling, represent the only solutions on the internet at this time, and have since been viewed 13,000 times.

If you or anyone you know have a solution using only Excel to generate XSD, please add it, because this is clearly something that would help many people.

Model the class in code: 

public class Something
{
    public int ID { get; set; }
    public string First_Name { get; set; }
    public string Last_Name { get; set; }
    public string Phone_number { get; set; }
}

Save it to a .cs file and then compile it.

and then use xsd.exe to generate the xsd:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Something" nillable="true" type="Something" />
  <xs:complexType name="Something">
    <xs:sequence>
      <xs:element minOccurs="1" maxOccurs="1" name="ID" type="xs:int" />
      <xs:element minOccurs="0" maxOccurs="1" name="First_Name" type="xs:string" />
      <xs:element minOccurs="0" maxOccurs="1" name="Last_Name" type="xs:string" />
      <xs:element minOccurs="0" maxOccurs="1" name="Phone_number" type="xs:string" />
    </xs:sequence>
  </xs:complexType>
</xs:schema>

Upvotes: 3

Automate This
Automate This

Reputation: 31364

  1. In notepad, write your new schema like this:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <record>
   <ID>12345</ID>
   <FirstName>First Name</FirstName>
   <LastName>Last Name</LastName>
   <PhoneNumber>555-555-5555</PhoneNumber>
 </record>
 <record>
   <ID>12346</ID>
   <FirstName>John</FirstName>
   <LastName>Doe</LastName>
   <PhoneNumber>555-555-5555</PhoneNumber>
 </record>
</data-set>

  2. Save the file as schema.xml

  3. Open your excel file.
  4. On the Developer tab, open the XML Source task pane.
  5. To add an XML map, click XML Maps

enter image description here

  1. Click Add
  2. Select schema.xml and click OK twice
  3. Now simply drag (map) the 4 elements from the tree onto the worksheet (row 1). Image shows the first two done.

enter image description here

  1. On the Developer tab, under the XML group, click Export.
  2. Save your file as data-set.xml and press Enter.

Result will look like this:

enter image description here

Upvotes: 2

Related Questions