XML 和數據庫工具
What This Exercise is About
關於這個練習
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. WebSphere 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 WebSphere Studio Application Developer.
現在，XML出現在越來越多的應用當中。因為有公共的格式，XML可以在應用程序中描述和傳輸數據。在大多數應用中數據存儲成某種格式，關系數據庫是最常見的一個。數據庫存儲，XML描述，兩者之間形成了聯系。WebSphere Studio Application Developer有支持這個聯系的工具比如數據庫設計工具，XML編輯工具。也有創建和編輯數據庫結構的工具，還有編輯特定XML的工具。在這個練習中，您會使用一些XML工具和WebSphere 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:yourbanklab5dbsetup.bat) and the source files used in the
lab must be available at c:yourbanklab5.
DB2 7.2,WSAD, Invest 數據庫，c:yourbanklab5有要用到的代碼文件。
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.
課程之後，您能夠連接到一個數據庫，導入結構到WSAD，加入主鍵到表。使用SQL指南為多個表創建SQL命令。使用XML to SQL指南查詢數據庫，返回XML文件格式的數據。使用XMLtoXML映射指南轉到另外的格式。
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.
Invest數據庫有三個表，存儲著一個客戶的聯系人信息。為了在您的應用程序中使用這些數據，請鏈接數據庫，導入表結構到WSAD。使用工作台裡的結構，您可以鏈接ADDRESS表到PERSONALINFORMATION表。然後創建SQL SELECT命令，查詢出所有的客戶地址。在數據視圖，運行SQL命令得到XML格式的兩個客戶的信息。最後，使用XMLtoXML映射指南，把客戶信息映射到DTD（Document Type Definition，文檔類型定義）。
** 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:YourBankLab5DBSetup.bat from a DB2 Command window
Start->Programs->IBM DB2->Command Window
Type C:YourBankLab5DBSetup.bat.
Enter the passWord PASSWord when prompted.
__1. 如果沒有Invest,則配置Invest 數據庫.
__ 在DB2命令行窗口運行C:YourBankLab5DBSetup.bat。
Start->Programs->IBM DB2->Command Window
鍵入 C:YourBankLab5DBSetup.bat.
輸入密碼。（PASSWord）
__2. In Application Developer, Open the Data Perspective.
__ Click Perspective --> Open --> Other... and click Data.
__ Click OK.
__2. 在WSAD中，打開數據視圖。
__ 單擊 Perspective --> Open --> Other... and click Data.
__ 單擊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.
__3. 連接到 Invest 數據庫。
__ 在數據庫Explorer VIEw，單擊 New Connection....
__ Connection Name是Invest DB Connection.
__ Database是Invest.
__ User ID 和 Password 分別是USERID 和 PASSWord.
__ Leave the other default values unchanged and click Finish.
__ 其他值保持默認，不作改變，單擊 Finish.
__4. Import data into a Project
__ Click File --> New --> Project....
__ Click Simple and Project before clicking Next.
__4. 向項目導入數據。
__ 單擊 File --> New --> Project....
__ 單擊 Simple 和 Project 然後是 Next。
__ For the Project name enter InvestDatabase.
__ Project Name 是InvestDatabase。
__ Click Finish. The new project will be created in a Resource perspective.
__ Close the Resource perspective by right clicking on the Resource perspective icon
and clicking Close.
__ 單擊Finish。在資源視圖，項目已經創建。
__關閉資源視圖。
__ 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...
__切換到數據視圖。
__展開Invest DB Connection，右擊Invest(jdbc:db2:Invest) 然後單擊 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.
__單擊Browse（浏覽），選擇InvestDatabase,點OK。然後Finish。
Invest結構信息導入到了InvestDatabase項目。
Part Two: Create Foreign Key
第二部分：創建外鍵。
__1. In the Data perspective, switch to the Navigator vIEw.
__1.在數據視圖，切換到導航器視圖（Navigator vIEw）。
__2. Expand InvestDatabase and double click Invest DB Connection_Invest_USERID_ADDRESS.tblxmi.
__2. 展開 InvestDatabase ，雙擊 Invest DB Connection_Invest_USERID_ADDRESS.tblxmi 。
__3. Change to the Foreign Keys vIEw by clicking on the tab.
__3.切換到Foreign Keys vIEw（外鍵）
__4. Click Add another.
__4. 點 Add another.（添加另一個）
__5. For the Target Table fIEld, click Browse... to select the table holding the key.
__ Expand InvestDatabaseInvest (DB2 UDB, V7.2)USERIDTables
__ Click USERID.PERSONALINFORMATION.
__ Click OK.
__5.在目標表欄位，點Browse（浏覽）。選擇外鍵表，
__ 展開InvestDatabaseInvest (DB2 UDB, V7.2)USERIDTables
__ 點USERID.PERSONALINFORMATION.
__ 點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.
__6.單擊>>,把MEMBERID列移動到右邊。這樣，建立了一個在MEMBERID和PersonalInformation的主鍵之間的聯系。
__7. Save and Close the file.
__7.保存，然後關閉。
Part Three: Generating a SQL Script
第三部分：生成SQL腳本。
__1. Click File --> New --> Other... and select Data and SQL Statement.
__1.單擊File --> New --> Other... 選擇 Data 和 SQL Statement. （數據和SQL語句）。
__2. Click Next.
__2.單擊 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
schema already imported into the Workbench or import one at this time. You will use
the Legacy database schema information imported during Part One.
__ To the right of the Database Model fIEld, click Browse....
__ Expand InvestDatabase and select Invest (DB2 UDB, V7.2).
__ Click OK.
__3.您會一步步創建一個SELECT語句。SQL語句必須使用一個已經存在的表結構。您可以指定一個已存在的庫結構，或導入一個。在這裡，您將使用第一部分導入的數據庫。
__在數據庫模型（Database Model）右邊，單擊Browse...。
__展開InvestDatabase，選擇Invest (DB2 UDB, V7.2).
__點OK。
__4. In the SQL Statement Name fIEld, enter customerinfo.
__4. 語句名（SQL Statement Name）：customerinfo。
__5. Click Next.
__5.點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.
__6. 構造SQL語句窗口出來了。選擇SELECT語句用到的表。
__展開DB2ADMIN結構和表文件夾。
__選擇USERID.ADDRESS點>加入右邊列表。
__ 選擇USERID.PERSONALINFORMATION點>加入右邊列表。PersonalInformation 和Address表，會用來生成SQL語句。
__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 columns
ADDRESS, CITY, STATE, and POSTALCODE, listed under the table using the > button. Do not move over the column MEMBERID.
__7.選擇”列”（ Columns）頁面，指定表中要被選擇的列。
__展開USERID.PERSONALINFORMATION，選FULLNAME, SSN, 和 MEMBERID 。
__展開USERID. ADDRESS，選ADDRESS, CITY, STATE, 和POSTALCODE 。不要選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.
__8.選”連接”（Joins）頁面。指定SELECT中的連接關系。
__從Address的MEMBERID拖動鼠標，到PersonalInformation的MEMBERID放開。注意連接關系的建立是看您怎樣拖動鼠標。
__ Click Next.
__單擊Next。
__9. The Work with SQL statement screen displays the source SQL statement. You could
make any additional necessary changes to the SQL statement at this point.
__9.現在畫面上顯示了SQL語句。您可以在這裡加上必要的修改。
__10. Click Finish.
__10.點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 editting
the SQL statement.
invest_customerinfo.sqx文件建立並顯示在InvestDatabase項目內。您可以打開並且直接修改SQL語句。
Part Four: Executing SQL Script
第四部分：執行SQL腳本
__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.
__1.使用上面創建的SQL，你可以運行並且以XML格式顯示結果。打開文件invest_custormerinfo.sqx。
__2. In the Outline vIEw, right click on customerinfo and select Generate new XML....
__2.在“輪廓”（OutLine）內，右擊customerinfo，選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.
__3.選中 DTD選項，其他默認即可。然後單擊Finish。可以在InvestDatabase項目中看到創建了文件customerinfo.dtd, customerinfo.Html, customerinfo.XML,customerinfo.xsl, 和customerinfo.xst。
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.
打開customerinfo.html，注意表中的信息。這些信息來自您的Query，是Html格式。看完了請關閉它。
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.
customer.xml文件包含了數據庫中您的Query信息，但是顯示成XML格式，還有XML的定義文件。這兩者相結合，您就可以在應用中使用數據庫來的數據，因為很容易變成XML格式。
Part Five: XML to XML Mapping
第五部分：XML to XML映射
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.
投資聲明每個月都會發出。客戶信息和投資總結打印成報表發到客戶。聲明需要特定XML格式。您要把SQL SELECT產生的XML中含有的客戶信息從第二部分的XML映射過來。【？？？】
__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.
__1.在建立映射之前，建個項目存放XML文件。
__ 點 File --> New --> Project. 選 Simple 選 Project. 然後 Next.
__ Project name 是StatementXML。
__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:yourbanklab5statement.dtd.
Double click on the box infront of folder lab5 to select it. Uncheck the DBSetup.bat
Click OK.
__2.導入statement.dtd文件，它定義了用戶信息。
__點文件à導入，選文件系統，然後Next。
__ In the Folder fIEld, click Browse... and select StatementXML.
__ Click Finish.
__ 選中StatementXML
__ 點完成。
__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.
__3.有了定義文件，您就可以映射客戶數據數據了。
文件à新建à其他 …選XML，XML至XML映射。然後NEXT。
__4. In the New XML to XML Mapping Session window, click StatementXML and click Next.
__4.在新建XML至XML映射窗口，選StatementXML然後點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.
__5.在源XML，DTD或XSD文件窗口，展開InvestDatabase，把customerinfo.xml加入到右邊。您也可以選擇多個文檔來形成多XML文檔對一個源。點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.
__6.在目標XML，DTD或XSD文件窗口，展開StatementXML選statement.dtd，您也可以選customerinfo.dtd，點Next。
__7. Click Finish.
__7.點Finish.
__8. The mapping file, XMLmap.xmx, will be displayed in the Editor vIEw. Expand
customerinfoSQLResultADDRESS_PERSONALINFORMATION to vIEw the
elements in the source.
__8.映射文件XMLmap.xmx在編輯器裡顯示出來了。展開customerinfoSQLResultADDRESS_PERSONALINFORMATION，查看源中的元素。
__9. In the Target, expand statementStatementAddress. Also expand AccountInfo and Contact.
__9.在目標中，展開statementStatementAddress，還有AccountInfo 和 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.
__10.從源拖動元素到目標來完成映射。注意視圖會隨之變化。
__ 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.
__11.LastName和FistName映射了同一個元素，為了得到LastName，要作特別的映射。
__ 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.
__在目標內，右擊LastName，選擇“定義XLT函數”（Define XSLT Function...）。
__在定義函數窗口，選中String，點Next。
__函數名選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.
__點Add按鈕，彈出值對話框，輸入’ ’（單引號，中間空格），點OK。
__ Select Finish.
__點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.
__12.FirstName與此類似，只是substring-after改成substring-before.
__13. Select the Generate XSLT script for mapping button from the Tool Bar (last button on
the right).
__13.選擇（為映射生成XSLT腳本）Generate XSLT script for mapping按鈕。
__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.
__14.選擇StatementXML，FileName不變，就是statement.xsl。
__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
__15.在導航器裡，用Control鍵選擇InvestDatabase項目的customerinfo.xml和 XMLStatement項目內的statement.xsl。右擊，選 應用XSLà作為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.
__16.通過使用XSL 文件statement.xsl，XML文件customerinfo轉化成了Result XML 輸出。請注意customerinfo.xml裡的值是怎樣到Result XML裡的。FULLNAME的值已經分解成LastName和FirstName。
__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.
__17.在這裡，也可以通XSL文件跟蹤這個轉化。確認選擇了XSL Trace Editor（XSL跟蹤編輯器），XSL Trace菜單也可用。
__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).
__18.在XSL Trace菜單，選擇“向前跟蹤”開始跟蹤轉化。也可在工具條上選擇跟蹤。
__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.
__19.單擊“向前跟蹤”數次，然後單擊是“向後跟蹤”。查看高亮的行。
__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.
__20.再次選擇customerinfo.XML 和 statement.xsl，然後是應用XSLà作為HTML。XSL Trace Editor（XSL跟蹤編輯器）會顯示Html格式的結果。
What you did in this exercise
In this exercise, you used the Data Perspective to establish a connection to an active database.
You then imported in the schema from the database into WebSphere Studio Application
Developer. Next you created a foreign key in one of the tables. Then you created a SQL
SELECT statement which returned information in XML from two tables in the active database.
Finally you used the XML to XML Mapping wizard to map the XML returned by the SQL
statement to the another XML definition.
Solution Files:
To run this lab without having run the previous labs:
1) Run the file C:yourbanksolutionslab1CfgLab1.bat
(type in into a DOS window or double-click on it.)
This will open a DB2CMD window and execute cfgdb.bat
2) Import the Server Configurations
a) File->New->Project... Server, Server Project, click Next
b) Enter the project name TestServer, click Finish.
c) File->Import. Select Zip file, click Next
d) Browse to C:YourBankSolutionsLab1TestServers.zip
e) Enter "TestServer" as your Folder name
f) Click "Finish"
*NOTE* The Test Servers imported assume default Directory structure. If you have used
different directory structure than the defaults, you will have to configure the servers
manually as found in Section 3 of Lab 1.
3) Import the EAR in Application Developer:
a) File->Import
b) Select EAR file, click NEXT
c) Browse to c:YourBankSolutionsLab2YourBankAfterLab2.ear
d) Enter the Enterprise Application Project name as YourBankEAR
e) click "Finish"
4) Right-click on YourBankWebModule in the Navigator view. Select PropertIEs,
Server Preferences, YourBankServer, click Apply, then OK.
5) Right-click on YourBankEJBModule in the Navigator view. Select PropertIEs,
Server Preferences, YourBankServer, click Apply, then OK.
6) Run the file c:YourBankLab5DBSetup.bat from a DB2 Command Window.
© Copyright IBM Corporation 2001. All rights reserved.
2001/12/20 16:46:02 IBM WebSphere Studio Application Developer Workshop - Lab Exercise APPDEVL5.lwp
Page 15 of 15