IBM Systems Magazine, Mainframe - September/October 2010 - 51

in LOB data types, you can retrieve portions of XML data stored in a column of XML data by specifying an XPath expression through SQL with XML extensions (SQL/XML).

Code Sample 1

1) 2)

EXEC SQL DECLARE CUSTOMERS_CS CURSOR FOR SELECT BIGINT(C.CUSTOMERID) , XMLDOCUMENT( XMLELEMENT(NAME “customer” ,XMLNAMESPACES(‘http://troylcoleman.com as “info”) ,XMLATTRIBUTES(C.CUSTOMERID AS “cid”) ,XMLCOMMENT(‘CID is the Customer ID’) ,XMLELEMENT(NAME “info:name” ,XMLELEMENT(NAME “info:fi rstname” ,C.FIRSTNAME) ,XMLELEMENT(NAME “info:lastname” ,C.LASTNAME) ) ,XMLELEMENT(NAME “info:addr” ,XMLATTRIBUTES(‘USA’ AS “country”) ,XMLELEMENT(NAME “info:street” ,C.STREET_ ADDRESS) ,XMLELEMENT(NAME “info:city” , C.CITY) ,XMLELEMENT(NAME “info:state” ,C.STATE) ,XMLELEMENT(NAME “info:zip” , C.ZIP) ) ,XMLELEMENT(NAME “info:agegroupid” , C.AGEGROUPID) ,XMLELEMENT(NAME “info:income_rangeid” ,C.INCOME_RANGEID) ,XMLELEMENT(NAME “info:marital_status” ,C.MARITAL _STATUS) ,XMLELEMENT(NAME “info:gender” ,C.GENDER) ,XMLELEMENT(NAME “info:ethnicgroupid” ,C.ETHNICGROUPID) ) ) FROM CUSTOMERS C WITH UR END-EXEC.

+)%Ã9LSH[PVUHSÃ*VS\TUZÃ
Over the past few years, the need to format data stored in DB2 into an XML document has increased. A greater number of companies require transactions between business partners to be handled through standard XML document formats (purchase orders, invoices, tax forms, medical billing records, etc.). These documents can be stored in DB2 in the XML format. However, with legacy systems, the data needs to be pulled and then formatted into the appropriate XML document to be delivered to your business partner. The sample program I provided uses some of the common SQL XML functions for publishing an XML document. Each of these functions can be nested within functions to build the XML document. This simple example should give you an idea of the strength of the SQL/XML publishing functions.

3) 4) 5) 6) 7) 8) 9) 10) 11) 12) 13) 14) 15) 16) 17) 18) 19) 20) 21) 22) 23) 24) 25) 26) 27) 28) 29) 30)

*6)63ÃHUKÃ+)%Ã?43
COBOL is one of the most common languages used when developing applications to run on the mainframe with z/OS. However, most of the articles w r itten about X ML development assume Java* is the language and only show Java examples. COBOL is still alive and can be used to develop XML applications. In my example program XMLCUSTS, I’m using sample table DSN8910. CUSTOMERS and DSN8910.CUSTOMER. These tables are delivered with DB2 and they make it possible to show how relational customer data stored i n DSN8910.C US TOM E R S ca n b e formatted using SQL XML publishing functions then stored as XML in DSN8910.CUSTOMER.

31) 32) 33) 34) 35) 36) 37) 38) 39) 40)

Since these are sample tables, they may or may not be created in your system. The DB2 systems programmer can create these tables by running some install jobs found in data set DSN910.NEW.SDSNSAMP. The member (DSNTEJ3M) found in this data set is used to create and populate the CUSTOMERS table. The

member (DSNTEJ1) is used to create the CUSTOMER table. Here are the steps I took writing the COBOL program XMLCUSTS to read DB2 relational data then publish DB2 XML data. The complete code can be found online (www.ibmsystemsmag.com/ mainframe/XML_pt3_xmlcust). The logic in program XMLCUSTS is simple.

ibmsystemsmag.com /mainframe SEPTEMBER /OCTOBER 2010

51


http://www.ibmsystemsmag.com/mainframe/xml_pt3_xmlcust http://www.ibmsystemsmag.com/mainframe/xml_pt3_xmlcust http://www.ibmsystemsmag.com/mainstream

IBM Systems Magazine, Mainframe - September/October 2010

Table of Contents for the Digital Edition of IBM Systems Magazine, Mainframe - September/October 2010

IBM Systems Magazine, Mainframe - September/October 2010
Contents
Editor's Desk:  Not So Secret Sauce
Dashboard:  Find an Extra Day
Think Smarter:  Competing Takes Equal Parts Growth and Restraint
Data Display:  Privacy:  Who Do You Trust?
Insider:  Untangling the Web of Processes and Technology
Trends:  System z as the Hub of a Workload-Optimized, Business Analytics Systems
Case Study: A Merger Made Easy" EmblemHealth Saves Money By Easing into an In-Sourced Computing Model
Expanding Beyond Borders:  IBM zEnterprise Systems Announcement Delivers a New Dimension in Computing.
Reducing Complexity:  The Next-Generation System z Server Is More Than Just a Fast, Scalable Solution
Focus on Storage:  IBM Tivoli Key Lifecycle Manager Solves Security Problems and Meets New Standards
Administrator:  z/OS Management Facility V1.12 Includes New Workload-Management and Resource-Monitoring Functionality
Developer: pureXML Extends Availability and Scalability to DB2 for z/OS
Solutions
Advertisers' Index
Stop Run:  IBM Cooling Expert Roger Schmidt Says the Positioning of Machines Matters
2011 Mainframe Buyer's Guide
IBM Systems Magazine, Mainframe - September/October 2010 - IBM Systems Magazine, Mainframe - September/October 2010
IBM Systems Magazine, Mainframe - September/October 2010 - Cover2
IBM Systems Magazine, Mainframe - September/October 2010 - 1
IBM Systems Magazine, Mainframe - September/October 2010 - Contents
IBM Systems Magazine, Mainframe - September/October 2010 - 3
IBM Systems Magazine, Mainframe - September/October 2010 - 4
IBM Systems Magazine, Mainframe - September/October 2010 - 5
IBM Systems Magazine, Mainframe - September/October 2010 - 6
IBM Systems Magazine, Mainframe - September/October 2010 - 7
IBM Systems Magazine, Mainframe - September/October 2010 - 8
IBM Systems Magazine, Mainframe - September/October 2010 - 9
IBM Systems Magazine, Mainframe - September/October 2010 - Editor's Desk:  Not So Secret Sauce
IBM Systems Magazine, Mainframe - September/October 2010 - 11
IBM Systems Magazine, Mainframe - September/October 2010 - Dashboard:  Find an Extra Day
IBM Systems Magazine, Mainframe - September/October 2010 - 13
IBM Systems Magazine, Mainframe - September/October 2010 - 14
IBM Systems Magazine, Mainframe - September/October 2010 - 15
IBM Systems Magazine, Mainframe - September/October 2010 - Think Smarter:  Competing Takes Equal Parts Growth and Restraint
IBM Systems Magazine, Mainframe - September/October 2010 - 17
IBM Systems Magazine, Mainframe - September/October 2010 - 18
IBM Systems Magazine, Mainframe - September/October 2010 - 19
IBM Systems Magazine, Mainframe - September/October 2010 - Data Display:  Privacy:  Who Do You Trust?
IBM Systems Magazine, Mainframe - September/October 2010 - 21
IBM Systems Magazine, Mainframe - September/October 2010 - Insider:  Untangling the Web of Processes and Technology
IBM Systems Magazine, Mainframe - September/October 2010 - 22A
IBM Systems Magazine, Mainframe - September/October 2010 - 22B
IBM Systems Magazine, Mainframe - September/October 2010 - 23
IBM Systems Magazine, Mainframe - September/October 2010 - 24
IBM Systems Magazine, Mainframe - September/October 2010 - 25
IBM Systems Magazine, Mainframe - September/October 2010 - Trends:  System z as the Hub of a Workload-Optimized, Business Analytics Systems
IBM Systems Magazine, Mainframe - September/October 2010 - 27
IBM Systems Magazine, Mainframe - September/October 2010 - 28
IBM Systems Magazine, Mainframe - September/October 2010 - 29
IBM Systems Magazine, Mainframe - September/October 2010 - Case Study: A Merger Made Easy" EmblemHealth Saves Money By Easing into an In-Sourced Computing Model
IBM Systems Magazine, Mainframe - September/October 2010 - 31
IBM Systems Magazine, Mainframe - September/October 2010 - 32
IBM Systems Magazine, Mainframe - September/October 2010 - 33
IBM Systems Magazine, Mainframe - September/October 2010 - Expanding Beyond Borders:  IBM zEnterprise Systems Announcement Delivers a New Dimension in Computing.
IBM Systems Magazine, Mainframe - September/October 2010 - 35
IBM Systems Magazine, Mainframe - September/October 2010 - 36
IBM Systems Magazine, Mainframe - September/October 2010 - 37
IBM Systems Magazine, Mainframe - September/October 2010 - Reducing Complexity:  The Next-Generation System z Server Is More Than Just a Fast, Scalable Solution
IBM Systems Magazine, Mainframe - September/October 2010 - 39
IBM Systems Magazine, Mainframe - September/October 2010 - 40
IBM Systems Magazine, Mainframe - September/October 2010 - 41
IBM Systems Magazine, Mainframe - September/October 2010 - Focus on Storage:  IBM Tivoli Key Lifecycle Manager Solves Security Problems and Meets New Standards
IBM Systems Magazine, Mainframe - September/October 2010 - 43
IBM Systems Magazine, Mainframe - September/October 2010 - 44
IBM Systems Magazine, Mainframe - September/October 2010 - 45
IBM Systems Magazine, Mainframe - September/October 2010 - Administrator:  z/OS Management Facility V1.12 Includes New Workload-Management and Resource-Monitoring Functionality
IBM Systems Magazine, Mainframe - September/October 2010 - 47
IBM Systems Magazine, Mainframe - September/October 2010 - 48
IBM Systems Magazine, Mainframe - September/October 2010 - 49
IBM Systems Magazine, Mainframe - September/October 2010 - Developer: pureXML Extends Availability and Scalability to DB2 for z/OS
IBM Systems Magazine, Mainframe - September/October 2010 - 51
IBM Systems Magazine, Mainframe - September/October 2010 - 52
IBM Systems Magazine, Mainframe - September/October 2010 - 53
IBM Systems Magazine, Mainframe - September/October 2010 - Solutions
IBM Systems Magazine, Mainframe - September/October 2010 - Advertisers' Index
IBM Systems Magazine, Mainframe - September/October 2010 - Stop Run:  IBM Cooling Expert Roger Schmidt Says the Positioning of Machines Matters
IBM Systems Magazine, Mainframe - September/October 2010 - Cover3
IBM Systems Magazine, Mainframe - September/October 2010 - Cover4
IBM Systems Magazine, Mainframe - September/October 2010 - 2011 Mainframe Buyer's Guide
IBM Systems Magazine, Mainframe - September/October 2010 - BG-2
IBM Systems Magazine, Mainframe - September/October 2010 - BG-3
IBM Systems Magazine, Mainframe - September/October 2010 - BG-4
IBM Systems Magazine, Mainframe - September/October 2010 - BG-5
IBM Systems Magazine, Mainframe - September/October 2010 - FBG-6
IBM Systems Magazine, Mainframe - September/October 2010 - BG-7
IBM Systems Magazine, Mainframe - September/October 2010 - BG-8
IBM Systems Magazine, Mainframe - September/October 2010 - BG-9
IBM Systems Magazine, Mainframe - September/October 2010 - BG-10
IBM Systems Magazine, Mainframe - September/October 2010 - BG-11
IBM Systems Magazine, Mainframe - September/October 2010 - BG-12
IBM Systems Magazine, Mainframe - September/October 2010 - BG-13
IBM Systems Magazine, Mainframe - September/October 2010 - BG-14
IBM Systems Magazine, Mainframe - September/October 2010 - BG-15
IBM Systems Magazine, Mainframe - September/October 2010 - BG-16
IBM Systems Magazine, Mainframe - September/October 2010 - BG-17
IBM Systems Magazine, Mainframe - September/October 2010 - BG-18
IBM Systems Magazine, Mainframe - September/October 2010 - BG-19
IBM Systems Magazine, Mainframe - September/October 2010 - BG-20
IBM Systems Magazine, Mainframe - September/October 2010 - BG-21
IBM Systems Magazine, Mainframe - September/October 2010 - BG-22
IBM Systems Magazine, Mainframe - September/October 2010 - BG-23
IBM Systems Magazine, Mainframe - September/October 2010 - BG-24
IBM Systems Magazine, Mainframe - September/October 2010 - BG-25
IBM Systems Magazine, Mainframe - September/October 2010 - BG-26
IBM Systems Magazine, Mainframe - September/October 2010 - BG-27
IBM Systems Magazine, Mainframe - September/October 2010 - BG-28
IBM Systems Magazine, Mainframe - September/October 2010 - BG-29
IBM Systems Magazine, Mainframe - September/October 2010 - BG-30
IBM Systems Magazine, Mainframe - September/October 2010 - BG-31
IBM Systems Magazine, Mainframe - September/October 2010 - BG-32
IBM Systems Magazine, Mainframe - September/October 2010 - 95
http://www.ibmsystemsmagmainframedigital.com/mspcomm/ibmsystemsmag/ibmsystems_mainframe_20200910
http://www.ibmsystemsmagmainframedigital.com/mspcomm/ibmsystemsmag/ibmsystems_mainframe_20200708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20200506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20200304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20200102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/2020mfse
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20191112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/relevantz_20190102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/2019mfse
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20181112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/2018mfse
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20171112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_sesupp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_linuxsupp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20161112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/MainframeSecurity
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20151112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150910_se
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150910
http://www.ibmsystemsmagmainframedigital.com/MFSkills
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150506_supp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20141112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_gt_201405
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/BigData
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20131112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20121112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/buyersguide2013
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/ibmsystems_mainframe_2012bg
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20111112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20101112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100910_bg
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20091112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090506
https://www.nxtbook.com/nxtbooks/ibmsystemsmag/mainframe_20090304
https://www.nxtbookmedia.com