Blog
DB2utor

Categories

September 30, 2008

Publishing XML Using pureXML Functions

Blogging about new DB2 features is exciting, but it can also be trying. For instance, I recently started writing a COBOL program that processes XML data. While I look forward to sharing that experience with you, I've concluded I need a little more time working on it before I'm ready to do so.

With DB2 V9 for z/OS, the entire area of XML and pureXML is a learning opportunity for me. And I like writing about new features, because I can make you aware of them while learning something new at the same. So for now, allow me to tell you about using pureXML features and SPUFI to publish an XML document.

In this example, I've stored an XML document in a table with a relational column and an XML column. The first column is the customer ID (BIGINT); the second is customer information stored as native XML. Because I didn't have source data, I hand-coded the values. (If I had source data from other tables I could have as easily built the document using data from these tables.)

With the DDL for the table and index, notice I use an SQL termination character (#) instead of a semi-colon (;). This is because the ; can be imbeded into statements, as you can see on the create index statement that follows. Here is a good occasion to start using a different termination character.

CREATE TABLE CUSTOMER      
  (Cid       BIGINT,
   INFO    XML)      
   IN dbname.tsname
   CCSID EBCDIC#

CREATE UNIQUE INDEX CUST_CID_XMLIDX ON CUSTOMER(INFO)       
GENERATE KEY USING XMLPATTERN                                  
  'declare default element namespace "http://troylcoleman.com";
  /customerinfo/@Cid' AS SQL DECFLOAT#

This is my XML document that uses pureXML functions. It's stored in the INFO column. The numbers on each line are references the SQL code that produced it.

1) <?xml version="1.0" encoding="IBM1047"?>                           
2) <customerinfo xmlns="http://troylcoleman.com" Cid="000006">   
3) <name>Maria Gonzalas</name>
4) <addr country="Mexico">            
5)          <street>Pte Masaryk n.° 153, 4er piso, Colonia Polanco</street>
6)         <city>Mexico City</city>
7) </addr>                               
8) <phone type="work">(52-55) 5094-2713</phone>
9) </customerinfo>

The SQL I used to publish this XML document is as follows:

INSERT INTO mycustomer
SELECT bigint(100006)                                        
     , XMLDOCUMENT(                                          
         XMLELEMENT(NAME "customerinfo"                      
         , XMLNAMESPACES('http://troylcoleman.com' as "info")
         , XMLATTRIBUTES(BIGINT('100006') as "cid")          
         , XMLELEMENT(NAME "name",'Maria Gonzalas')          
         , XMLELEMENT(NAME "addr"                            
           , XMLATTRIBUTES(char('Mexico') as "country")      
           , XMLELEMENT(NAME "street"                        
           ,'Pte Masaryk n. 153, 4er piso, Colonia Polanco') 
           , XMLELEMENT(NAME "city",'Mexico City')            
           )                                                 
         , XMLELEMENT(NAME "phone"                           
           , XMLATTRIBUTES(char('work')   as "type")         
           , '(52-55) 5094-2713'                              
           )                                                 
       )                                                      
     ) as xmldoc                                             
FROM sysibm.sysdummy1                                        
#                                                               

  1. I first tried to insert the XML document without wrapping it with the XMLDOCUMENT function. I received sqlcode -20345, ERROR: THE XML VALUE IS NOT A WELL-FORMED DOCUMENT WITH A SINGLE ROOT ELEMENT. So you need to wrap your published XML with the XMLDOCUMENT function to get the standard heading as seen in line 1 above.
    a. XMLDOCUMENT(
  2. Line 2 actually required a combination of function that was imbeded within the XMLELEMENT function. The XMLELEMENT function was used to define the document as customerinfo. XMLNAMESPACES was then used as the second parameter to generate the xmlns parameter. Finally, XMLATTRIBUTES was used to define the SQL identifier (cid) for customer ID. I built an index on this identifier to enforce uniqueness and speed up document processing.
    a. XMLELEMENT(NAME "customerinfo"                      
    b. XMLNAMESPACES('http://troylcoleman.com' as "info")
    c. XMLATTRIBUTES(BIGINT('100006') as "cid")
  3. The <name> tag was generated using a simple XMLELEMENT function.
    a. XMLELEMENT(NAME "name",'Maria Gonzalas')          
  4. Building the address <addr> was more complicated. I had to figure out how to identify the address to Mexico. I started with the XMLELEMENT function to define the <addr> tag. Then I imbedded XMLATTRIBUTES, giving a value of "Mexico" and, using the "as" verb, named it country. This wasn't obvious to me at first, so I'm making the point to you. There's no example of this in the user guide -- at least I couldn't find one. The next few lines define the <street> and <city> tags. I close off the first XMLELEMENT function in the group with a closing parent ")" and I now have an address identified by country and containing a street and city. Notice I don't have a ZIP Code. What's nice about XML is if I need that later on a different document, I can add it to that document without impacting this one. 
    a. XMLELEMENT(NAME "addr"
    b. XMLATTRIBUTES(char('Mexico') as "country")
    c. XMLELEMENT(NAME "street" ,
    'Pte Masaryk n. 153, 4er piso, Colonia Polanco')
    d. XMLELEMENT(NAME "city",'Mexico City')      
  5. The phone number was defined with an identifing attribute of "type." I used the XMLELEMENT function imbedded with the XMLATTRIBUTES function. As you can see from the pattern, defining a new element and giving it an identifing attribute is very easy.
    a. XMLELEMENT(NAME "phone"
    b. XMLATTRIBUTES(char('work')   as "type")
    c. , '(52-55) 5094-2713')                  

Thanks for joining me on the road to learning more about DB2 V9 pureXML. If you have any questions or suggestions, please post them.