Using XML in Other Office Applications


In addition to the big two applications of Excel and Word, the various editions of Microsoft Office include other applications that either save or edit XML. Two of the more commonly used are Access and InfoPath.

Microsoft Access

Microsoft Access is the data access component in the Microsoft Office suite. Whereas SQL Server is the tool targeted at DBAs and Microsoft FoxPro the dedicated developer, Access has always been the ease- of-use database. You can use Access either to create applications that store data externally or against its own data format.

Importing XML

Because XML is frequently used as an intermediate data format, it should come as no surprise that Access is capable of importing XML. This import by default attempts to identify each of the tables in the XML and creates new Access tables with a similar structure. However, this is often not the behavior you want. You may want to create the table structure or map one XML format to a target table. Both of these options are available with Access.

In addition to importing both structure and data, you can limit the import to structure only. Access attempts to infer the type of each element in the XML and generates the appropriate column type. If you want better control over this process, you can import an XML schema file instead of an XML file (see Figure 25-8). This uses the type information in the schema file to better define the resulting table (see Figure 25-9). Each global type in the schema becomes a table. Relationships are maintained through the creation of primary/foreign keys. You can then import valid XML into the resulting table without errors.

image from book
Figure 25-8

image from book
Figure 25-9

If the XML does not completely align with the target table format, you can also apply a transformation during import. This identifies an XSLT stylesheet file that is applied to the XML before import.

Although this import is one-way, it can be a useful means of creating a tool for managing an XML file. As you learn in a moment, it is equally easy to get XML back out of an Access database.

Saving as XML

Tables, queries, views, forms, and reports in Microsoft Access can all be exported as XML. This means that you can dump the data in one or more tables as XML or use the definition of the object to generate an appropriate XML schema or transformation.

The simplest use of this feature is to select an object, then File image from book Export image from book Select XML as the output format. You see the dialog shown in Figure 25-10. This gives you the option of exporting the data, the schema, and/or the presentation of the data (for example an XSLT file).

image from book
Figure 25-10

When saving tables, you have the option of including any related tables (see Figure 25-11) by selecting the More Options button. This can be useful to recreate the hierarchy of the data in the XML file. Listing 25-11 is part of an export of the Categories and Products tables from the Northwind database that ships with Microsoft Access.

image from book
Figure 25-11

Listing 25-11: Exporting related tables

image from book
      <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"        xsi:noNamespaceSchemaLocation="Categories.xsd"        generated="2006-06-11T16:53:13">      <Categories>      <CategoryID>1</CategoryID>      <CategoryName>Beverages</CategoryName>      <Description>Soft drinks, coffees, teas, beers, and ales</Description>      <Picture>base64 encoded image</Picture>      <Products>      <ProductID>1</ProductID>      <ProductName>Chai</ProductName>      <SupplierID>1</SupplierID>      <CategoryID>1</CategoryID>      <QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit>      <UnitPrice>18</UnitPrice>      <UnitsInStock>39</UnitsInStock>      <UnitsOnOrder>0</UnitsOnOrder>      <ReorderLevel>10</ReorderLevel>      <Discontinued>0</Discontinued>      </Products>      <Products>      <ProductID>2</ProductID>      <ProductName>Chang</ProductName>      <SupplierID>1</SupplierID>      <CategoryID>1</CategoryID>      <QuantityPerUnit>24 - 12 oz bottles</QuantityPerUnit>      <UnitPrice>19</UnitPrice>      <UnitsInStock>17</UnitsInStock>      <UnitsOnOrder>40</UnitsOnOrder>      <ReorderLevel>25</ReorderLevel>      <Discontinued>0</Discontinued>      </Products>      ...      </dataroot> 
image from book

You can see a reference to a schema file (in the exported XML in Listing 25-11) generated during the export. This schema file (see Listing 25-12 for part of this document) contains the usual XSD definition of the XML, as well as additional information used by Access

Listing 25-12: An exported XML schema

image from book
      <?xml version="1.0" encoding="utf-8"?>      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"      xmlns:od="urn:schemas-microsoft-com:officedata">        <xsd:element name="dataroot">          <xsd:complexType>            <xsd:sequence>              <xsd:element ref="Categories" minOccurs="0"              maxOccurs="unbounded" />            </xsd:sequence>            <xsd:attribute name="generated" type="xsd:dateTime" />          </xsd:complexType>        </xsd:element>        <xsd:element name="Categories">          <xsd:annotation>            <xsd:appinfo>              <od:index index-name="CategoryName"              index-key="CategoryName" primary="no" unique="yes"              clustered="no" />              <od:index index-name="PrimaryKey" index-key="CategoryID"              primary="yes" unique="yes" clustered="no" />            </xsd:appinfo>          </xsd:annotation>          <xsd:complexType>            <xsd:sequence>              <xsd:element name="CategoryID" minOccurs="1"              od:jetType="autonumber" od:sqlSType="int"              od:autoUnique="yes" od:nonNullable="yes" type="xsd:int" />              <xsd:element name="CategoryName" minOccurs="1"              od:jetType="text" od:sqlSType="nvarchar"              od:nonNullable="yes">                <xsd:simpleType>                  <xsd:restriction base="xsd:string">                   <xsd:maxLength value="15" />                  </xsd:restriction>                </xsd:simpleType>              </xsd:element>              <xsd:element name="Description" minOccurs="0"              od:jetType="memo" od:sqlSType="ntext">                <xsd:simpleType>                  <xsd:restriction base="xsd:string">                   <xsd:maxLength value="536870910" />                  </xsd:restriction>                </xsd:simpleType>              </xsd:element>              <xsd:element name="Picture" minOccurs="0"              od:jetType="oleobject" od:sqlSType="image">                <xsd:simpleType>                  <xsd:restriction base="xsd:base64Binary">                   <xsd:maxLength value="1476395008" />                  </xsd:restriction>                </xsd:simpleType>              </xsd:element>      ...      </xsd:schema> 
image from book

The preceding XML schema contains a root element of dataroot. This includes the XML schema names- pace and an additional URN (urn:schemas-microsoft-com:officedata) that is used to identify hints for Microsoft Access should the data be imported back into Access. These hints include any indexes to apply to the resulting table, any keys on the table, and the data type to map to the XSD type.

In addition to using Access to generate XML schemas, you can also use the export function to generate a view of the data using XSLT. This generates two files: an XSLT stylesheet that renders HTML output and either an HTML page or an Active Server Page (ASP) file that uses the XSLT to render the data. The end result is a fairly accurate rendition of the original object. Figure 25-12 shows the output from one of the reports in the Northwind database.

image from book
Figure 25-12

Figure 25-13 shows the resulting HTML file that leverages the transformation.

image from book
Figure 25-13

Although not as full featured as dedicated XML tools from Altova, Stylus, or Oxygen, Microsoft Access can help the average user create XML, XSD, and XSLT files based on his data. These files can be used as-is or as the starting point for further refinements.

Microsoft InfoPath

Microsoft InfoPath is the first tool from Microsoft designed to support XML from the ground up. It is a form-based tool and, as such, it has properties similar to the Word editing capabilities. However, it does much more than simply enable the creation of XML files. It is a capable Web Service client and schema editor. InfoPath is covered in more detail in Chapter 24.




Professional XML
Professional XML (Programmer to Programmer)
ISBN: 0471777773
EAN: 2147483647
Year: 2004
Pages: 215

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net