XML programming using PHP and Ajax (3)

Store policy in DB2

The PHP application stores the incoming XML document directly in the database without having to parse it (see Listing 3). DB2 's PureXML support will implicitly parse incoming XML and store it in a hierarchical hierarchy of class DOM. You can now query XML in XQuery statements using XML navigation techniques such as XPath, as used in the DOM. DB2 9 also provides the ability to index on any node in the hierarchy.

Exposing a service on an XML document

After the new policy is stored in DB2 9, the insurance agent can inquire about the policy to decide whether to accept the insurance. Queries for obtaining new policy reports are exposed to client applications through WEB services.

The Web service in this example is written in PHP, which provides a concise interface for invoking the DB2 stored procedures of the business and transformation logic that implements the service. Each DB2 stored procedure consists of a sql/xml query that filters and transforms the XML policies stored in the database to create an output XML document. The PHP Web service then returns the XML document to the client.

Let's analyze each stored procedure to see those queries that effectively compose the WEB service implementation.

List DB2 inquiries for all new customer insurance. The stored procedure that contains the query is Listallnewcustomers (see Listing 4). This query searches for all policy documents in the INFO column of the ACORD table. In each XML document, the DB2 is further parsed to return only those documents where the code value of the policystatus/@tc property is set to 12 (that is, the recommended value). The query output is an XML document that has a root node newpolicylist, which contains a series of txlife subnodes for each new policy (see Figure 4).

Figure 4. Return sql/xml query for new policy list

Notice how this query first uses the DB2 XQuery function Db2-fn:xmlcolumn to navigate the relational schema to navigate to the XML column db2admin. Acord.info.　　When it reaches an XML column, it further uses XPath to navigate to the appropriate node in the XML schema (similar to navigating the DOM using PHP, JavaScript, or other languages). List the DB2 inquiries for the proposed insurance of the risk customers. The query lists only new customers at risk (that is, they answered yes for a medical question). The query is contained in a stored procedure named Listatrisknewcustomers (see Listing 5). NOTE: The WHERE clause examines both the answer and the policy state.

DB2 queries that assess the risk of new customers at risk. For each policy in the above list, only questions that answer yes are listed in the health risk area of the policy. The query also returns Policytype to show how much the policy is worth in order to assess the risk. The stored procedure that contains the query (see Listing 6) is Getriskquestions (GUID). Note: You need a DB2 driver version that supports XML types. Otherwise, you will need to use xmlserialize in each stored procedure to serialize the XML value from the xmlquery. See developerWorks article "using DB2 native XML with PHP" For more detailed information.