Â¥Ö÷

һƪ¹ØÓÚXMLµÄÓ¢ÎIJÄÁÏ XML and Database Tooling XML is found in more and more applications today. With its non-proprietary format, XML can be used to represent and exchange information in an application. The information for most applications is stored in some fashion. A relational database is the most common location for persisting information. With databases storing information and XML used for representing information, a relationship exists between the two technologies. Web Sphere Studio Application Developer has tooling that supports this relationship as well as individual relational database design tools and XML authoring tools. There are tools for working with existing databases schemas or creating new database schemas. There are also editors for specific types of XML documents. In this exercise you will use some of the XML tools and some of the features in the Data Perspective in Web Sphere Studio Application Developer. User Requirement User must have DB2 (version 7.2) and WebSphere Studio Application Developer installed.  The Invest database must be setup (c:\yourbank\lab5\dbsetup.bat) and the source files used in the¡¡lab must be available at c:\yourbank\lab5. What You Should Be Able to Do£® After completing this lab you should understand how to connect to an active database and import the schema into WebSphere Studio Application Developer. You should be able to add foreign keys to a table schema. By the end of this lab you will also be able to create SQL  statements from multiple tables using the SQL statment wizard. Finally, you should be able to use the XML to SQL wizard to query the active database and return the information in an XML document. With the XML results, you will use the XML to XML mapping wizard to translate the results to another XML format. Introduction The Invest database contains three tables for storing information on stocks owned by a customer and customer contact information. To extract this information from the database and possibly integrate it into your application, you will need to connect to the database and import the schema into WebSphere Studio Application Developer. With the schema in the Workbench, you will link the ADDRESS table to the PERSONALINFORMATION table. Next you will create a SQL SELECT statement that will select all of the customer address information. Then in the Data Perspective, you will execute the SQL statement against the database and obtain customer information in XML format for two customers. Finally you will use the XML to XML mapping wizard to map customer information to a statement DTD. ** NOTE ** Solution instructions are at the end of Part Five Exercise Instructions Part One: Setup Data Perspective __1. Set up the Invest database, if it has not been done already. __ Run C:\YourBank\Lab5\DBSetup.bat from a DB2 Command window Start->Programs->IBM DB2->Command Window Type C:\YourBank\Lab5\DBSetup.bat. Enter the password PASSWORD when prompted. __2. In Application Developer, Open the Data Perspective. __ Click Perspective --> Open --> Other... and click Data. __ Click OK. __3. Connect to the Invest database. __ In the DB Explorer view, right click and click New Connection.... __ In the Connection field, change the value to Invest DB Connection. __ In the Database field, change the value to Invest. __ In the User ID and Password fields, enter USERID and PASSWORD. __ Leave the other default values unchanged and click Finish. __4. Import data into a Project __ Click File --> New --> Project.... __ Click Simple and Project before clicking Next. __ For the Project name enter InvestDatabase. __ Click Finish. The new project will be created in a Resource perspective. __ Close the Resource perspective by right clicking on the Resource perspective iconand clicking Close. __ If the Data perspective is not displayed, switch to it and insure the DB Explorer view is displayed. __ Expand the Invest DB Connection and right click on Invest(jdbc:db2:Invest) and click Import to Folder... __Click Browse. Select the InvestDatabase project and click OK. Then click Finish. The schema information for the invest database will be imported into the InvestDatabase project. Part Two: Create Foreign Key¡£ __1. In the Data perspective, switch to the Navigator view. __2. Expand InvestDatabase and double click Invest DB Connection_Invest_USERID_ADDRESS.tblxmi. __3. Change to the Foreign Keys view by clicking on the tab __4. Click Add another. __5. For the Target Table field, click Browse... to select the table holding the key. __ Expand InvestDatabase\Invest (DB2 UDB, V7.2)\USERID\Tables __ Click USERID.PERSONALINFORMATION. __ Click OK. __6. Move the MEMBERID column over to the right pane with the >> button. This creates a relationship between the MEMBERID column in the local table is linked to the Primary Key field in the PersonalInformation table. __7. Save and Close the file. Part Three: Generating a SQL Script __1. Click File --> New --> Other... and select Data and SQL Statement. __2. Click Next. __3. You will be guided through the creation of a SELECT SQL statement. The SQL statement must use an existing database schema for reference. You can specify a se the Legacy database schema information imported during Part One. __ Expand InvestDatabase and select Invest (DB2 UDB, V7.2). __ Click OK. __4. In the SQL Statement Name field, enter customerinfo. __5. Click Next. __6. The Construct a SQL Statement window will be displayed with the Tables tab in view. You will need to select which tables will the select statement reference. __ Expand the DB2ADMIN schema and the Tables folder. __ Select USERID.ADDRESS and move it over to the right list by clicking the > button. __ Select USERID.PERSONALINFORMATION and move it over to the right list by clicking the > button. The PersonalInformation table and the Address table will be used to create the SQL statement. __7. Click the Columns tab to specify which columns in the table should be returned when selected. __ Expand USERID.PERSONALINFORMATION and move over the columns FULLNAME, SSN, and MEMBERID with the > button. __ Expand USERID.ADDRESS and move to the Selected Columns the columnsADDRESS, CITY, STATE, and POSTALCODE, listed under the table using the > button. Do not move over the column MEMBERID. __8. Click the Joins tab to specify how the relationship between the tables for the SELECT statement. __ Click and drag the mouse from MEMBERID in the Address table to MEMBERID in the PersonalInformation table. Release the button when you have reached MEBERID in the PersonalInformation table. Notice how the join between the two tables is created as you drag the mouse. __ Click Next. __9. The Work with SQL statement screen displays the source SQL statement. You couldmake any additional necessary changes to the SQL statement at this point. __10. Click Finish. The file invest_customerinfo.sqx will be created in the InvestDatabase project. You can open this file up and change the statement using the interface or by directly editing the SQL statement. Part Four: Executing SQL Script __1. With the script just created, you can run the SQL and have the results displayed in XML format. Open the file invest_custormerinfo.sqx. __2. In the Outline view, right click on customerinfo and select Generate new XML.... __3. Select the DTD radio button, leave the rest of the fields as the defaults and select Finish. The files customerinfo.dtd, customerinfo.html, customerinfo.xml, customerinfo.xsl, and customerinfo.xst are created in the InvestDatabase project. Open up customerinfo.html and notice the information displayed in the table. This information is from the database based on your query, displayed in HTML format. Close  the file when finished viewing. The customer.xml file contains the information from the database based on your query, except the information is displayed in XML format. The definition files for the XML are also available. With this combination, you can begin using information from your database in your application as it can be easily used in the XML format. Part Five: XML to XML Mapping Investment statements are sent out every month. The customer information and investment summary are printed on reports mailed to the customer. The layout for the statement requires the customer information to be in a specific XML format. You will map the customer information optained by your customerinfo SQL SELECT statement from Part Two to the statement XML format using the XML to XML Mapping wizard. __1. Before creating the mapping, create a project to hold the resulting XML files __ Click File --> New --> Project. Click Simple and select Project. Click Next. __ In the Project name field, enter, StatementXML __ Click Finish. __2. Import in the statement.dtd file which specifies the definition for the customer information. __ Click File --> Import and select File System and click Next. __ Click Browse... and navigate to c:\yourbank\lab5\statement.dtd. Double click on the box infront of folder lab5 to select it. Uncheck the DBSetup.bat Click OK. __ In the Folder field, click Browse... and select StatementXML. __ Click Finish. __3. With the Statement definition file in place, you are ready to begin mapping the customer information obtained with the customerinfo SQL SELECT statment to the Statement.  Start the wizard by clicking File --> New --> Other... and click XML and then click XML to XML Mapping. Click Next. __4. In the New XML to XML Mapping Session window, click StatementXML and click Next. __5. In the Source XML or DTD Files window, expand InvestDatabase and click customerinfo.xml and move it to the Select Files view by clicking the > button. You could have selected other documents allowing you to map multiple XML documents to one definition. Click Next. __6. In the Target XML or DTD File window, expand StatementXML and click statement.dtd.  You could have also select customerinfo.dtd. Click Next. __7. Click Finish. __8. The mapping file, xmlmap.xmx, will be displayed in the Editor view. Expand customerinfo\SQLResult\ADDRESS_PERSONALINFORMATION to view the elements in the source. __9. In the Target, expand statement\Statement\Address. Also expand AccountInfo and Contact. __10. Map the following elements from Source to the Target by dragging each element from the Source to the Target. Notice the Outline view is updated with each mapping. __ Select the ADDRESS element under Source and map it to AddressLine under Target. __ Select the CITY element under Source and map it to City under Target. __ Select the STATE element under Source and map it to State under Target. __ Select the POSTALCODE element under Source and map it to Zip under Target. __ Select the FULLNAME element under Source and map it to LastName under Target. __ Select the FULLNAME element again under Source and map it to FirstName under Target. __ Select the SSN element again under Source and map it to TaxID under Target. __ Select the MEMBERID element again under Source and map it to AccountNumber under Target. The Output view should look like the diagram below. __11. The LastName element is currently mapped to an attribute in the form of lastname,  firstname (e.g. Doe, John). To extract just the lastname from this attribute we need to define a special mapping. __ Under Target, right-click on LastName and select Define XSLT Function... __ In the Define a Function for this Mapping window, leave String selected and Click Next.    __ In the Function name field select substring-after. __ Select the Add button to bring up the Value Dialog window. In the Enter a value for the parameter field, type ¡® ¡® (1 apostrophe, 1 space, 1 apostrophe). Click OK. __ Select Finish. __12. Just as with the LastName element, the FirstName element is currently mapped to an attribute in the form of lastname, firstname. To extract just the firstname from this attribute we need to define a special mapping. __ Under Target, right-click on FirstName and select Define XSL Function... __ In the Define a Function for this Mapping window, leave String selected and select Next. __ In the Function name field select substring-before. __ Click the Add button to bring up the Value Dialog window. In the Enter a value for the parameter field, type ¡® ¡® (1 apostrophe, 1 space, 1 apostrophe). Click OK. __ Click Finish. __ Save the mapping file. __13. Select the Generate XSLT script for mapping button from the Tool Bar (last button on the right). __14. Select StatementXML and leave the File name as statement.xsl.  Click Finish. The statement.xsl file should be displayed and added to the XMLStatement project. __15. In the Navigator view, select customerinfo.xml in the InvestDatabase project and in the XMLStatement project, using the Control key, select statement.xsl. Right-click on the files and select Apply XSL -> As XML __16. The XML document customerinfo.xml has been converted to the Result XML output by the XSL file statement.xsl. Notice how the values in the customerinfo.xml file have been transferred to the elements in the Result XML. The value in the FULLNAME element in the customerinfo.xml has been separated into the LastName element and the FirstName element in Result XML. __17. At this point you can also trace through the conversion done by the XSL file. Make sure the XSL Trace Editor view is selected and the XSL Trace menu is visible. __18. From the XSL Trace menu, select Trace forward to begin tracing through the conversion by statement.xsl. You can also select the Trace forward button on the tool bar to progress through the trace (third button from the right end of the tool bar). __19. Select the Trace forward button a number of times and then the Trace backward button from the tool bar (second button from the right end of the tool bar). Watch as the lines corresponding to the mapping in the XML files are hi-lighted as well as the line in the XSL file performing the translation. __20. Select customerinfo.xml and statement.xsl again, right click, and click Apply XSL --> As HTML. The XSL Trace Editor will open again and display the Result as HTML.    Êղؠ  ·ÖÏí   ¶¥(0)

	µÚ2Â¥

ÓÐûÓз­ÒëÖÐÎÄ,,¿´²»¶®