The Alphabet Soup of XML

Note that in the previous example, we had only an XML file and Excel was able to accurately read the data, allow editing, and write the data back out for use by another application.

Two additional file types—schemas and transforms—enhance XML files.

Although the XML file contains the data and field names, an XML Schema file defines data relationships and data validation requirements. For example, a zip code field could require five numeric digits. XML Schemas are typically stored in XSD files.

XSL files are called Transforms or Solutions. A Transform describes how the fields in the XML file should be mapped to your document. If your data contains 20 elements, you can define in the transform file that you want to see only particular elements in this spreadsheet. You can have many XSL files for a particular schema to enable many views of the same data.

If you are reading someone else's XML data, XSD and XSL files have probably been provided for you. If you are reading your own XML data, Excel actually infers a data schema for you. After opening the XML file with TodaysOrders introduced at the start of this chapter, you can go to the immediate pane of the Visual Basic Editor (VBE) and retrieve the XSD file. Type

Print ActiveWorkbook.XmlMaps(1).Schemas(1).xml

Copy the result to a Notepad window and save as TodaysOrders.xsd. Figure 15.4 shows the inferred XSD file, although I have added line breaks and spacing to improve the readability.

The final step to fully use XML in Office 2003 is to create XSL transform files. There is no easy way to create this with the tools in Excel. For a discussion of the rather awkward process of manually creating an XSL file, see http://www.mrexcel.com/tip064.shtml.

Figure 15.4

Excel is able to infer a default schema for any XML file that it encounters. Having an .XSD schema file is a requirement to use any higher level XML features such as repurposing data.

r NcwGrdcri.iad Notepad

<*sd:5<h«n* mlns:*sd-"hx*p://*ww.w?. orq/i<K)lAwi,Schwa"> ijidiclenent n1 n ¿bl e-' true" nane»"Tc>!ljyioriters"> <xsd: carpi exTyiws-

<1« rj: conp lex f ype> <xsd :sc-queicc n*" <*sd:element n cxtd:c1tirain? n1ftOCtgri."0" nil libit" "true" tyjie-'xitf V«sd:elBtenx>

<*5d:elrmcns n1nOecgr5«"G" nllliblex^rge" T>|lf,-"*3<i c/asdrelencnt»

-£)iS(i:elimpni n1«Je<grs«"0" nillable.~trge" Type*

(n.d:L-luniiril niriOCCuri-Vxidielenenti-<*sd:el«ni»rrc nlnoccurs-

<x5d:elBnent nlnoceurs* </*id:ilcnint> </xsd: sequence»

</isd: sequence </iid:cemplexrype> KAid:e1cnenti

■0" nm able-"true" "0" ninsble«"trge"

i-"5i1eiOrder" fomi""uriciud11fitd"j string" name-"Cgstoner" form«"unciuilHl1ed"> string" njmc-'Addr«}" fflrm."gr«|g4l1f1i:d"> String" naim>"c1ty" form»"unquo11fHcd"> String" name-"'State" fori»«-"»inqu»Hf1«i~s-Integer" nane»"21p" farm« "unqgall Med"» Integer" n4nf"ne«5iaj- form-"gnqw11f1e<l"> Integer" nane»"agantity" forn-"griquaHf1ed">

tillable» "true" type*"xsif:double name-"un1tPr1ie" forn-'unquallfled's

0 0

Post a comment