Case-Study: XML in the Microsoft Office System (Contoso Pharmaceuticals)

  • Article

 

John Peltonen
3Sharp

Frank Rice
Microsoft Corporation

May 2003

Applies to:
    Microsoft® Office Access 2003
    Microsoft Office Excel 2003
    Microsoft Office Word 2003

Summary: Learn how to create an XML solution using applications from the Microsoft Office System in the context of a pharmaceutical company. You'll see how easy it is to map data into a Word document, create an intelligent Smart Document, and work with data maps and PivotTable reports in Excel. By the end of this article, you should have a better idea how you can use this functionality in your own business. (23 printed pages)

Important The information set out in this topic is presented exclusively for the benefit and use of individuals and organizations outside the United States and its territories or whose products were distributed by Microsoft before January 2010, when Microsoft removed an implementation of particular functionality related to custom XML from Word. This information may not be read or used by individuals or organizations in the United States or its territories whose products were licensed by Microsoft after January 10, 2010; those products will not behave the same as products licensed before that date or licenses for use outside the United States.

Download odc_xmlno11.exe.

Click here to download sample - odc_xmlno11.exe.

Contents

Background
Using XML in Word
Applying Intelligence to a Document with Smart Documents Technology
Using XML in Access
Using XML in Excel
Conclusion

Background

Contoso Pharmaceuticals is taking advantage of the integrated XML functionality within the Microsoft® Office System to solve a major business problem. Their sales people are required to go on frequent sales calls to doctors. They usually get about two minutes of the doctor's time, during which they must make their best case as to why the doctor should write prescriptions for their medications, and to give them samples, if requested. In order to be prepared to brief the physician and answer their questions, their sales representatives must pull data from a variety of sources about such things the drug’s interactions, efficacy, insurance or formulary information, as well as prepare forms for the physicians to sign if they are to receive samples.

Putting this sales information together requires gathering data from multiple systems, including company drug databases, marketing databases, insurance databases, and even customer relationship management (CRM) systems in order to find on the physician. For example, it's important to know whether a copy of the physician's current license is on file so that they can receive samples. Once at the doctor site, if the doctor agreed to accept some samples, the sales representative must write down the number of samples provided and get the doctor to sign a receipt. After the visit, the salesperson is expected to document the meeting so that management and the scientists back at headquarters can review the feedback about their products.

With the Microsoft Office System, the sales team at Contoso is able to develop a new solution – one that automates many of the sales tasks by leveraging data that already exists in various sources throughout the company. With Office 2003, every salesperson can take advantage of the XML functionality that natively exists in each Office 2003 application.

In this article, you will see how three Microsoft Office System applications, Microsoft Office Word 2003, Microsoft Office Access 2003, and Microsoft Office Excel 2003 use XML and how Contoso was able to combine the strengths of these three applications to build a powerful solution. By the end of this article, you should have a better idea how you can use this functionality in your own business.

After completing the procedures in this article, you will be able to:

  • Understand how to use XML schemas.
  • Map an XML schema to a Word document.
  • Understand Smart Documents.
  • Use Access' XML capabilities to manipulate XML data.
  • Use Excel's XML capabilities to perform rich, real-time analysis of data.

Using XML in Word

The document you'll use is a template that the sales force uses. This is just a basic Word template to begin with. You will go through the process of defining XML, building the schema, and mapping the schema to the document.

In this procedure, you will take an existing Word document template and learn how to enhance it by integrating XML.

Load the Word Document

  1. Start Word 2003.

  2. On the File menu, click Open, and then navigate to the file CustomDatasheet_SchemaNotMapped.doc.

    Figure 1. Page 1 of the CustomDatasheet_SchemaNotMapped document

  3. Scroll up and down to view the entire document.

  4. To understand the logical pieces of the document, and how we will build an XML schema to describe those pieces, look at the document in more detail:

    • In the header, there is a placeholder for the doctor’s name.

    • The first major piece of the document is a letter consisting of a date, an opening, a body and a closing.

    • At the bottom of the first page, there is a placeholder for the product information.

    • On the second page, the first set of variable information is contained in a table meant to list the product sample information. It has four columns: Previous Quantity, Quantity, Brand Name, and Dosage. Note that the number of rows is variable, based on how many products are added to the document.

      Figure 2. Page 2 of the CustomDatasheet_SchemaNotMapped document

    • So as you can see, the form is meant to store the doctor’s information.

View the Schema

  1. On the Microsoft Windows&174; desktop, click Start, click Run, type Notepad.exe, and then click OK.

  2. In Microsoft Notepad, click File, and then click Open.

  3. Navigate to the file datasheet.xsd under the datapath folder, and then click Open.

  4. Maximize the window. A listing similar to the following is displayed:

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns="contoso_datasheet_demo"
   targetNamespace="contoso_datasheet_demo"
   elementFormDefault="qualified">

<xsd:element name="datasheet" type="datasheettype"/>

<xsd:complexType name="datasheettype" mixed="true">
   <xsd:sequence>
      <xsd:element name="drname" type="xsd:string" minOccurs="1" />
      <xsd:element name="letter" type="lettertype" minOccurs="1" />
      <xsd:element name="products" type="xsd:string" minOccurs="1" />
      <xsd:element name="samplesorder" type="samplesordertype" />
      <xsd:element name="samplesform" type="samplesformtype" />
   </xsd:sequence>
</xsd:complexType>

<xsd:complexType name="lettertype">
   <xsd:sequence>
      <xsd:element name="date" type="xsd:string" />
      <xsd:element name="opening" type="xsd:string" />
      <xsd:element name="body" type="xsd:string" />
      <xsd:element name="closing" type="xsd:string" />
   </xsd:sequence>
</xsd:complexType>

<xsd:complexType name="formcompoundtype" mixed="true">
   <xsd:sequence>
      <xsd:element name="brand" type="xsd:string" />
      <xsd:element name="dosage" type="xsd:string" />
      <xsd:element name="quantity" type="xsd:string" />
      <xsd:element name="prevquantity" type="xsd:string" />
   </xsd:sequence>
</xsd:complexType>

<xsd:complexType name="samplesordertype" mixed="true">
   <xsd:sequence>
      <xsd:element name="compound" minOccurs="0" maxOccurs="unbounded">
         <xsd:complexType>
            <xsd:sequence>
               <xsd:element name="prevquantity" type="xsd:string" />
               <xsd:element name="quantity" type="xsd:string" />
               <xsd:element name="brand" type="xsd:string" />
               <xsd:element name="dosage" type="xsd:string" />
            </xsd:sequence>
         </xsd:complexType>
      </xsd:element>
   </xsd:sequence>
</xsd:complexType>

<xsd:complexType name="samplesformtype" mixed="true" >
   <xsd:sequence>
      <xsd:element name="drtype" type="xsd:string" />
      <xsd:element name="drname" type="xsd:string" />
      <xsd:element name="institution" type="xsd:string" />
      <xsd:element name="addr" type="xsd:string" />
      <xsd:element name="city" type="xsd:string" />
      <xsd:element name="state" type="xsd:string" />
      <xsd:element name="zip" type="xsd:string" />
      <xsd:element name="phone" type="xsd:string" />
      <xsd:element name="fax" type="xsd:string" />
      <xsd:element name="licnum" type="xsd:string" />
      <xsd:element name="exprdate" type="xsd:string" />
      <xsd:element name="liccopy" type="xsd:string" />
      <xsd:element name="signature" type="xsd:string" />
      <xsd:element name="date" type="xsd:string" />
   </xsd:sequence>
</xsd:complexType>
</xsd:schema>

  5. Within the datasheet, find the definition of the datasheet which starts on line 9 with the opening tag:

    <xsd:complexType name="datasheettype" mixed="true">

    And finishes on line 17 with the closing XML tag:

    </xsd:complexType>

  6. Below this section, notice how each element in the samples form and doctor’s form are described in greater detail. For example, the <Lettertype> element has the subelements – <data>, <opening>, <body>, <closing>.

  7. Exit Notepad.

This schema file will be attached to the Contoso document. Once you attach a schema to the document, the document's elements can be defined by XML, so that the data within any given XML element can be manipulated intelligently. For example, the text that falls within the Doctor's name element will be described by the schema as "doctor's name" <drname>. The data is thus exposed intelligently, and any system that knows the schema will be able to retrieve the data and place it into a database or other application without having to know where in the document the data resides. This is the basic idea behind using XML.

Attach the Schema to the Document

  1. While still in Word 2003, with the CustomDatasheet_SchemaNotMapped.doc loaded (displayed), click Tools, and then click Templates and Add-Ins.
  2. On the XML Schema tab of the Templates and Add-Ins dialog box, click Add Schema.
  3. Browse to datasheet.xsd and click Open.
  4. In the Schema Settings dialog box, type Memo Example in the Alias box. This will be the name that the schema will be known and referenced by.
  5. Select the Changes affect current user only check box, if it isn't already, and then click OK.
  6. In the Templates and Add-Ins dialog box, select Memo Example and then click OK.

Map the Schema Elements to the Document Elements

  1. In the bottom right of the XML Structure task pane, select datasheet underneath Choose an element to apply to your current selection.

  2. When prompted, select Apply to Entire Document.

    Figure 3. The Apply to entire document dialog box

    Notice in the bottom right that you now can apply the XML elements to various parts of the document. By applying the root element of the schema, all of the other elements become available.

  3. At the top of the document, highlight Doctor’s Name Here and then click <drname> in the list of available elements.

  4. Highlight the letter underneath the header, starting with the date and ending with Sales Executive.

  5. Click <letter> in the list of available elements. Notice that the subelements under letter are now available to be mapped.

  6. Highlight the date within the letter and select the <date> element.

  7. Highlight Dear Dr. Cetinok, (including the comma) within the letter and select the <opening> element.

  8. Highlight the letter text (beginning with Today and ending with bbbb) within the letter and select the <body> element.

  9. Highlight the closing (beginning with Best and ending with Executive) within the letter and select the <closing> element.

    Note   If you wish to skip to the next section, there is a fully mapped document in the file CustomDatasheet_Mapped.doc.

    Figure 4. Page 1 of the CustomDatasheet_SchemaNotMapped.doc document with XML elements

  10. On the second page (see Figure 5), select the entire Receipt of Samples Form area by clicking the tab that appears at the top left of the table when you hover over it. Select the <samplesorder> element.

  11. Highlight the four empty cells in the table and select the <compound> element.

  12. Click inside the cell below Previous Quantity (click to the right of the <compound> tag) and select the <prevquantity> element.

  13. Click inside the cell below Quantity and select the <quantity> element.

  14. Click inside the cell below Brand Name and select the <brand> element.

  15. Click inside the cell below Dosage (click to the left of the <compound> tag) and select the <dosage> element.

  16. Select the entire Doctor’s information form by clicking the tab that appears at the top left of the table when you hover over it. Select the <samplesform> element.

  17. Click inside the cell to the right of MD or DO? and select the <drtype> element.

  18. Click inside the cell to the right of Physician Name and select the <drname> element.

  19. Click inside the cell to the right of Institution and select the <institution> element.

  20. Click inside the cell to the right of Street Address and select the <addr> element.

  21. Click inside the cell to the right of City, State and Zip and select the <city>, <state>, and <zip> elements.

  22. Click inside the cell to the right of Phone Number and select the <phone> element.

  23. Click inside the cell to the right of Fax Number and select the <fax> element.

  24. Click inside the cell to the right of State License and select the <licnum> element.

  25. Click inside the cell to the right of Expiration Date and select the <exprdate> element.

  26. Click inside the cell to the right of Copy of State License and select the <liccopy> element.

  27. Click inside the cell to the right of Physician Signature and select the <signature> element.

  28. Click inside the cell to the right of Date and select the <date> element.

    Figure 5. Page 2 of the CustomDatasheet_SchemaNotMapped.doc document with XML elements

  29. Save the document as ContosoTemplate.doc.

Applying Intelligence to a Document with Smart Documents Technology

In this section, you will learn how to install and use a Smart Document. Thus far, you have created a Word template that is tagged with XML. When a sales person enters data into this document, not only is a richly formatted sales document created which can be handed off to a doctor, but valuable information is captured that can be easily analyzed at a later date. However, there is still more leg work that has to be done in order to gather the information required for this document.

For example, the sales person must go to the various data sources within the organization to retrieve the doctor’s information, the doctor’s specialty, which products that Contoso sells target that specialty, and how many samples of those products has the doctor ordered in the past. This information is currently stored in four different data sources.

For the sake of our sales person, Contoso would like to automate this process as much as possible. Fortunately, thanks to the XML Smart Document technology rolled into Office 2003, we are able to apply intelligence to do just that. XML makes data within the Word document accessible to any system that knows what XML elements to look for. Well, it works both ways: The smart document solution, through XML, can find locations within the document to place data, and it can find locations from which to retrieve data.

Attach the Smart Document DLL

Note   Because of an issue in running security checks on manifests at the time this article was written, you need to disable the manifest security check by navigating to the disableManifestSecurityCheck.reg (provided as part of the download for this article) and then double-click the file. Click OK in the verification dialog box.

Note   You may need to modify the paths in the manifest file (manifest.xml) to match the drive and path of the datasheet.dll and the datasheet.xsd files. The default paths are C:\.

  1. While in the document you saved in the previous procedure (ContosoTemplate.doc), click Templates and Add-Ins on the Tools menu.

  2. On the XML Expansion Packs tab of the Templates and Add-Ins dialog box, click Add.

  3. Navigate to manifest_.xml and click Open.

    IMPORTANT   When prompted to re-enable smart document security, click NO.

  4. If prompted, click Yes to install the solution for all users on the machine and then click OK.

  5. On the XML Expansion Packs tab of the Templates and Add-Ins dialog box, select the Contoso Datasheet Solution underneath Available XML expansion packs.

  6. Click Attach and then OK to attach the smart document logic.

    Figure 6. The XML Expansion Packs tab on the Templates and Add-Ins dialog box

Use the Smart Document for Retrieval of Information

  1. Press CTRL+SHIFT+X to hide the pink XML tags in the document.

  2. Scroll to the top of the document and highlight the Doctor’s Name Here placeholder.

  3. In the task pane on the right, highlight the Doctor’s Name Here box and type Andrew Dixon.

    NOTE   This must be spelled correctly and is case-sensitive.

    Figure 7. The Doctor’s Name Here text box in the Document Actions task pane

  4. Click back into the letter area on the left. It will automatically place you towards the bottom of the document. Scroll up and down to view the new data that has been entered. The doctor’s name has been inserted anywhere the <drname> tag exists, and the Smart Document retrieved data from various databases and knew where to put it based on the XML tags within the document. Look at the table for an example.

  5. Scroll back to the top of the document and click anywhere in the letter area again.

  6. In the task pane on the right, select Pediatrics.

    Figure 8. Specialty options in the task pane

    Once the smart document has the doctor’s specialty, it is able to query the back end data sources to find out which products mapped to the doctor’s specialty and how many (if any) of the products the doctor had previously ordered. Now our document has the doctor’s information filled out, the appropriate glossy product datasheets, and a table filled out with the doctor’s previous order history.

  7. Scroll down through the document to view the new data that has been entered.

    Figure 9. First page of the Contoso data sheet

Fill in the Doctor’s Order

Scroll down to the last page and notice the Receipt of Samples Form. In the Quantity column (2nd from the left), enter numerical values for each of the products.

Figure 10. Second page of the Contoso data sheet

Saving the Smart Document

  1. Click anywhere on the Doctor’s information form.

  2. Note the large Click to apply changes and save document and data button in the Document Actions pane. Click it to save. The smart document will save the order information to a database.

    Figure 11. The Click to apply changes button in the Document Actions pane

Saving the Data to an XML File with WordML

  1. On the File menu, click Save As.
  2. Navigate to a directory that you will remember.
  3. In the File Name field, type salesreport_formatted.
  4. In the Save as Type field, select XML Document (*.xml).
  5. Clear Save Data Only, if it is selected.
  6. Click Save.

Saving the Data to an XML File

  1. On the File menu, click Save As.
  2. Navigate to a directory that you will remember.
  3. In the File Name field, type salesreport.
  4. Choose XML Document (*.xml) from the Save as File list.
  5. Select the Save Data Only check box.
  6. Click Save to save as an XML document.
  7. Click Continue when warned about losing Word ML formatting.

Viewing the Files in Internet Explorer

  1. In Windows Explorer, navigate to the location where you saved the previous files.

  2. Right-click salesreport_formatted.xml, point to Open With, and then click Internet Explorer.

    Figure 12. Salesreport_formatted.xml file

    Notice that everything contained within this document is straight up XML, even down to the formatting elements such as bold and italics.

  3. Right-click salesreport.xml, point to Open With, and then click Internet Explorer.

    Figure 13. Salesreport.xml file

Notice that only our namespace elements (from the schema we attached) remain. What is important to keep in mind is that in either case; whether you keep the formatting or not, the XML defined data is just as accessible. This means that these living, breathing documents can be fully integrated with enterprise level business logic and data at any stage in their lifecycle! We’ll see this in the next exercise.

Using XML in Access

In this section, you will learn how to utilize the built-in XML functionality of Access to selectively import the doctor's order quantities entered in the Word document and insert those numbers into a small database that we can report from, either within Access itself, or by exporting XML that can then be loaded into Excel.

One benefit that XML integration gives middle managers is the ability to get up to the minute reports on how their field employees are doing. Frequently, this level of management receives any sort of business intelligence report at roughly the same time upper management does. This is frequently because business intelligence solutions can be time consuming and difficult to deploy.

By using some simple, built in functionality of Access, we can feed all of our sales reports into a small database that we can report from, either within Access itself, or by exporting XML that can then be loaded into Excel.

Load the Access Database

  1. Start Access 2003.

  2. Open salesdb.mdb.

    Note   This table contains the details from all of the sales datasheets that management feels are important.

  3. Double-click to open the detail table.

    Figure 14. Access detail table

  4. Close the detail table.

Import the Latest Sales Report

  1. On the File menu, point to Get External Data, and then click Import.

  2. In the Files of type drop down list, click XML (*xml;*.xsd).

  3. Navigate to salesreport_formatted.xml and then click Import.

    Because Access works with strongly typed data, it will warn us when we are using a non-Access specific schema. This has no effect on the actual data importation. It just means that Access cannot import the schema.

    Figure 15. XSD Schema warning screen in Access

  4. Click Yes to continue.

    Figure 16. The Import XML dialog box with elements from salesreport_formatted.xml

    As you can see, Access is taking a look at the huge amount of XML in our document and trying to split it up into what are logical units of information. To put more structure into this process, you can use an XSL Transform file to specify what pieces of data we want.

  5. On the Import XML dialog box, click Options, click Transform, and then click Add.

  6. Browse to import.xsl and click Add.

    Figure 17. The Import Transforms dialog box with import transform highlighted

  7. Click OK.

  8. Expand orderitem to view five columns.

    Figure 18. The data columns of the Import transform

  9. Click OK to import the data.

  10. Click OK at the finished message.

Insert Imported Data

  1. Double-click to open the orderitem table. This data has been imported from the Word document.

    Figure 19. The Orderitem table

  2. Close the orderitem table.

  3. In the Database window, click Queries under Objects.

  4. Double-click Insert new orders into Detail table.

  5. Click Yes through all of the warning messages.

  6. In the Database window, click Tables under Objects.

  7. Open the details table and click CTRL+END to move to the bottom of the table.

    Note   The data from the Word document have been appended to this table.

    Figure 20. The Details table with two records appended

  8. Close the details table.

Export the Last Four Weeks of Data

  1. In the Database window, click Queries under Objects.

  2. Double-click Last 4 Weeks. This query selects the last four weeks of sales data. We’ll export this data for use in Excel.

    Figure 21. The Last 4 Weeks query

  3. Close the query window.

  4. Right-click Last 4 Weeks and select Export.

  5. In the Files of type drop down list, click XML (*xml;*.xsd).

  6. Navigate to location that you will remember and click Export.

  7. Click OK and then close Access.

Using XML in Excel

In this section, you will learn how to easily map elements within a schema to a spreadsheet. For this example, we will be using the schema that Access generated along with the XML file. By mapping elements from a schema as opposed to just loading the data itself, we are allowing this spreadsheet to exist independently of the underlying data. Thus, once the schema is mapped, we can load data from any XML file that is mapped to the schema.

Load a Schema into an Excel Workbook

  1. Start Excel 2003.

  2. Open reportingtool.xls.

  3. Click the tab at the bottom to navigate to the Data worksheet.

  4. On the Data menu, point to XML, and then click XML Source.

  5. In the XML Source pane, click XML Maps.

  6. In the XML Maps dialog box, click Add, navigate to the file Last 4 weeks.xsd, and then click Open.

  7. When prompted, select dataroot as your root element and click OK.

  8. Click OK to close the XML Maps dialog box.

    Figure 22. The XML Maps dialog box

Map the Schema

Once the file is displayed in the task pane, a list of the elements that you can map is displayed. Because the schema is specific to our report, we'll simply drag the <Last 4 Weeks> element over to our spreadsheet to map all of the data all at once.

Drag the <Last 4 Weeks> element from the XML Structure pane to cell A1 on the data worksheet.

Figure 23. Last 4 Weeks elements mapped in a worksheet

Import the Data

Now that the schema has been mapped in the worksheet, you can import data from XML files that share the schema definition.

Note   If you were to run this query next week and generate a new XML file, all you would need to do is refresh the data within Excel for the latest version of the data to show up in the report.

  1. To import the data, on the Data menu, point to XML, and then click Import.

  2. Browse to Last 4 weeks.xml and then click Import.

    Figure 24. Last 4 Weeks populated map

Create a PivotTable Report

  1. On the Tool worksheet, select cell D10 and then on the Data menu, click PivotTable and PivotChart Report.

  2. Select Microsoft Excel list or database and click Next.

    Figure 25. Step 1 of the PivotTable and PivotChart Wizard

  3. In the Range field, enter Data!A1:E2285.

    Figure 26. Step 2 of the PivotTable and PivotChart Wizard

  4. Click Finish.

Add Data to the Report

  1. Drag Week_Name into the Column fields.
  2. Drag Product_Name into the Row fields.
  3. Drag Detail_ID into the Data Items field.
  4. Close any small windows that may be blocking your view of the worksheet.

Now, at quick glance, we can see that the overall numbers have been going up from week to week. The point here, though, is that this is real-time, up to the minute data that was exposed through XML.

Figure 27. Finish PivotTable report

Conclusion

In this article, you saw how three Office 2003 applications, Microsoft Office Word 2003, Microsoft Office Access 2003, and Microsoft Office Excel 2003 can be combined to build a powerful XML solution. After working through the steps in this article, you should have a better idea how you can use this functionality in your own business solutions.

© Microsoft Corporation. All rights reserved.