Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Home > English site > Articles > XML and Excel > XML Schemas
Deze pagina in het Nederlands

XML and Excel

XML Schemas

An XML file can have a schema definition (XSD) attached to it. A schema definition determines the structure of the XML file. Other things an XSD file may define are:

To attach an XML file to a schema a reference to the schema file must be included inside the XML file. The schema reference can be added as an attribute of the root element, like this:

<companies xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Schema.xsd">

The text in Bold builds the reference to the schema file schema.xsd, which in this example must reside in the same folder as the xml file. Without getting into the details of the exact syntax of a schema definition here is an example tied to the file test1_en.xml:

Listing 1: Schema.xsd

<?xml version='1.0' encoding='UTF-16'?>
<!-- XmlMap.DataBinding.SourceUrl: C:\Data\OfficeMagazine\XML\test2.xml --> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">    <xsd:element name="companies">
       <xsd:complexType>
          <xsd:sequence minOccurs="0">             <xsd:element minOccurs="0" name="company" >                <xsd:complexType>
                   <xsd:sequence minOccurs="0">                      <xsd:element minOccurs="0" type="xsd:string" name="companyname" />                      <xsd:element minOccurs="0" maxOccurs="unbounded" name="employee" >                         <xsd:complexType>                            <xsd:sequence minOccurs="0">                               <xsd:element minOccurs="1" type="xsd:string" name="code" />                               <xsd:element minOccurs="1" type="xsd:string" name="name" />                               <xsd:element minOccurs="0" type="xsd:string" name="street" />                               <xsd:element minOccurs="0" type="xsd:string" name="Houseno" />                               <xsd:element minOccurs="0" type="xsd:string" name="areacode" />                               <xsd:element minOccurs="0" type="xsd:string" name="place" />                               <xsd:element minOccurs="0" type="xsd:string" name="phone" />                            </xsd:sequence>                         </xsd:complexType>
                      </xsd:element>
                   </xsd:sequence>
                </xsd:complexType>
             </xsd:element>
          </xsd:sequence>
       </xsd:complexType>
    </xsd:element>
 </xsd:schema>

Such a schema is obviously less easy to read than the accompanying XML file. More information on schemas.

Extensible Stylesheets

With Extensible Stylesheets (xsl files) xml files can be converted to other formats, e.g. to SpreadsheetML, the XML format that supports spreadsheets. How these stylesheets work is outside the scope of this article. More information about extensible stylesheets by the w3c organisation.


 


Comments

All comments about this page:


Comment by: Leemo Tsheboagae (3/31/2010 9:15:53 PM)

What are the disadvantages of XML schema?
How is an XML schema generated using UML models?

 


Comment by: Jan Karel Pieterse (3/31/2010 10:00:19 PM)

Hi Leemo,

To be honest, I have not used UML at all yet. I found a discussion on XML and UML here:

http://www.ibm.com/developerworks/library/x-umlschem/

 


Comment by: JD (11/28/2012 1:08:56 AM)

I hope you are still offering assistance with VBA script...

I created my XSD Schema described above, but now I'm trying to figure out the VBA code to map it to a 2010 Excel worksheet, before I import my XML data. I've looked through Google and cannot find anyone with knowledge of this code.


Workbooks.Add
ActiveWorkbook.XmlMaps.Add("c:\VendorDetails.xsd", "vendordetails").Name = "vendordetails_Map"
ActiveWorkbook.XmlMaps("vendordetails_Map").Import URL:="c:\VendPaymtDtl.xml"


Please let me know.

Thank you,

JD

 


Comment by: Jan Karel Pieterse (11/28/2012 8:45:10 AM)

Hi JD,

Check out this page, it contains a comment showing just how you do that:

http://www.jkp-ads.com/Articles/XMLAndExcel05.asp?AllComments=True

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].