6.1. External XML documents Worldwide Widget Corporation is known for its award-winning customer service. It responds to all customer complaints with a sincere personalized form letter. Thanks to XML, the letter is largely computer-generated. Figure 6-1 shows how a typical letter looks when rendered by Word. Figure 6-1. Typical customer letter, rendered by Word 
The XML representation is shown in Example 6-1 and its schema in Example 6-2. Example 6-1. Typical customer letter (letter.xml) <?xml version="1.0" encoding="UTF-8"?> <customerLetter xmlns="http://xmlinoffice.com/letter"> <date>2004-04-15</date> <address> <line>123 Front Street</line> <city>Traverse City</city> <state>Michigan</state> <zip>49684</zip> </address> <greeting> Dear <salutation>Mr.</salutation> <customer id= "C12266">Steve Jackson</customer>:</greeting> <body type="problem"> <p>Thank you for your recent <format>letter</format> to our Customer Service department. We apologize for the difficulties you had with <problem>ordering from our Web site</problem>. We take your concerns seriously and will address them promptly.</p> <p>We appreciate your business and hope to retain you as a customer.</p> </body> <footer>Sincerely, <emp >Denise Dorning</emp> </footer> </customerLetter>
Example 6-2. Customer letter schema (letter.xsd) <?xml version="1.0"?> <xs:schema targetNamespace="http://xmlinoffice.com/letter" xmlns="http://xmlinoffice.com/letter" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <xs:element name="letter" type="LetterType"/> <xs:complexType name="LetterType"> <xs:sequence> <xs:element name="date" type="xs:date"/> <xs:element name="address" type="AddressType"/> <xs:element name="greeting" type="GreetingType"/> <xs:element name="body" type="BodyType"/> <xs:element name="footer" type="FooterType"/> </xs:sequence> <xs:attribute name="id" type="xs:ID"/> <xs:attribute name="type" type="xs:string"/> </xs:complexType> <xs:complexType name="AddressType"><xs:sequence> <xs:element name="line" type="xs:string" maxOccurs="unbounded"/> <xs:element name="city" type="xs:string"/> <xs:element name="state" type="xs:string"/> <xs:element name="zip" type="xs:string"/> </xs:sequence></xs:complexType> <xs:complexType name="GreetingType" mixed="true"><xs:sequence> <xs:element name="salutation" type="xs:string"/> <xs:element name="customer" type="IdentifiedType"/> </xs:sequence></xs:complexType> <xs:complexType name="BodyType"> <xs:sequence> <xs:element name="p" type="ParaType" maxOccurs="unbounded"/> </xs:sequence> <xs:attribute name="type" type="xs:string"/> </xs:complexType> <xs:complexType name="ParaType" mixed="true"> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="format" type="xs:string"/> <xs:element name="problem" type="xs:string"/> </xs:choice> </xs:complexType> <xs:complexType name="FooterType" mixed="true"><xs:sequence> <xs:element name="emp" type="IdentifiedType"/> </xs:sequence></xs:complexType> <xs:complexType name="IdentifiedType"> <xs:simpleContent><xs:extension base="xs:string"> <xs:attribute name="id" type="xs:string"/> </xs:extension></xs:simpleContent> </xs:complexType> </xs:schema>
The letter has XML elements representing the customer's name and address, the nature of the problem, the name of the employee who generated the letter, and so on. Because of the self-describing markup, these data elements can easily be manipulated, validated or completed from an external data source. For example, Worldwide's system automatically fills in a customer's address based on its name, imports canned text from an external document, and automatically fills in the city and state based on the ZIP code. 6.1.1 Importing an external XML document Note from Example 6-1 that the body element has a type attribute: <body type="problem">
The plan is that the letter reply system will eventually support a variety of body types. For each type there would be a separate XML file with appropriate canned text. For now there is just the XML document shown in Example 6-3, which is to be included in every customer "problem" letter. It contains a paragraph to be embedded directly in the body of the letter, with placeholder text for the format and problem elements to be replaced by the human sending the letter. Example 6-3. Canned letter paragraph (problem_para.xml) <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <p xmlns="http://xmlinoffice.com/letter">Thank you for your recent <format>ENTER FORMAT</format> to our Customer Service department. We apologize for the difficulties you had with <problem>ENTER PROBLEM</problem>. We take your concerns seriously and will address them promptly.</p>
You can set up your Word document so that when a body element is inserted, it is automatically populated with the contents of the problem_para.xml document. The VBA function shown in Example 6-4 accomplishes this.[1] [1] All of the examples in this chapter are written in Visual Basic for Applications (VBA). Example 6-4. Function to automatically insert external XML document Private Sub Document_XMLAfterInsert(ByVal NewXMLNode As XMLNode, ByVal InUndoRedo As Boolean) If NewXMLNode Is Nothing Then 'do nothing ElseIf NewXMLNode.BaseName = "body" Then Dim theRange As Word.Range Set theRange = NewXMLNode.Range theRange.InsertFile "C:\problem_para.xml" End If End Sub
This function is automatically executed in response to the event XMLAfterInsert, which occurs every time an XML element is added to the letter document. On line 5 it checks whether the newly inserted element is of type body. If so, it creates a range variable theRange and assigns it the range of the newly inserted body element. It then inserts the contents of problem_para.xml into the range. (In the future, it will test the value of the type attribute to determine which external XML document to insert.) A working version of this code can be found in the example file letter insert.doc. Tip  | The inserted canned text need not be an XML document in a file system. It could also be returned from a URL reference. For example, Worldwide Widget has a customer information application that returns a customer's address from, for example: http://xmlinoffice.com/customerlookup?id=123456. The returned XML can be inserted exactly as if it was a local file. The important thing is the XML; any means of addressing it will work the same way. |
6.1.2 Querying an external XML document On the other hand, suppose you do not want to insert an entire XML document, but want to look up a value in an external XML document. For example, you might have some customer names and addresses stored in an XML document that looks like the one in Example 6-5. Example 6-5. External customer address file (customers.xml) <?xml version="1.0" encoding="UTF-8"?> <customers> <customer> <name>Steve Jackson</name> <salutation>Mr.</salutation> <address>123 Front Street</address> <city>Traverse City</city> <state>Michigan</state> <zip>49684</zip> </customer> <customer> <name>Brenda Smith</name> <salutation>Ms.</salutation> <address>5 Wilson Boulevard</address> <city>Arlington</city> <state>Virginia</state> <zip>22203</zip> </customer> <!-- etc. --> </customers>
When a document author enters the customer name in the customer element, the document should automatically look up the customer's address in the customers.xml file and complete it in the letter. The function shown in Example 6-6 will do this. Example 6-6. Function to automatically update address based on customer name Sub GetAddress() Const xmlnsLetter = "xmlns:let='http://xmlinoffice.com/letter'" Dim str_customer, str_line, str_city, str_state, str_zip As String Dim cust_node, line_node, city_node, state_node, zip_node As XMLNode Set line_node = ActiveDocument.SelectSingleNode("//let:line", xmlnsLetter) Set city_node = ActiveDocument.SelectSingleNode("//let:city", xmlnsLetter) Set state_node = ActiveDocument.SelectSingleNode("//let:state", xmlnsLetter) Set zip_node = ActiveDocument.SelectSingleNode("//let:zip", xmlnsLetter) 'Open the customers document Dim custXML As MSXML2.DOMDocument50 Set custXML = New MSXML2.DOMDocument50 custXML.async = False custXML.Load ("c:\customers.xml") 'Find the appropriate customer element str_customer = ActiveDocument. SelectSingleNode("//let:customer", xmlnsLetter).Text Set cust_node = custXML. SelectSingleNode("//customer[name='" & str_customer & "']") 'Assign values from customer.xml to letter.xml If cust_node Is Nothing Then MsgBox ("Invalid customer name " + str_customer) line_node.Text = "UNKNOWN" city_node.Text = "UNKNOWN" state_node.Text = "UNKNOWN" zip_node.Text = "UNKNOWN" Else line_node.Text = cust_node.SelectSingleNode("address").Text city_node.Text = cust_node.SelectSingleNode("city").Text state_node.Text = cust_node.SelectSingleNode("state").Text zip_node.Text = cust_node.SelectSingleNode("zip").Text End If End Sub
-
Lines 9 through 16 assign the relevant nodes of the letter document to variables. -
Lines 19 through 22 then access the customers.xml document and assign it to the variable custXML. -
Lines 25 to 28 retrieve the customer name from the letter document and attempt to find a match in the customer document. -
If the customer node is found, lines 38 through 41 assign the selected values to the relevant nodes in the letter document. This function assumes that the line, city, state and zip elements already exist in the document. A more sophisticated function could be written to check for their existence and insert them if necessary. 6.1.3 Responding to an event Rather than requiring the user to take some action to update the address, perhaps you want this function to be executed every time the user changes the customer name. To do this, you need to write a function that responds to the XMLSelectionChange event, which occurs every time the user changes the selection to a different element. The desired function is shown in Example 6-7. It determines whether the changed XML is the content of the customer element and, if so, it calls the GetAddress function. Example 6-7. Responding to the XMLSelectionChange event Dim WithEvents oApp As Word.Application Private Sub Document_Open() Set oApp = Application End Sub Private Sub oApp_XMLSelectionChange (ByVal Sel As Word.Selection, ByVal OldXMLNode As Word.XMLNode, ByVal NewXMLNode As Word.XMLNode, Reason As Long) If OldXMLNode Is Nothing Then 'do nothing ElseIf OldXMLNode.BaseName = "customer" Then GetAddress End If End Sub
A working version of this code can be found in the example file letter query.doc. | 
