XML Guide

XML User Guide

Informatica PowerCenter\u00ae

(Version 7.1.1)

Informatica PowerCenter XML User Guide Version 7.1.1 August 2004 Copyright (c) 1998\u20132004 Informatica Corporation. All rights reserved. Printed in the USA.

This software and documentation contain proprietary information of Informatica Corporation, they are provided under a license agr containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibite part of this document may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording or otherwis without prior consent of Informatica Corporation.

Use, duplication, or disclosure of the Software by the U.S. Government is subject to the restrictions set forth in the applicable softwa license agreement as provided in DFARS 227.7202-1(a) and 227.7702-3(a) (1995), DFARS 252. 227-7013(c)(1)(ii) (OCT 1988), FAR 12.212(a) (1995), FAR 52.227-19, or FAR 52.227-14 (ALT III), as applicable.

The information in this document is subject to change without notice. If you find any problems in the documentation, please repo us in writing. Informatica Corporation does not warrant that this documentation is error free. Informatica, PowerMart, PowerCenter, PowerChannel, PowerCenter Connect, MX, and SuperGlue are trademarks or registered of Informatica Corporation in the United States and in jurisdictions throughout the world. All other company and product name trade names or trademarks of their respective owners. Portions of this software are copyrighted by DataDirect Technologies, 1999-2002.

Informatica PowerCenter products contain ACE (TM) software copyrighted by Douglas C. Schmidt and his research group at Wa University and University of California, Irvine, Copyright (c) 1993-2002, all rights reserved.

Portions of this software contain copyrighted material from The JBoss Group, LLC. Your right to use such materials is set forth in the Lesser General Public License Agreement, which may be found at http://www.opensource.org/licenses/lgpl-license.php. The JBoss m are provided free of charge by Informatica, \u201cas-is\u201d, without warranty of any kind, either express or implied, including bu implied warranties of merchantability and fitness for a particular purpose.

Portions of this software contain copyrighted material from Meta Integration Technology, Inc. Meta Integration\u00ae is a regist of Meta Integration Technology, Inc. This product includes software developed by the Apache Software Foundation (http://www.apache.org/). The Apache Software is Copyright (c) 1999-2004 The Apache Software Foundation. All rights reserved.

DISCLAIMER: Informatica Corporation provides this documentation \u201cas is\u201d without warranty of any kind, either express or implied, including, but not limited to, the implied warranties of non-infringement, merchantability, or use for a particular purpose. The inform provided in this documentation may include technical inaccuracies or typographical errors. Informatica could make improvements a changes in the products described in this documentation at any time without notice.

Table of Contents List of Figures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix List of Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii New Features and Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv PowerCenter 7.1.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv PowerCenter 7.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi PowerCenter 7.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx About Informatica Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvi About this Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii Document Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii Other Informatica Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxviii Visiting Informatica Customer Portal . . . . . . . . . . . . . . . . . . . . . . . . xxviii Visiting the Informatica Webzine . . . . . . . . . . . . . . . . . . . . . . . . . . . xxviii Visiting the Informatica Web Site . . . . . . . . . . . . . . . . . . . . . . . . . . xxviii Visiting the Informatica Developer Network . . . . . . . . . . . . . . . . . . . xxviii Obtaining Technical Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix

Chapter 1: XML Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 XML Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 DTD Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 DTD Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 DTD Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 XML Schema Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Types of XML Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Namespace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Null Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Simple and Complex XML Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Simple Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Complex Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Component Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Element and Attribute Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Substitution Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 XML Path . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Code Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Chapter 2: Using XML with PowerCenter . . . . . . . . . . . . . . . . . . . . . . 29 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Extracting XML Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Metadata from an XML File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Metadata From DTD Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Metadata from an XML Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Metadata from Relational Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Working with XML Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Rules for View Keys and Relationships . . . . . . . . . . . . . . . . . . . . . . . . . 36 Creating Custom XML Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Generating Hierarchical View Relationships . . . . . . . . . . . . . . . . . . . . . . . . 38 Generating Normalized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Generating a Denormalized View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Generating Entity View Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Rules and Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Using Entity Relationships in an XML Definition . . . . . . . . . . . . . . . . . 42 Using Substitution Groups in an XML Definition . . . . . . . . . . . . . . . . . 45 Working with Circular References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Understanding View Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Pivoting Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Using Multiple-Level Pivots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Chapter 3: Working with XML Sources . . . . . . . . . . . . . . . . . . . . . . . 55 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Importing an XML Source Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 Multi-line Attributes Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Working with XML Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Generating Entity Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Generating Hierarchy Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Creating Custom XML Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Selecting Root Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Reducing Metadata Explosion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Synchronizing XML Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Editing XML Source Definition Properties . . . . . . . . . . . . . . . . . . . . . . . . . 70 Creating XML Definitions from Repository Definitions . . . . . . . . . . . . . . . 72 Tr oubleshooting XML Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Chapter 4: Using the XML Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 XML Navigator Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Workspace Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Columns Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Creating and Editing Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Creating an XML View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Adding Columns to Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Adding Pivoted Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Applying Content to the Any Content Element . . . . . . . . . . . . . . . . . . . 85 Deleting Columns from Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Adding a Pass-Through Port . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Adding a FileName Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Maintaining View Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Creating Relationships Between Views . . . . . . . . . . . . . . . . . . . . . . . . . 89 Creating Type Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Recreating Entity Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Viewing Schema Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Updating the Namespace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Sorting Components in the Components Pane . . . . . . . . . . . . . . . . . . . 94 Navigating to Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Arranging Views in the Workspace . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Searching for Components in the Schema . . . . . . . . . . . . . . . . . . . . . . . 95 Searching for Components in XML Views . . . . . . . . . . . . . . . . . . . . . . 95 Viewing a Simple Type Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Viewing Complex Types in the Schema . . . . . . . . . . . . . . . . . . . . . . . . 96 Viewing XML Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 Previewing XML Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

Validating XML Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Setting XML View Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 All Hierarchy Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Non-Recursive Row Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Hierarchy Relationship Row Option . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Force Row Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Type Relationship Row Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Tr oubleshooting XML Editing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Chapter 5: Working with XML Targets . . . . . . . . . . . . . . . . . . . . . . . 103 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Importing an XML Ta rget Definition from XML Files . . . . . . . . . . . . . . . . 105 Creating a Ta rget from an XML Source Definition . . . . . . . . . . . . . . . . . . 106 Editing XML Ta rget Definition Properties . . . . . . . . . . . . . . . . . . . . . . . . 107 Validating XML Ta rgets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Hierarchy Relationship Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Type Relationship Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Inheritance Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Using an XML Ta rget in a Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Active Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Selecting a Root Element . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Connecting Ta rget Ports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Connecting Abstract Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Flushing XML Data to Ta rgets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Naming XML Files Dynamically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Tr oubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Chapter 6: XML Source Qualifier Transformation . . . . . . . . . . . . . . 121 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 Adding an XML Source Qualifier to a Mapping . . . . . . . . . . . . . . . . . . . . . 123 Automatically Creating an XML Source Qualifier Tr ansformation . . . . 123 Manually Creating an XML Source Qualifier Tr ansformation . . . . . . . . 123 Editing an XML Source Qualifier Tr ansformation . . . . . . . . . . . . . . . . . . . 125 Setting Sequence Numbers for Generated Keys . . . . . . . . . . . . . . . . . . 128 Using the XML Source Qualifier in a Mapping . . . . . . . . . . . . . . . . . . . . . 129 XML Source Qualifier Tr ansformation Example . . . . . . . . . . . . . . . . . 131 Tr oubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135

Chapter 7: Midstream XML Transformations . . . . . . . . . . . . . . . . . . 137 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 XML Parser Tr ansformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 XML Generator Tr ansformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 Creating a Midstream XML Tr ansformation . . . . . . . . . . . . . . . . . . . . . . . 143 Editing Midstream XML Tr ansformation Properties . . . . . . . . . . . . . . . . . 144 Midstream XML Parser Ta b . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Midstream XML Generator Ta b . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 Generating Pass-Through Ports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

Chapter 8: Working with XML Sessions . . . . . . . . . . . . . . . . . . . . . . 151 Working with XML Sources in a Session . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Server Handling for XML Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Working with XML Ta rgets in a Session . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Server Handling for XML Ta rgets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 Character Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 Special Characters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Null and Empty String . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Handling Duplicate Group Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 DTD and Schema Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 Flushing XML on Commits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 XML Caching Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 Session Logs for XML Ta rgets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 Multiple XML Document Output . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Working with Midstream XML Tr ansformations . . . . . . . . . . . . . . . . . . . . 165

Appendix A: XML Datatype Reference . . . . . . . . . . . . . . . . . . . . . . . 169 XML and Tr ansformation Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 Unsupported Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 XML Date Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

List of Figures

Figure 1-1. Sample XML File: StoreInfo.xml . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Figure 1-2. Elements in the XML Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Figure 1-3. Sample DTD: StoreInfo.dtd . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Figure 1-4. Sample XML Schema File: StoreInfo.xsd . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Figure 1-5. XML Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Figure 1-6. Relative Cardinality of Elements in StoreInfo.xml . . . . . . . . . . . . . . . . . . . . . . . . Figure 1-7. Sample XSD File with Union Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Figure 1-8. Restricted and Extended Complex Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Figure 1-9. XPath of Elements and Attributes in the StoreInfo.xml . . . . . . . . . . . . . . . . . . . . Figure 2-1. A Sample Employees XML File with Multiple-Occurring Elements . . . . . . . . . . . Figure 2-2. The Root Element and XML Views in an XML Definition . . . . . . . . . . . . . . . . . . 32 Figure 2-3. XML Definition From an XML File Referencing a DTD File . . . . . . . . . . . . . . . . 33 Figure 2-4. XML Schema with a Derived Complex Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Figure 2-5. XML Definition Containing a Derived Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Figure 2-6. XML Target Created from Two Relational Sources . . . . . . . . . . . . . . . . . . . . . . . 35 Figure 2-7. Sample DTD File to Create Normalized Views . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Figure 2-8. Source Definition for StoreInfo.dtd . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Figure 2-9. Normalized Views Data Preview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Figure 2-10. Sample XML File to Create a Denormalized View . . . . . . . . . . . . . . . . . . . . . . . 40 Figure 2-11. Source Definition Containing a Denormalized View . . . . . . . . . . . . . . . . . . . . . 40 Figure 2-12. Data Preview for the ProdAndSales.xml Denormalized View . . . . . . . . . . . . . . . Figure 2-13. Complex Type View Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Figure 2-14. Complex Type XML Views Sample Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Figure 2-15. Sample XML Schema Using Substitution Groups . . . . . . . . . . . . . . . . . . . . . . . . Figure 2-16. Sample XML Definition Generated from Substitution Groups . . . . . . . . . . . . . . Figure 2-17. Circular Reference View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Figure 2-18. Circular Reference Data Preview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Figure 2-19. ADDRESS Element Occurrences Pivoted into Columns . . . . . . . . . . . . . . . . . . . Figure 3-1. The XML Wizard Options to Create Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Figure 3-2. Root Selection Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Figure 3-3. Reduce Metadata Explosions Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Figure 4-1. XML Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Figure 4-2. Pivoted Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Figure 4-3. Specify XPath Pivot Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Figure 4-4. Any Content Element in the Schema Navigator . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Figure 4-5. Edit Any or Any Attribute Dialog Box . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Figure 4-6. New Type Added to the Any Content Element . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Figure 4-7. Search XML Views and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Figure 4-8. Preview XML Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Figure 4-9. Columns Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

Figure 5-1. Filename Column in a Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Figure 6-1. XML Source Qualifier Transformation Ports . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Figure 6-2. Linking XML Source Qualifier Transformations to One Input Group . . . . . . . . . . 129 Figure 6-3. Linking XML Source Qualifier to Multiple Input Group Transformations . . . . . . 130 Figure 6-4. Sample XML File StoreInfo.xml . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Figure 6-5. Invalid use of XML Source Qualifier Transformation in Aggregator Mapping . . . . 132 Figure 6-6. Using a Denormalized Group in a Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Figure 6-7. Using an XML Source Definition Twice in a Mapping . . . . . . . . . . . . . . . . . . . . . 134 Figure 7-1. XML Parser Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Figure 7-2. XML Generator Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 Figure 7-3. Sample XML Generator Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Figure 7-4. Midstream XML Parser Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 Figure 7-5. Midstream XML Generator Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 Figure 7-6. Pass-Through Ports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Figure 8-1. Properties Settings for an XML Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Figure 8-2. Properties Settings for an XML Writer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Figure 8-3. Mapping Data to an XML Root . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Figure 8-4. Properties Settings for an XML Generator Transformation . . . . . . . . . . . . . . . . . . 165 Figure 8-5. Properties Settings for an XML Parser Transformation . . . . . . . . . . . . . . . . . . . . . 167

List of Tables

Table 1-1. Cardinality of Elements in XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Table 3-1. Create XML Views Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Table 6-1. XML Source Qualifier Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Table 7-1. Midstream XML Parser Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 Table 7-2. Midstream XML Generator Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 Table 8-1. XML Reader Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Table 8-2. XML Source Qualifier Options for a Session . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Table 8-3. XML Writer Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Table 8-4. Null and Empty String Output for XML Targets . . . . . . . . . . . . . . . . . . . . . . . . . 159 Table 8-5. XML Generator Transformation Session Options . . . . . . . . . . . . . . . . . . . . . . . . . 165 Table 8-6. XML Parser Transformation Session Options . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Table A-1. XML and Transformation Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170

Preface

Welcome to PowerCenter, Informatica’s software product that delivers an open, sc integration solution addressing the complete life cycle for all data integration pro including data warehouses and data marts, data migration, data synchronization, information hubs. PowerCenter combines the latest technology enhancements for managing data repositories and delivering information resources in a timely, usab efficient manner.

The PowerCenter metadata repository coordinates and drives a variety of core fu including extracting, transforming, loading, and managing data. The PowerCenter S extract large volumes of data from multiple platforms, handle complex transformatio data, and support high-speed loads. PowerCenter can simplify and accelerate the pro moving data warehouses from development to test to production.

New Features and Enhancements

This section describes new features and enhancements to PowerCenter 7.1.1, 7.1, and 7

PowerCenter 7.1.1 This section describes new features and enhancements to PowerCenter 7.1.1.

Data Profiling ♦

♦

♦

♦

♦

♦

♦

Data sampling. You can create a data profile for a sample of source data entire source. You can view a profile from a random sample of data, a specified percen of data, or for a specified number of rows starting with the first row.

Verbose data enhancements. You can specify the type of verbose data you PowerCenter Server to write to the Data Profiling warehouse. The PowerCenter Server write all rows, the rows that meet the business rule, or the rows that do not meet the business rule. Session enhancement. the repository.

You can save sessions that you create from the Profile

Domain Inference function tuning . You can configure the Data Profiling Wizard the Domain Inference function results. You can configure a maximum number of patterns and a minimum pattern frequency. You may want to narrow the scope of patterns returned to view only the primary domains, or you may want to widen the scope of patterns returned to view exception data.

Row Uniqueness function. You can determine unique rows for a source based selection of columns for the specified source.

Define mapping, session, and workflow prefixes . You can define default mappi session, and workflow prefixes for the mappings, sessions, and workflows generated w you create a data profile. Profile mapping display in the Designer. profile mappings node in the Navigator.

The Designer displays profile mapping

PowerCenter Server ♦

♦

♦

Code page. PowerCenter supports additional Japanese language code pages, kana, JEF-kana, and MELCOM-kana.

Flat file partitioning. When you create multiple partitions for a flat file source can configure the session to create multiple threads to read the flat file source. pmcmd. You can use parameter files that reside on a local machine with command in the pmcmd program. When you use a local parameter file, pmcmd passes variables and values in the file to the PowerCenter Server.

♦

♦

♦

SuSE Linux support. The PowerCenter Server runs on SuSE Linux. On can connect to IBM, DB2, Oracle, and Sybase sources, targets, and repositorie native drivers. Use ODBC drivers to access other sources and targets.

Reserved word support. If any source, target, or lookup table name contains a database reserved word, you can create and maintain a file, resword containing reserved words. When the PowerCenter Server initializes a session, for reswords.txt in the PowerCenter Server installation directory. If the file exi PowerCenter Server places quotes around matching reserved words when it ex against the database.

Teradata external loader. When you load to Teradata using an external now override the control file. Depending on the loader you use, you can also overr error, log, and work table names by specifying different tables on the same or diff Teradata database.

Repository ♦

Exchange metadata with other tools. You can exchange source and tar other BI or data modeling tools, such as Business Objects Designer. You can expor import multiple objects at a time. When you export metadata, the PowerCenter Cl creates a file format recognized by the target tool.

Repository Server ♦

pmrep. − −

− ♦

You can use pmrep to perform the following functions:

Remove repositories from the Repository Server cache entry list.

Enable enhanced security when you create a relational source or targ repository.

Update a connection attribute value when you update the connection

SuSE Linux support. The Repository Server runs on SuSE Linux. On can connect to IBM, DB2, Oracle, and Sybase repositories.

Security ♦

Oracle OS Authentication. You can now use Oracle OS Authentication database users. Oracle OS Authentication allows you to log on to an Oracle databa have a logon to the operating system. You do not need to know a database user nam password. PowerCenter uses Oracle OS Authentication when the user name for an connection is PmNullUser.

Web Services Provider ♦

Attachment support. When you import web service definitions with atta you can pass attachments through the requests or responses in a service sessio document type you can attach is based on the mime content of the WSDL file. Yo attach document types such as XML, JPEG, GIF, or PDF.

♦

Pipeline partitioning. You can create multiple partitions in a session containing service source and target definitions. The PowerCenter Server creates a connection to Web Services Hub based on the number of sources, targets, and partitions in the sess

XML ♦

Multi-level pivoting. You can now pivot more than one multiple-occurring elem XML view. You can also pivot the view row.

PowerCenter 7.1 This section describes new features and enhancements to PowerCenter 7.1.

Data Profiling ♦ ♦

♦ ♦

♦

♦

♦

♦

Data Profiling for VSAM sources.

You can now create a data profile for VSAM

Support for verbose mode for source-level functions. You can now create dat with source-level functions and write data to the Data Profiling warehouse in verbose mode. Aggregator function in auto profiles.

Auto profiles now include the Aggregator

Creating auto profile enhancements. You can now select the columns or grou to include in an auto profile and enable verbose mode for the Distinct Value Count function. Purging data from the Data Profiling warehouse. Profiling warehouse. Source View in the Profile Manager. in the Profile Manager.

You can now purge data fro

You can now view data profiles by sour

PowerCenter Data Profiling report enhancements. You can now view PowerCent Profiling reports in a separate browser window, resize columns in a report, and view verbose data for Distinct Value Count functions.

Prepackaged domains. Informatica provides a set of prepackaged domains tha include in a Domain Validation function in a data profile.

Documentation ♦

♦

Web Services Provider Guide. This is a new book that describes the functionali Web Services. It also includes information from the version 7.0 We b Services Hub Guide .

XML User Guide. This book consolidates XML information previously documente Designer Guide, Workflow Administration Guide , and Transformation Guide.

Licensing

Informatica provides licenses for each CPU and each repository rather than for each installation. Informatica provides licenses for product, connectivity, and options. You sto

the license keys in a license key file. You can manage the license files using the R Server Administration Console, the PowerCenter Server Setup, and the command program, pmlic.

PowerCenter Server ♦

♦

♦

♦

64-bit support. (Itanium).

You can now run 64-bit PowerCenter Servers on AIX

Partitioning enhancements. If you have the Partitioning option, you can partitions at any partition point in a pipeline that supports multiple partitions.

PowerCenter Server processing enhancements. The PowerCenter Server block of rows at a time. This improves processing performance for most session CLOB/BLOB datatype support.

You can now read and write CLOB/BLOB

PowerCenter Metadata Reporter

PowerCenter Metadata Reporter modified some report names and uses the Power MX views in its schema.

Repository Server ♦

♦

♦

Updating repository statistics . PowerCenter now identifies and updates repository tables and indexes when you copy, upgrade, and restore repositories improves performance when PowerCenter accesses the repository.

Increased repository performance . You can increase repository performan information when you copy, back up, or restore a repository. You can choose to data, workflow and session log history, and deploy group history.

pmrep. You can use pmrep to back up, disable, or enable a repository connection from a repository, delete repository details, truncate log files, and ru pmrep commands sequentially. You can also use pmrep to create, modify, and delete a folder.

Repository ♦

♦

♦

Exchange metadata with business intelligence tools. You can export me import metadata from other business intelligence tools, such as Cognos Report Business Objects. Object import and export enhancements. You can compare objects in objects in the target repository when you import objects.

MX views. MX views have been added to help you analyze metadata repository. REP_SERVER_NET and REP_SERVER_NET_REF views allow you to se information about server grids. REP_VERSION_PROPS allows you to see the versi history of all objects in a PowerCenter repository.

Transformations ♦

♦

♦

♦

♦

Flat file lookup. You can now perform lookups on flat files. When you create transformation using a flat file as a lookup source, the Designer invokes the Flat File Wizard. You can also use a lookup file parameter if you want to change the name or location of a lookup between session runs.

Dynamic lookup cache enhancements. When you use a dynamic lookup cache PowerCenter Server can ignore some ports when it compares values in lookup and input ports before it updates a row in the cache. Also, you can choose whether the PowerCenter Server outputs old or new values from the lookup/output ports when it updates a row. You might want to output old values from lookup/output ports when you use the Lookup transformation in a mapping that updates slowly changing dimension tables.

Union transformation. You can use the Union transformation to merge multipl into a single pipeline. The Union transformation is similar to using the UNION ALL SQ statement to combine the results from two or more SQL statements.

Custom transformation API enhancements. The Custom transformation API inclu new array-based functions that allow you to create procedure code that receives and outputs a block of rows at a time. Use these functions to take advantage of the PowerCenter Server processing enhancements.

Midstream XML transformations. You can now create an XML Parser transform an XML Generator transformation to parse or generate XML inside a pipeline. The XM transformations enable you to extract XML data stored in relational tables, such as da stored in a CLOB column. You can also extract data from messaging systems, such as TIBCO or IBM MQSeries.

Usability ♦

♦

Viewing active folders. folder in the Navigator. Enhanced printing.

The Designer and the Workflow Manager highlight the

The quality of printed workspace has improved.

Version Control

You can run object queries that return shortcut objects. You can also run object queries base on the latest status of an object. The query can return local objects that are checked out, the latest version of checked in objects, or a collection of all older versions of objects.

Web Services Provider ♦

♦

Real-time Web Services. Real-time Web Services allows you to create services Workflow Manager and make them available to web service clients through the Web Services Hub. The PowerCenter Server can perform parallel processing of both reque response and one-way services.

Web Services Hub. The Web Services Hub now hosts Real-time Web Services to Metadata Web Services and Batch Web Services. You can install the Web Services H on a JBoss application server.

Note: PowerCenter Connect for Web Services allows you to create sources, targets, and

transformations to call web services hosted by other providers. For more informa PowerCenter Connect for Web Services User and Administrator Guide .

Workflow Monitor

The Workflow Monitor includes the following performance and usability enhancem ♦

♦ ♦

♦

♦

♦

When you connect to the PowerCenter Server, you no longer distinguis or offline mode. You can open multiple instances of the Workflow Monitor on one mac You can simultaneously monitor multiple PowerCenter Servers registered repository. The Workflow Monitor includes improved options for filtering tasks by time.

The Workflow Monitor displays workflow runs in Task view chronological recent run at the top. It displays folders alphabetically. You can remove the Navigator and Output window.

XML Support PowerCenter XML support now includes the following features: ♦

♦

♦

♦

♦

Enhanced datatype support. datatypes.

You can use XML schemas that contain

Additional options for XML definitions. When you import XML definitions choose how you want the Designer to represent the metadata associated with the files. You can choose to generate XML views using hierarchy or entity relationship view with hierarchy relationships, the Designer expands each element and refere its parent element. When you create views with entity relationships, the Designer separate entities for references and multiple-occurring elements.

Synchronizing XML definitions. You can synchronize one or more XML the underlying schema changes. You can synchronize an XML definition with an repository definition or file used to create the XML definition, including relation or targets, XML files, DTD files, or schema files.

XML workspace. You can edit XML views and relationships between vie workspace. You can create views, add or delete columns from views, and define relationships between views.

Midstream XML transformations. You can now create an XML Parser tra an XML Generator transformation to parse or generate XML inside a pipeline. transformations enable you to extract XML data stored in relational tables, suc stored in a CLOB column. You can also extract data from messaging systems, s TIBCO or IBM MQSeries.

♦

♦

Support for circular references. Circular references occur when an element indirect child of itself. PowerCenter now supports XML files, DTD files, and XML schemas that use circular definitions.

Increased performance for large XML targets. You can create XML files of se gigabytes in a PowerCenter 7.1 XML session by using the following enhancements: −

− −

Spill to disk. You can specify the size of the cache used to store the XM of the tree exceeds the cache size, the XML data spills to disk in order to free up memory. User-defined commits.

You can define commits to trigger flushes for XML

Support for multiple XML output files. You can output XML data to multiple targets. You can also define the file names for XML output files in the mapping.

PowerCenter 7.0 This section describes new features and enhancements to PowerCenter 7.0.

Data Profiling

If you have the Data Profiling option, you can profile source data to evaluate source data detect patterns and exceptions. For example, you can determine implicit data type, sugg candidate keys, detect data patterns, and evaluate join criteria. After you create a profili warehouse, you can create profiling mappings and run sessions. Then you can view repo based on the profile data in the profiling warehouse.

The PowerCenter Client provides a Profile Manager and a Profile Wizard to complete the tasks.

Data Integration Web Services

You can use Data Integration Web Services to write applications to communicate with th PowerCenter Server. Data Integration Web Services is a web-enabled version of the PowerCenter Server functionality available through Load Manager and Metadata Exchange is comprised of two services for communication with the PowerCenter Server, Load Manage and Metadata Exchange Web Services running on the Web Services Hub.

Documentation ♦

♦

♦

Glossary. terms.

The

Installation and Configuration Guide contains a glossary of new

Installation and Configuration Guide. The connectivity information in the Insta and Configuration Guide is consolidated into two chapters. This book now contains chapters titled “Connecting to Databases from Windows” and “Connecting to Databas from UNIX.”

Upgrading metadata. The Installation and Configuration Guide now contains titled “Upgrading Repository Metadata.” This chapter describes changes to repository

objects impacted by the upgrade process. The change in functionality for existi depends on the version of the existing objects. Consult the upgrade information chapter for each upgraded object to determine whether the upgrade applies to y version of PowerCenter.

Functions ♦

♦

Soundex. The Soundex function encodes a string value into a four-cha SOUNDEX works for characters in the English alphabet (A-Z). It uses the first of the input string as the first character in the return value and encodes the re three unique consonants as numbers.

Metaphone. The Metaphone function encodes string values. You can spe the string that you want to encode. METAPHONE encodes characters of the En language alphabet (A-Z). It encodes both uppercase and lowercase letters in up

Installation ♦

Remote PowerCenter Client installation. You can create a control file installation information, and distribute it to other users to install the PowerCente You access the Informatica installation CD from the command line to create the co file and install the product.

PowerCenter Metadata Reporter

PowerCenter Metadata Reporter replaces Runtime Metadata Reporter and Inform Metadata Reporter. PowerCenter Metadata Reporter includes the following featu ♦

♦

Metadata browsing. You can use PowerCenter Metadata Reporter to bro 7.0 metadata, such as workflows, worklets, mappings, source and target tables transformations.

Metadata analysis. You can use PowerCenter Metadata Reporter to anal metadata, including session load time, server load, session completion status, s errors, and warehouse growth.

PowerCenter Server ♦ ♦

♦

♦

DB2 bulk loading.

You can enable bulk loading when you load to IBM

Distributed processing. If you purchase the Server Grid option, you can PowerCenter Servers registered to the same repository into a server grid. In a PowerCenter Servers balance the workload among all the servers in the grid.

Row error logging. The session configuration object has new properties define error logging. You can choose to log row errors in a central location to h understand the cause and source of errors.

External loading enhancements. When using external loaders on Window choose to load from a named pipe. When using external loaders on UNIX, you c choose to load from staged files.

♦

♦

♦

♦

External loading using Teradata Warehouse Builder. You can use Teradata Ware Builder to load to Teradata. You can choose to insert, update, upsert, or delete data. Additionally, Teradata Warehouse Builder can simultaneously read from multiple sourc and load data into one or more tables.

Mixed mode processing for Teradata external loaders. You can now use data mode with Teradata external loaders. When you select data driven loading, the PowerCenter Server flags rows for insert, delete, or update. It writes a column in the t file or named pipe to indicate the update strategy. The control file uses these values to determine how to load data to the target.

Concurrent processing. The PowerCenter Server now reads data concurrently sources within a target load order group. This enables more efficient joins with minim usage of memory and disk cache.

Real time processing enhancements. You can now use real-time processing in also process active transformations, such as the Aggregator transformation. You can a the transformation logic to rows defined by transaction boundaries.

Repository Server ♦

♦

♦

Object export and import enhancements. You can now export and import obj the Repository Manager and pmrep. You can export and import multiple objects and objects types. You can export and import objects with or without their dependent obje You can also export objects from a query result or objects history.

pmrep commands. You can use pmrep to perform change management tasks, maintaining deployment groups and labels, checking in, deploying, importing, exporti and listing objects. You can also use pmrep to run queries. The deployment and object import commands require you to use a control file to define options and resolve confli

Trusted connections. You can now use a Microsoft SQL Server trusted connec connect to the repository.

Security ♦

♦

♦

LDAP user authentication. You can now use default repository user authentica Lightweight Directory Access Protocol (LDAP) to authenticate users. If you use LDAP, t repository maintains an association between your repository user name and your externa login name. When you log in to the repository, the security module passes your login name to the external directory for authentication. The repository maintains a status for each user. You can now enable or disable users from accessing the repository by changing the status. You do not have to delete user names from the repository.

Use Repository Manager privilege. The Use Repository Manager privilege allow perform tasks in the Repository Manager, such as copy object, maintain labels, and cha object status. You can perform the same tasks in the Designer and Workflow Manager you have the Use Designer and Use Workflow Manager privileges.

Audit trail. You can track changes to repository users, groups, privileges, and through the Repository Server Administration Console. The Repository Agent logs security changes to a log file stored in the Repository Server installation directory. Th

audit trail log contains information, such as changes to folder properties, addin removing a user or group, and adding or removing privileges.

Transformations ♦

Custom transformation. Custom transformations operate in conjunction you create outside of the Designer interface to extend PowerCenter functionali Custom transformation replaces the Advanced External Procedure transformati create Custom transformations with multiple input and output groups, and you compile the procedure with any C compiler.

You can create templates that customize the appearance and available properti Custom transformation you develop. You can specify the icons used for transfor the colors, and the properties a mapping developer can modify. When you create transformation template, distribute the template with the DLL or shared librar develop. ♦

Joiner transformation. You can use the Joiner transformation to join two originate from the same source.

Version Control

The PowerCenter Client and repository introduce features that allow you to create manage multiple versions of objects in the repository. Version control allows you t multiple versions of an object, control development on the object, track changes, deployment groups to copy specific groups of objects from one repository to anoth control in PowerCenter includes the following features: ♦

♦

♦

♦

♦

Object versioning. Individual objects in the repository are now versioned to store multiple copies of a given object during the development cycle. Each v separate object with unique properties.

Check out and check in versioned objects. You can check out and res want to edit, and check in the object when you are ready to create a new versio object in the repository.

Compare objects. The Repository Manager and Workflow Manager allow two repository objects of the same type to identify differences between them. Y compare Designer objects and Workflow Manager objects in the Repository Ma can compare tasks, sessions, worklets, and workflows in the Workflow Manager PowerCenter Client tools allow you to compare objects across open folders and repositories. You can also compare different versions of the same object.

Delete or purge a version. You can delete an object from view and the repository. You can recover or undelete deleted objects. If you want to perm remove an object version, you can purge it from the repository.

Deployment. Unlike copying a folder, copying a deployment group allow select number of objects from multiple folders in the source repository to multiple in the target repository. This gives you greater control over the specific objects cop one repository to another.

♦

♦

♦

♦

Deployment groups. You can create a deployment group that contains referen objects from multiple folders across the repository. You can create a static deployment group that you manually add objects to, or create a dynamic deployment group that us query to populate the group.

Labels. A label is an object that you can apply to versioned objects in the allows you to associate multiple objects in groups defined by the label. You can use labels to track versioned objects during development, improve query results, and organize grou of objects for deployment or export and import.

Queries. You can create a query that specifies conditions to search for obje repository. You can save queries for later use. You can make a private query, or you ca share it with all users in the repository.

Track changes to an object. You can view a history that includes all version and compare any version of the object in the history to any other version. This allows to see the changes made to an object over time.

XML Support

PowerCenter contains XML features that allow you to validate an XML file against an XM schema, declare multiple namespaces, use XPath to locate XML nodes, increase perform for large XML files, format your XML file output for increased readability, and parse or generate XML data from various sources. XML support in PowerCenter includes the following features: ♦

♦

♦

♦

XML schema. You can use an XML schema to validate an XML file and to and target definitions. XML schemas allow you to declare multiple namespaces so you use prefixes for elements and attributes. XML schemas also allow you to define some complex datatypes.

XPath support. The XML wizard allows you to view the structure of XML sch can use XPath to locate XML nodes.

Increased performance for large XML files. When you process an XML file or can set commits and periodically flush XML data to the target instead of writing all th output at the end of the session. You can choose to append the data to the same target or create a new target file after each flush. XML target enhancements. You can format the XML target file so that you the XML file in a text editor. You can also configure the PowerCenter Server to not out empty elements to the XML target.

Usability ♦

♦

Copying objects. You can now copy objects from all the PowerCenter Client the copy wizard to resolve conflicts. You can copy objects within folders, to other folde and to different repositories. Within the Designer, you can also copy segments of mappings to a workspace in a new folder or repository. Comparing objects. You can compare workflows and tasks from the Workflow You can also compare all objects from within the Repository Manager.

♦

♦

♦

♦

♦

♦

♦

Change propagation. When you edit a port in a mapping, you can changed attributes throughout the mapping. The Designer propagates ports, exp and conditions based on the direction that you propagate and the attributes you ch propagate.

Enhanced partitioning interface. The Session Wizard is enhanced to pro depiction of a mapping when you configure partitioning.

Revert to saved. You can now revert to the last saved version of an Manager. When you do this, the Workflow Manager accesses the repository to r last-saved version of the object.

Enhanced validation messages. The PowerCenter Client writes messages window that describe why it invalidates a mapping or workflow when you modi dependent object.

Validate multiple objects. You can validate multiple objects in the repos fetching them into the workspace. You can save and optionally check in objects change from invalid to valid status as a result of the validation. You can validat mappings, mapplets, workflows, and worklets.

View dependencies. Before you edit or delete versioned objects, such mappings, or workflows, you can view dependencies to see the impact on other You can view parent and child dependencies and global shortcuts across reposi Viewing dependencies help you modify objects and composite objects without b dependencies. Refresh session mappings.

In the Workflow Manager, you can refresh

About Informatica Documentation The complete set of documentation for PowerCenter includes the following books: ♦

♦

♦ ♦

♦

♦

♦

♦

♦

♦

♦

♦

Data Profiling Guide. Provides information about how to profile PowerCenter evaluate source data and detect patterns and exceptions.

Designer Guide. Provides information needed to use the Designer. Includes help you create mappings, mapplets, and transformations. Also includes a description the transformation datatypes used to process and transform source data. Getting Started.

Provides basic tutorials for getting started.

Installation and Configuration Guide. Provides information needed to install an configure the PowerCenter tools, including details on environment variables and data connections.

PowerCenter Connect® for JMS® User and Administrator Guide. Provides inform to install PowerCenter Connect for JMS, build mappings, extract data from JMS messag and load data into JMS messages.

Repository Guide. Provides information needed to administer the repository Repository Manager or the pmrep command line program. Includes details on functionality available in the Repository Manager and Administration Console, such as creating and maintaining repositories, folders, users, groups, and permissions and privileges. Transformation Language Reference. Provides syntax descriptions and examples transformation function provided with PowerCenter. Transformation Guide. Provides information on how to create and configure transformation in the Designer.

Troubleshooting Guide. Lists error messages that you might encounter while PowerCenter. Each error message includes one or more possible causes and actions th you can take to correct the condition.

Web Services Provider Guide. Provides information you need to install and confi Services Hub. This guide also provides information about how to use the web services tha Web Services Hub hosts. The Web Services Hub hosts Real-time Web Services, Batch Web Services, and Metadata Web Services. Workf low Administration Guide. Provides information to help you create and workflows in the Workflow Manager, as well as monitor workflows in the Workflow Monitor. Also contains information on administering the PowerCenter Server and performance tuning.

XML User Guide. Provides information you need to create XML definitions fro XSD, or DTD files, and relational or other XML definitions. Includes information on running sessions with XML data. Also includes details on using the midstream XML transformations to parse or generate XML data within a pipeline.

About this Book

The XML User Guide is written for IS developers and software engineers respons working with XML in a data warehouse environment. Before you use the XML Us ensure that you have a solid understanding of XML concepts, your operating syst files, or mainframe system in your environment. Also, ensure that you are familia interface requirements for your supporting applications. The material in this book is available for online use.

Document Conventions This guide uses the following formatting conventions: If you see…

It means…

italicized text

The word or set of words are especially emphasized.

boldfaced text

Emphasized subjects.

italicized monospaced text

This is the variable name for a value you enter as part of an operating system command. This is generic text that should be replaced with user-supplied values.

Note:

The following paragraph provides additional facts.

Tip:

The following paragraph provides suggested uses.

Warning:

The following paragraph notes situations where you can overwri or corrupt data, unless you follow the specified procedure.

monospaced text

This is a code example.

bold monospaced text

This is an operating system command you enter from a prompt t run a task.

Other Informatica Resources In addition to the product manuals, Informatica provides these other resources: ♦

Informatica Customer Portal

♦

Informatica Webzine

♦

Informatica web site

♦

Informatica Developer Network

♦

Informatica Technical Support

Visiting Informatica Customer Portal

As an Informatica customer, you can access the Informatica Customer Portal site at http: my.informatica.com. The site contains product information, user group information, newsletters, access to the Informatica customer support case management system (ATLA the Informatica Knowledgebase, Informatica Webzine, and access to the Informatica use community.

Visiting the Informatica Webzine The Informatica Documentation team delivers an online journal, the Informatica Webzine This journal provides solutions to common tasks, detailed descriptions of specific features, and tips and tricks to help you develop data warehouses.

The Informatica Webzine is a password-protected site that you can access through the Customer Portal. The Customer Portal has an online registration form for login accounts its webzine and web support. To register for an account, go to http://my.informatica.com If you have any questions, please email [email protected].

Visiting the Informatica Web Site

You can access Informatica’s corporate web site at http://www.informatica.com. The site contains information about Informatica, its background, upcoming events, and locating y closest sales office. You will also find product information, as well as literature and partn information. The services area of the site includes important information on technical support, training and education, and implementation services.

Visiting the Informatica Developer Network The Informatica Developer Network is a web-based forum for third-party software developers. You can access the Informatica Developer Network at the following URL: http://devnet.informatica.com

The site contains information on how to create, market, and support customer-ori on solutions based on Informatica’s interoperability interfaces.

Obtaining Technical Support

There are many ways to access Informatica technical support. You can call or ema nearest Technical Support Center listed below or you can use our WebSupport Se

WebSupport requires a user name and password. You can request a user name and p http://my.informatica.com. North America / South America

Africa / Asia / Australia / Europe

Informatica Corporation

Informatica Software Ltd.

2100 Seaport Blvd. Redwood City, CA 94063 Phone: 866.563.6332 or 650.385.5800 Fax: 650.213.9489 Hours: 6 a.m. - 6 p.m. (PST/PDT) email: [email protected]

6 Waltham Park Waltham Road, White Waltham Maidenhead, Berkshire SL6 3TN Phone: 44 870 606 1525 Fax: +44 1628 511 411 Hours: 9 a.m. - 5:30 p.m. (GMT) email: [email protected] Belgium Phone: +32 15 281 702 Hours: 9 a.m. - 5:30 p.m. (local time) France Phone: +33 1 41 38 92 26 Hours: 9 a.m. - 5:30 p.m. (local time) Germany Phone: +49 1805 702 702 Hours: 9 a.m. - 5:30 p.m. (local time) Netherlands Phone: +31 306 082 089 Hours: 9 a.m. - 5:30 p.m. (local time) Singapore Phone: +65 322 8589 Hours: 9 a.m. - 5 p.m. (local time) Switzerland Phone: +41 800 81 80 70 Hours: 8 a.m. - 5 p.m. (local time)

Chapter 1

XML Concepts This chapter includes the following topics: ♦

Overview, 2

♦

XML Files, 3

♦

DTD Files, 7

♦

XML Schema Files, 9

♦

Types of XML Metadata, 11

♦

Simple and Complex XML Types, 17

♦

Component Groups, 23

♦

XML Path, 26

♦

Code Pages, 27

Overview

XML (Extensible Markup Language) is a flexible way to create common information form and share both the format and the data on the world wide web, intranets, and between applications. XML is similar to HTML because XML and HTML contain markup symbols to describe the contents of a page or file. XML describes the content in terms of the data. HTML, however, describes the content of a web page such as the text and graphics. You can import XML definitions into PowerCenter from XML files, DTD files, and XML schema files. ♦

♦

♦

An XML file contains data, and it can reference a Document Type Definition XML schema to describe the data.

A Document Type Definition (DTD) file defines the element types, attributes, entities in an XML file. It provides some constraints on the XML document structure b it does not contain any data.

An XML schema file is called an XML Schema Definition (XSD). A schema elements and attributes, and it contains a description of the type of elements and attribut in the associated XML file. Schemas contain simple and complex types. A simple type is an XML element or attribute that can contains only text. A complex type is an XML element that contains other elements and attributes.

In XML schemas, you can create element and attribute groups that you can reference throughout a schema. You can also create substitution groups to substitute one element another element in the XML instance document.

XML Files

XML files contain tags that identify the data in a document, but not the format of The basic component of an XML file is an element. An XML element includes an e start tag, content, and an element end tag. All XML documents must have a root e defined by a single tag at the top and bottom of the document. The root element e the other elements in the file. For example, the following XML file describes a book: Understanding XML Using XML Using DTD Files Fun with Schemas

Book is the root element and it contains the title and chapter elements. Book is th element of title and chapter, and chapter is the parent of heading. Title and chapte elements because they have the same parent.

An element can have attributes that provide additional information about the elem example below, the attribute graphic_type describes the content of file: <file

graphic_type="gif">computer.gif

Figure 1-1 shows the structure, elements, and attributes in an XML file: Figure 1-1. Sample XML File: StoreInfo.xml

Root Element

Element Data

Enclosure Element Element Tags Element Data Attribute Value Attribute Tag

An XML file is a hierarchical structure. The XML hierarchy may contain the following elements: ♦ ♦

♦

♦

♦

♦

Child element.

An element contained within another element.

Enclosure element. An element that contains other elements but does not can include other enclosure elements. Global element. An element that is a direct child of the root element. You global elements throughout an XML schema. Leaf element. An element that does not contain other elements. The lowest in the XML hierarchy. Local element. An element that is nested in another element. You cannot elements outside of the context of the parent element. Multiple-occurring element. An element that occurs more than once within element. Enclosure elements can be multiple-occurring elements.

♦

♦ ♦

Parent chain. The succession of child-parent elements that traces the element to the root. Parent element.

An element that contains other elements.

Single-occurring element.

An element that occurs once within its paren

Figure 1-2 shows components in an XML hierarchy: Figure 1-2. Elements in the XML Hierarchy

Enclosure Element: Element Address encloses elements StreetAddress, City, State, and Zip . Element Address is also a Parent element. Leaf Element: Element Zip, along with all its sibling elements, is the lowest level element within element Address. Multiple-occurring Element: Element Sales occurs more than once within element Product. Single-occurring Element: Element PName occurs only once within element Product. Child Element: Element PName is a child of product, which is a descendant of Store. Parent Chain: Element YTDSales is a child of element Sales, which is a child of element Product, which is a child of root element Store. All these elements belong in the same parent chain.

A valid XML file conforms to the structure defined in an associated DTD or schema file. T DOCTYPE declaration in an XML file references the location and name of a DTD file. It also names the root element for the XML file. For example, the following DOCTYPE declaration specifies the note.dtd file:
"http://www.w3schools.com/dtd/note.dtd">

XML Data

The schemaLocation declaration references the location and name of a schema. The follo XML file references an external schema: XML Data

XML files contain an encoding declaration that indicates the code page in the file. The m common code pages in XML are UTF-8 and UTF-16.

DTD Files

A Document Type Definition (DTD) file defines the element types, attributes, and ent an XML file. It also provides some constraints on the XML document structure. A DTD does not contain any data or element datatypes, but it can contain attribute datat

DTD Elements The following syntax describes a simple element in a DTD file:

This DTD description defines the XML tag . The description (#PCDATA) s for parsed character data. Parsed character data is plain text without child elements also use (#CDATA), which is character data. CDATA is not parsed or displayed. An element that contains child elements has the following syntax in a DTD file:

The boat element has two child elements: brand and type. Each child element can characters. In this example, brand and type can occur once inside the element boa the number of possible occurrences use one of the following occurrence indicator ♦

+ must occur one or more times

♦

* may occur zero or more times

♦

? may occur once or not at all

For example, to specify that color must occur one or more times for a boat:

DTD Attributes The following syntax describes an attribute in a DTD file:

Use the following parameters to identify an element attribute in a DTD file: ♦

Element_name. The name of the element that has the attribute.

♦

Attribute_name. The name of the attribute.

♦

Attribute_type. The kind of attribute. The most common attribute type is CDATA

♦

Default_value. The value of the attribute if no attribute value occurs in the XML

You can use the following options in place of a default value: − −

#REQUIRED. #IMPLIED.

The XML document must contain the attribute value.

The attribute value is optional.

−

#FIXED. The XML document must use the default value from the DTD file XML file can contain the same attribute value as the DTD, or it can have no attribute value. You must specify a default value with this option. For example:

Figure 1-3 shows elements and attributes in a DTD file: Figure 1-3. Sample DTD: StoreInfo.dtd

Element

Element List

Element Occurrence

Attribute

Attribute Value Option Attribute Name

XML Schema Files

The XML schema file, like the DTD file, contains only metadata. In addition to the definition and structure of elements and attributes, an XML schema contains a descr the type of elements and attributes in the associated XML file. An XML schema file is an XML Schema Definition (XSD). Figure 1-4 shows XML schema components: Figure 1-4. Sample XML Schema File: StoreInfo.xsd

Element Name

Attribute Attribute Type and Null Constraint Element Datatype

Element Data Element List and Occurrence

Element List and Datatype

When you use a schema to define an XML file, you can restrict data, define data fo convert data between datatypes.

XML schemas support inheritance in elements, complex types, substitution groups, attri groups, any content, and circular references. For more information about XML schemas, see “Simple and Complex XML Types” on page 17. For information about element groups, see “Component Groups” on page 23.

Types of XML Metadata

You can create PowerCenter XML definitions from XML, DTD, or XML schema file files provide both data and metadata, while DTD and XML schema files provide on metadata.

PowerCenter extracts the following types of metadata from XML, DTD, and XML s files: ♦

♦

♦

♦

♦

♦

Namespace. A collection of element types and attribute names identifie reference in an XML document. Namespace differentiates between elements that from different sources. For more information about namespaces, see “Namespace page 11. Name. A tag that contains the name of an element or attribute. For about the name tag, see “Name” on page 13.

Datatype. A classification of a data element, such as numeric, string, XML supports custom datatypes and inheritance. For more information about d see “Datatype” on page 13.

Hierarchy. The position of an element in relationship to other elements For more information, see “Hierarchy” on page 13. Cardinality. The number of times an element occurs in an XML file. information about cardinality, see “Cardinality” on page 14.

Null constraint. Specification that determines if an element can contain more information about the null constraint, see “Null Constraint” on page 16.

Namespace

An XML namespace identifies groups of elements. It can identify elements and attrib combined from different XML documents into one file. For example, you can distingu meanings for the element table by declaring different namespaces, such as math:t furniture:table.

A namespace contains a Uniform Resource Identifier (URI) to identify schema loc URI is a string of characters that identifies an internet resource. It is an abstracti A URL locates a resource, but a URI identifies a resource.

The DTD or schema file does not have to exist at the location you specify in the U URI distinguishes between elements with the same name from different locations

You can declare a namespace at the root level of an XML instance document, or y declare a namespace inside any element in an XML structure. A namespace decla appears in an instance document as an attribute that starts with xmlns. XML is ca so the namespace Math:table is different namespace from math:table .

When you declare multiple namespaces in the same instance document, you use p associate an element to a namespace. Prefixes follow the namespace attribute and as xmlns:. You can create a prefix name of any length, however, short prefi

efficient. The following example shows how to declare and use multiple namespaces in a XML instance document: xmlns:math = “http://www.mathtables.com” xmlns:furniture = “http://www.home.com”>

If you associate a prefix with an attribute, the PowerCenter Server associates the attribut a namespace. If you do not associate a prefix, the PowerCenter Server use the default namespace. An element cannot have two attributes with the same name unless the attrib have different qualified names.

Qualified names are names that contain a namespace name. You create a qualified name using a prefix that is mapped to a namespace or by declaring a default namespace for an element.

Each schema in the repository contains the default prefix for elements and attributes. For on XML source or XML target, the list of prefixes and their namespaces should be unique. If a duplicate prefix appears in the same instance document, the Designer makes the prefix uniq before storing it in the repository. For example, the following is an example of a common schema declaration: ... ...

The fragment targetNamespace="http://www.w3XML.com"

describes the namespace for the schema itself. The simple and complex datatypes that t schema defines come from the "http://www.w3XML.com" namespace. The fragment xmlns:xs="http://www.w3.org/2001/XMLSchema"

indicates that the native XML schema elements and datatypes the schema uses come from t “http://www.w3.org/2001/XMLSchema” namespace. The elements and data types that com from the “http://www.w3.org/2001/XMLSchema” namespace have a prefix of xs:. The fragment xmlns="http://www.w3XML.com"

indicates that the default namespace is "http://www.w3XML.com.” You can attach a defau namespace to an element and the child elements using an xmlns attribute with no pre element and its children are in the default namespace unless they have an explici The fragment elementFormDefault="qualified"

indicates that any element that this schema declares must be namespace qualifie instance document.

Name

In an XML file, each tag is the name of an element or attribute. In a DTD file, the indicates the name of an element and the tag indicates t attributes for an element. In a schema file, indicates the name of an and indicates the name of an attribute.

When you import XML definitions into PowerCenter, the element tags become the names in the PowerCenter definition by default.

Datatype

The XML schema language has over 40 built-in datatypes, including numeric, string XML, and binary. These datatypes are called simple types. They contain text but no o elements and attributes. You can derive new simple types from the basic XML simple For more information about XML datatypes, see the W3C specifications for XML dat at http://www.w3.org/TR/xmlschema-2.

You can create complex XML datatypes. A complex datatype is a datatype that can co more than one simple type. They can also contain other complex types and attributes more information about simple and complex datatypes, see “Complex Types” on pag

XML files and DTD files do not store datatypes. When you import a source or targ definition from an XML file without an associated DTD or an XML schema, the De assigns either a numeric or a string datatype to the elements. For information on PowerCenter uses XML datatypes, see “XML and Transformation Datatypes” on p

Hierarchy

An XML document models a hierarchical database rather than a relational databa position of an element in an XML hierarchy represents its relationship to other eleme example, an element can contain child elements, and elements can inherit character other elements.

When you import an XML definition in PowerCenter, the Designer creates a schema repository. The schema models the hierarchy of the file you import. The Designer val the XML definition you create against the hierarchy of this schema.

Cardinality

Cardinality is the number of members in a set. Element cardinality in a DTD or schema fil the number of times an element might occur in an XML document. Element cardinality affects how you can structure groups in an XML definition. Two types of cardinality affec XML definition: absolute cardinality and relative cardinality.

Absolute Cardinality

The absolute cardinality of an element is the number of times an element occurs within i parent element in an XML hierarchy. DTD and XML schema files explicitly describe the absolute cardinality of elements within the hierarchy. A DTD file uses symbols, and an XM schema file uses the and attributes to describe the absolut cardinality of an element. For example, an element has an absolute cardinality of Once(1) if it occurs once within its parent element. However, it might occur many times within an XML hierarchy if its parent element has a cardinality of one or more(+). Table 1-1 describes the way DTD and XML schema files represent cardinality: Table 1-1. Cardinality of Elements in XML

Absolute Cardinality

DTD

Schema

Zero or once

?

minOccurs=0 maxOccurs=1

Zero or one or more times *

minOccurs=0 maxOccurs=unbounded minOccurs=0 maxOccurs=n

Once

minOccurs=1 maxOccurs=1

One or more times

+

minOccurs=1 maxOccurs=unbounded minOccurs=1 maxOccurs=n

Figure 1-5 shows the absolute cardinality of elements in a sample XML file: Figure 1-5. XML Cardinality

Element Address occurs more than once within Store. Its absolute cardinality is One or More(+). Element City occurs only once within its parent element Address. Its absolute cardinality is Once(1).

Element Sales occurs zero or more times within its parent element Product. Its absolute cardinality is Zero or More(*).

Relative Cardinality

Relative cardinality is the relationship of an element to another element in the XM hierarchy. An element can have a one-to-one, one-to-many, or many-to-many relati another element in the hierarchy.

An element has a one-to-one relationship with another element if for every occurren element there is one occurrence of the other element. For example, an employee ele have only one social security number element. Employee and social security number one-to-one relationship.

An element has a one-to-many relationship with another element if for every occu one element, there can be multiple occurrences of the other element. For exampl employee element might have multiple email elements.

An element has a many-to-many relationship with another element if an XML file can multiple occurrences of both elements. For example, an employee might have multip addresses and multiple street addresses.

Figure 1-6 shows the relative cardinality between elements in a sample XML file: Figure 1-6. Relative Cardinality of Elements in StoreInfo.xml

One-to-Many Relationship For every occurrence of SNAME, there can be many occurrences of ADDRESS and, therefore, many occurrences of CITY.

Many-to-Many Relationship There can be multiple occurrences of STATE and multiple occurrences of YTDSALES.

One-to-One Relationship For every occurrence of PNAME, there is also one occurrence of PPRICE.

Null Constraint

The absolute cardinality of an element determines its null constraint. An element that ha absolute cardinality of one or more(+) cannot have null values, but an element with a cardinality of zero or more(*) can have null values. An attribute marked as fixed or required an XML schema or DTD file cannot have null values, but an implied attribute can have null values. For more information about default, fixed, and implied attributes, see “DTD Attributes” on page 7. When you import an XML definition, the Designer sets the null constraints for the columns depending on the absolute cardinality of the element or attribute the column points to. For more information about absolute cardinality, see “Cardinality” on page 14.

Simple and Complex XML Types

Schema files contain simple and complex types. A simple type is a basic type that con other elements or attributes. A complex type is an XML element that contains other e and attributes.

Simple Types

A simple type is an XML element or attribute that can contain only text. A simple indivisible and it forms a column in an XML definition. Simple types cannot have a but attributes are simple types. PowerCenter supports the following simple types: ♦

Atomic types

♦

Lists

♦

Unions

Atomic Types

An atomic datatype is a basic datatype in an XML schema definition such as a boolean integer, decimal, or date. You can define custom atomic datatypes by adding restrict atomic datatype in order to limit the content.

A facet is a specification of the values that are allowed or not allowed in a restriction. can specify minimum or maximum data values, a list of the legal values, or a data pat base attribute for the element specifies the datatype that the facet restricts.

The pattern facet restricts an element to an expression. The following example re string to one lowercase letter from a to z:

The enumeration facet lists all allowed values for an element. The following exam a string to “a”, “b”, or “c”.

Lists

A list is an array collection of atomic types. For example, a list might include an array of size The following example shows a simple type called letter. It contains a list of strings. The list itemType defines the datatype of the list components.

PowerCenter stores a list as one string that contains all array elements. It does not pars respective simple types from the string.

Unions

A union type is a combination of one or more atomic or list types that map to one simple type in an instance document. When you define a union type, you specify what types to combine. For example, you might create a type called Size. Size can include string data, such as S, M, and L, or size might contain decimal sizes, such as 30, 32, and 34. If you define a union type element, the XML file can include a sizename type for string sizes, and a sizenum type for numeric sizes.

Figure 1-7 shows a schema file containing a Shoesize union that contains sizenam sizenums lists: Figure 1-7. Sample XSD File with Union Type

Sizename is a restricted string type.

The sizenames type accepts a list of strings. The sizenums type accepts a list decimals. The ShoeSize union accepts both the decimal and string lists.

The union defines sizenames and sizenums as member types. Sizenames defines a values. Sizenums defines a list of decimal values. When you import an XML schem containing this union, the Designer creates the port using a compatible datatype types. In this case, the Designer creates the port as a string datatype because it c both strings and decimals as strings.

Complex Types

Complex types aggregate collections of simple types into a logical unit. For exam Customer type might include the customer number, name, street address, town, c code. Complex types can also reference other complex types or element and attri

XML supports complex type inheritance. If you define a complex type, you can create complex types that inherit the components of the base type. In a type relationship, th type is the complex type from which you derive another type. A derived type is a com type that inherits elements from the base type.

The derived type does not need to redefine the components it inherits from the base type. It can extend or restrict the base type. An extended complex type inherits elements from a bas type and includes additional elements. For example, Customer Purchases might inherit its definition from the Customer complex type, but it adds Item, Cost, and Date_Sold elements.

A restricted complex type restricts some elements from the base type. For example, Mail_Li might inherit elements from Customer, but restrict the Phone Number element by setting th minoccurs and maxoccurs boundaries to zero. Figure 1-8 shows an example of restricted and extended complex types: Figure 1-8. Restricted and Extended Complex Types

Base Complex Type

Extended Complex Type

Restricted Complex Type

Element Reference

The base type is PublicationType. BookType extends the PublicationType and includes th ISBN and Publisher elements. Publication_Minimum restricts PublicationType. Publication_Minimun requires between and 25 Authors, and restricts the date to just the year.

Element Type ANY

An element with a type of ANY can contain any type of data and any defined elem element that is ANY type is unrestricted. You can substitute it for any datatype you w example, the following schema element describes a person. It includes a firstname, l and an ANY element:

If you substitute the ANY type element for a complex type containing childnames file could contain the following data: <firstname>Danny Russell Cissy Cory

Abstract Elements

The abstract attribute specifies that an element cannot occur directly in an XML d you set the abstract attribute to true, an XML instance document must contain a element instead of the abstract type. The abstract attribute default value is false.

For example, the following schema specifies PublicationType as abstract. In an XM document that references this schema, you must use the derived type, BookType. inherits the elements in PublicationType, but also includes ISBN and Publisher ele



Component Groups You can create the following types of groups in an XML schema: ♦

♦

Element and attribute groups. throughout a schema.

Groups of elements and attributes that

Substitution groups. Groups that you can use to replace one member another member in an XML instance document. The substituted element must same complex or simple type derivation chain.

Element and Attribute Groups

You can assemble elements and attributes into groups that you reference through schema. You declare a group of elements or attributes and reference that group t the document. The following example shows the schema syntax for an element group:

The following example shows the schema syntax for an attribute group:

You can create the following element and attribute groups that have constraints: ♦

Sequence group. All elements in an XML file must occur in the order them. For example, OrderHeader requires the customerName first, then orderN and then orderDate:

♦

Choice group. Only one element in the group can occur in an XML example, only one element in the CustomerInfo group can occur in an XML doc

♦

All group. All elements must occur in the XML document or none at all. The occur in any order. For example, CustomerInfo requires all or none of the three eleme

Substitution Groups

Substitution groups allow you to replace one element with another in an XML instance document. For example, if you have addresses from Canada and the United States, you m want to create an address type for Canada and another type for the United States. You c then create a substitution group that accepts either type of address. The following schema section shows an Address base type, and the derived types, CAN_Address, and USA_Address. CAN_Address has Province and PostalCode, and the USA_Address has State and Zip. The MailAddress substitution group includes both addr types.



For more information about using Substitution Groups with PowerCenter, see “Us Substitution Groups in an XML Definition” on page 45.

XML Path

The XML Path (XPath) represents the position of an element or attribute in the XML hierarchy. In an XML definition, the XPath lists the path from an element or attribute to the root. Except for generated key columns, each column in a group has an XPath and refers to a attribute or an element in an XML hierarchy.

The XPath ensures integrity in the group column definition. PowerCenter uses a slash (/) depict the XPath of a column. Figure 1-9 shows the XPath for different elements and attributes in a hierarchy: Figure 1-9. XPath of Elements and Attributes in the StoreInfo.xml

STORE STORE/SNAME

STORE/ADDRESS/STREETADDRESS

STORE/PRODUCT/PNAME

STORE/PRODUCT/SALES/YTDSALES

STORE/PRODUCT/@PID STORE/PRODUCT/SALES/@REGION

Code Pages

XML files contain an encoding declaration that indicates the code page used in the fil most commonly used code pages in XML are UTF-8 and UTF-16. All XML parsers supp these two code pages. For information on the XML character encoding specification, W3C website at http://www.w3c.org.

PowerCenter supports the same set of code pages for XML files that it supports for re databases and other flat files. You can use any code page supported by both Informat the XML specification. For a list of code pages that Informatica supports, see “Code P the Installation and Configuration Guide . Informatica does not support any user-def page.

For XML source definitions, PowerCenter uses the repository code page. When you i source definition from an XML file, the Designer displays the code page declared in t for verification only. It does not use the code page declared in the XML file.

For XML target definitions, PowerCenter uses the code page declared in the XML file Informatica does not support the declared code page, the Designer returns an error. cannot import the target definition.

Chapter 2

Using XML with PowerCenter This chapter includes the following topics: ♦

Overview, 30

♦

Extracting XML Metadata, 31

♦

Working with XML Views, 36

♦

Generating Hierarchical View Relationships, 38

♦

Generating Entity View Relationships, 42

♦

Working with Circular References, 47

♦

Understanding View Rows, 49

♦

Pivoting Columns, 51

♦

Limitations, 54

Overview

You can create an XML definition in PowerCenter from an XML file, DTD file, XML schema, flat file definition, or relational table definition. When you create an XML definition, the Designer extracts XML metadata from the file or definition you choose an creates a schema in the repository. The schema provides the structure from which you c and validate the XML definition.

An XML definition can contain multiple groups. In an XML definition, groups are called views. The relationship between elements in the XML hierarchy defines the relationship between the views. When you generate an XML definition, the Designer creates views fo multiple-occurring elements and complex types in a schema. The relative cardinality of elements in an XML hierarchy affects how PowerCenter creates views in an XML definit Relative cardinality determines if elements can be part of the same view.

The Designer relates the views in an XML definition by keys. Source definitions do not require keys, but target views must have them. Each view has a primary key, which can contain an XML element or a generated key. The Designer defines relationships between views with foreign keys. A foreign key in a view points to the primary key of the other vie the relationship.

When you generate an XML definition, you can create a hierarchical model or an entity relationship model. If you create a hierarchical model, you create normalized or denorma views. A normalized hierarchy contains separate views for multiple-occurring elements. denormalized hierarchy has one group with duplicate data for multiple-occurring elemen

If you create an entity model, the Designer creates views for multiple-occurring and com types. You can use simple types such as lists, unions, and substitution groups. You can al model inheritance and circular relationships in PowerCenter definitions.

Extracting XML Metadata

The Designer creates XML metadata from XML files, DTD files, and XML schemas. It creates XML metadata from flat file and relational definitions in the repository. When import an XML definition, the Designer creates a schema in the repository for the de The repository schema provides the structure from which you edit and validate the X definition.

Metadata from an XML File

In an XML file, a pair of tags marks the beginning and end of each data element. Thes are the basis for the metadata that PowerCenter extracts from the XML file. If you im XML file without an associated DTD or an XML schema, the Designer reads the XML determine the elements, their possible occurrences, and their position in the hierarc Designer checks the data within the element tags and assigns a string datatype or a n datatype depending on the data representation. You can change the datatypes for elements in the XML definition.

Figure 2-1 shows a sample XML file. The root element is Employees. The Employe contains the LastName, FirstName, and Address. It also contains two multiple-oc elements: Phone and Email. Figure 2-1. A Sample Employees XML File with Multiple-Occurring Elements

Employee, Phone, and Email are multiple-occurring elements.

The Designer determines a schema structure from the data. This example shows separate views for the root element, and the multiple-occurring elements, Employee, Email, and Phone. Figure 2-2 shows a default XML source definition from the Employees XML file: Figure 2-2. The Root Element and XML Views in an XML Definition

Root Element

Multiple-Occurring Elements

When you import an XML file, you do not need all of the XML data to create an XML definition. You need enough data to accurately show the hierarchy of the XML file. Informatica recommends that you import an XML file that is under 100K. You can reduce size of a large XML file by deleting duplicate data elements.

The Designer can create an XML definition from an XML file that references an internal external DTD or an XML schema. The XML file uses a universal resource identifier (URI) refer to the address of an external DTD or an XML schema. If an XML file has a reference a DTD or an XML schema on another machine, the machine that hosts the PowerCenter Client must have access to the machine where the schema resides so that the Designer c read it.

Metadata From DTD Files

A DTD file provides constraints on a XML document structure. It lists elements, attribut entities, and notations that you can use in the XML document. It specifies relationships between components. A DTD specifies cardinality and null constraint. However, a DTD fi does not contain any data and it does not specify datatypes.

When you import a DTD file you can change the datatypes for the elements in the XML definition. You can change the null constraint, but you cannot change element cardinalit

If you import an XML file with an associated DTD, the Designer extracts the metadata based on the DTD structure. However, you can import an XML file with elements and attributes that include only a portion of the hierarchy defined in the DTD file.

Figure 2-3 shows an example of an XML file with a portion of the hierarchy from associated DTD and the source definition that the Designer creates: Figure 2-3. XML Definition From an XML File Referencing a DTD File

StoreInfo.dtd contains the Store element. Product is one of the child elements of Store.

ProductInfo.xml uses only the Product element from StoreInfo.dtd. Product includes the multiple-occurring Sales element.

The ProductInfo definition contains only the Product and Sales groups. The XML file determines what elements to include in the definition. The DTD file determines the structure of the XML definition.

Metadata from an XML Schema

A schema file defines the structure of elements and attributes in an XML file, but contains a description of the type of elements and attributes in the file. When you XML schema, the Designer determines the datatype, precision, and cardinality of elements. You cannot change them.

XML schemas can include complex types. Every attribute or simple type in a comple can become a column in an XML definition. Derived complex types inherit all the elem from a base type. If you want to reduce metadata, you can create separate entities fo

complex types and the derived types do not contain the inherited components in the XM view.

Figure 2-4 shows a schema containing a base type Address and a derived type CAN_add Figure 2-4. XML Schema with a Derived Complex Type

Address is a Base Type.

CAN_Postal_ Code restricts a string to a pattern. CAN_address extends Address.

The MailAddress element is an Address type. A derived type, Can_Address, inherits the Name, City, and Street from the Address type, and it extends Address by adding a Provin and PostalCode. PostalCode is a simple type called CAN_Postal_Code. Figure 2-5 shows an example XML definition that you might create from the schema if y choose to import the schema with the default options: Figure 2-5. XML Definition Containing a Derived Type

The Root element is Mail Address. The Address type contains name, street, and city.

The Canadian Address has a foreign key to Address. Canadian Address includes Province and PostalCode.

The Can_Address group contains only the elements that are unique for its type. The grou does not contain the Name, Street, and City that it inherits from MailAddress.

Metadata from Relational Definitions

You can create an XML definition from relational source definitions. When you creat XML definition from relational source definitions, you can select multiple definitions create relationships between them. The Designer creates an XML view for each rela definition you import. It converts every column in the relational definition, and gene primary key-foreign key relationships. It also creates a root element that encloses th

Figure 2-6 shows a sample XML definition from the relational definitions Orders a Order_Items. The root is XRoot. It encloses Orders and Order Items. Order Items foreign key that points to Orders. Figure 2-6. XML Target Created from Two Relational Sources

Working with XML Views

The relationship between elements in the XML hierarchy defines the relationship betwee XML views. In a source definition, a view does not have to be related to any other view. A denormalized view can be independent of any other view. Therefore, views in a source definition do not require primary or foreign keys. However, if a view is related to another view based on the XML hierarchy, and you do not designate any column as a key, the Designer generates a key.

In a target definition, each view must be related to at least one other group. Therefore, e view needs at least one key to establish its relationship with another view. If you do not designate the keys, the Designer generates primary and foreign keys in the target views can define primary and foreign keys for views if you create the views and relationships in XML Editor instead of allowing the Designer to create them for you.

When the Designer creates a primary or foreign key column, it assigns a column name with a prefix. In an XML definition, the prefixes are XPK_ for a generated primary key column and XFK_ for a generated foreign key column. The Designer uses the prefix FK_ for a foreign key that points to a primary key.

For example, when the Designer creates a primary key column for the Sales group, it na the column XPK_Sales. When it creates a foreign key column connecting a sales group to another group, it names the column XFK_Sales. You can rename any column name that t Designer creates. For an example of primary and foreign keys in a source group, see Fig 8 on page 39.

If a mapping contains an XML source, the PowerCenter Server creates the values for the generated primary key columns in the source definition when you run the session. You ca specify start values for the generated keys. For more information about setting generate values, see “Setting Sequence Numbers for Generated Keys” on page 128.

Rules for View Keys and Relationships

Consider the following rules and guidelines when you work with view keys and relations ♦

A group can have only one primary key.

♦

An enclosure element cannot be a key.

♦

A group can be related to several other groups, and can have multiple foreign keys.

♦

A column cannot be both a primary key and a foreign key.

♦

A source group does not require a key.

♦

A target group requires at least one key. − −

♦

The target root group requires a primary key. It does not require a foreig A target leaf group requires a foreign key. It does not require a primary

A foreign key always refers to a primary key in another group. You cannot referencing keys.

♦ ♦

A generated foreign key column always refers to a generated primary The relative cardinality of elements in an XML hierarchy affects how views in an XML definition. Relative cardinality determines if elements can be same view. − −

−

Elements in a one-to-one relationship can be part of the same view

Elements in a one-to-many relationship can be part of the same no denormalized view.

Elements in a many-to-many relationship cannot be part of the same

Creating Custom XML Views

Custom views are groups that you create with the XML Wizard or the XML Editor. the XML Wizard to create custom views, the wizard creates views containing all t components in the schema. If you use the XML Editor, you can define each view a the components.

The elements in the views and the relationship between views are dependent on t the Designer creates in the repository when you import the definition. The XML E validates XML definitions using the rules for valid views. For more information ab for view relationships, see “Rules for View Keys and Relationships” on page 36.

For more information about creating custom XML groups using the XML Editor, s “Creating and Editing Views” on page 81.

Generating Hierarchical View Relationships

When you generate a hierarchical view relationship, the Designer expands each referenc component under its parent element. You can generate normalized or denormalized hierarchical relationships. The Designer uses the following guidelines when it generates hierarchical views: ♦

♦

A normalized relationship reduces redundancy by separating multiple-occurring separate views. The views are related by primary and foreign keys.

A denormalized view contains all elements of the hierarchy. A source or tar can contain one denormalized view.

Generating Normalized Views When the Designer generates normalized views for an XML definition, it establishes the element and the multiple-occurring elements that become views in an XML definition.

Figure 2-7 shows an example DTD file and the elements that become views in a normaliz XML definition: Figure 2-7. Sample DTD File to Create Normalized Views

STORE: Root ADDRESS+: Multiple-occurring PRODUCT*: Multiple-occurring EMPLOYEE+: Multiple-occurring

SALES*: Multiple-occurring

Figure 2-8 shows a source definition based on the DTD file in Figure 2-7 with normal views. The root view is Store. The Address, Product, and Sales views have foreign ke Store. The Sales view has a foreign key to the Product view. Figure 2-8. Source Definition for StoreInfo.dtd

STORE View STORE Primary Key

Foreign Key to STORE ADDRESS View

PRODUCT View

SALES View

EMPLOYEE View

Figure 2-9 shows a data preview for each view in the source definition: Figure 2-9. Normalized Views Data Preview

STORE View ADDRESS View PRODUCT View SALES View

EMPLOYEE View

Generating a Denormalized View

When the Designer generates a denormalized view, it creates one group and puts all elemen of the hierarchy into the group. All the elements in a denormalized group belong to the same parent chain. Denormalized groups, like denormalized tables, generate duplicate data.

The Designer can generate denormalized views for XML definitions that contain more th one multiple-occurring element if the multiple-occurring elements have a one-to-many relationship and are all part of the same parent chain.

Figure 2-10 shows the ProdAndSales.dtd file, a sample XML source file that the Designe converts to a denormalized view. The ProdAndSales.dtd file contains two multiple occurr elements. Figure 2-10. Sample XML File to Create a Denormalized View

STORE: Root PRODUCT*: Multiple-occurring

SALES*: Multiple-occurring

Because the multiple-occurring elements have a one-to-many relationship, the Designer create a single denormalized group that includes all elements Figure 2-11 shows the denormalized group for ProdAndSales.dtd in a source definition. group does not need a primary or foreign key. Figure 2-11. Source Definition Containing a Denormalized View

The Designer creates a single group for all the elements in the ProdAndSales hierarchy. Because a DTD file does not define datatypes, the Designer assigns a datatype of string to all columns.

Figure 2-12 shows a data preview for the denormalized view Store if the Designer source definition from ProdAndSales.xml with the associated ProdAndSales.dtd: Figure 2-12. Data Preview for the ProdAndSales.xml Denormalized View

For an example of a denormalized group in a mapping, see “Adding an XML Sourc to a Mapping” on page 123.

For more information about generating hierarchy relationships using the XML Wi “Generating Hierarchy Relationships” on page 64.

Generating Entity View Relationships

You can generate entity relationships from an XML schema. When you generate an XML definition containing entity relationships, the Designer generates views for multipleoccurring elements, element groups, and complex types in an XML schema. It creates lin and keys between the views based on type and hierarchy relationships.

When you work with XML schemas, you can use references to other parts of the schema rather than repeat the same information in schema components. A component can inherit th elements and attributes of another component and restrict or extend the elements from it. F example, you might use a complex type as a base for creating a new complex type. You can add more elements to the new type to create an extended complex type. Or, you might create a restricted complex type, which is a subset of another complex type.

If you create groups manually or recreate entity relationships in the XML Editor, you choose how you want to structure the metadata. If you create an XML definition based on an XML schema that uses inheritance you can generate separate views for the base type and the der type. You might want to reduce metadata if you plan to map the XML data to normalized relational tables.

Rules and Guidelines The Designer generates entities based on the following guidelines: ♦

♦

♦

♦

♦

An entity represents a portion of an XML, DTD, or XML schema hierarchy. hierarchy does not need to start at the root of the instance document.

The Designer uses entities defined in a DTD file to create entity relationship DTD source. The Designer uses type structures defined in an XML schema to generate relationships from an XSD source. The Designer creates a new entity when it encounters a multiple-occurring a parent element. The Designer generates primary keys and foreign keys to relate separate

Using Entity Relationships in an XML Definition

The following schema contains a PublicationType, BookType, and MagazineType. PublicationType is the base type. It includes Title, Author, and Date. BookType and MagazineType are derived types that extend the PublicationType. A Book has an ISBN an Publisher, and a Magazine has a Volume and Edition.



When you generate XML views as entities in an XML definition, the Title and Date from PublicationType does not repeat in BookType or MagazineType by default. Inst these views contain only the metadata that distinguishes them from the PublicationT They have foreign keys that link them to the base type.

Author is a multiple-occurring element in a Publication. It becomes an XML view. example uses reduced metadata explosion. None of the elements in the base type derived types.

Figure 2-13 shows the views that you might generate from the schema: Figure 2-13. Complex Type View Relationships

Figure 2-14 shows a sample XML file containing a Publication, a Magazine, and two Boo Figure 2-14. Complex Type XML Views Sample Schema

If you process this sample XML file using the XML definition in Figure 2-13 on pag create data in the following views: ♦

♦

♦

♦

PublicationType view contains the Title and Date for each publication:

BookType view contains the ISBN and Publisher. It contains a foreign PublicationType:

MagazineType view contains Volume and Edition. It also contains a fore PublicationType:

The Author view contains authors for all the publications. The Designe separate view for Author, because Author is a multiple-occurring element. Each publication can contain multiple authors:

For more information about generating entity relationships using the XML Wizard “Generating Entity Relationships” on page 63.

For more information about using the XML Editor to create relationships between views, see “Creating Relationships Between Views” on page 89.

Using Substitution Groups in an XML Definition

When you import an XML schema that uses substitution groups, the Designer imp member of the substitution group as a separate view.

Figure 2-15 shows a sample portion of an XML schema containing substitution groups: Figure 2-15. Sample XML Schema Using Substitution Groups

Substitution Group Members

Figure 2-16 shows an XML definition generated from substitution groups: Figure 2-16. Sample XML Definition Generated from Substitution Groups

The Designer creates a new view for each member of the substitution group.

Working with Circular References

A circular reference relationship is a circular hierarchy relationship between two vie XML definition. A view can also refer to itself recursively. For example, an complex e called Part might contain an ID, part name, and a reference to another Part. The following example shows the components for the Part element: /xs:sequence>

Figure 2-17 shows a circular reference in the XML Editor workspace: Figure 2-17. Circular Reference View

You might use the Part XML definition to read the following sample XML file in a

1 Big Part L

1.A Middle Part M

1.A.B Small Part S

In this sample file, Part 1 contains Part 1.A, and Part 1.A contains Part 1.A.B.

Figure 2-18 shows the data and the keys that a session might generate from the XML sou Figure 2-18. Circular Reference Data Preview

Understanding View Rows

To extract data from an XML document, you must specify what rows to generate, columns of data to include, and when to generate the row. When you define a view XML Editor, you create the view row, an element or a global complex type that the PowerCenter Server requires to generate a row of data.

The PowerCenter Server uses a view row to determine when to read and write XML. set a view row at any single or multiple-occurring element. Once you set the view row element you add to the view has a one-to-one correspondence with the view row.

For example, in the view Employees, which contains elements Employee, Name, Firs and Lastname, if you set the view row to Employee, the PowerCenter Server extracts using an algorithm similar to this for-loop: For every (Employees/Employee) extract ./Name/Firstname/Lastname

An Employees XML schema might contain the following elements: EMPLOYEES EMPLOYEE+ ADDRESS+ NAME FIRSTNAME LASTNAME EMAIL+

Employee, Address, and Email are multiple-occurring elements. You could create contains the following elements: EMPLOYEE ADDRESS NAME

If you set the view row as Address, the PowerCenter Server extracts a Name for e Employee/Address in the XML data. You cannot add Email to this view because yo create a many-to-many relationship between Address and Email.

You can add a pivoted multiple-occurring column to the view. For example, you ca instance of Email as a pivoted column to the Employee view. The view would cont following elements: EMPLOYEE ADDRESS NAME EMAIL[1]

A view row can contain a pivoted column. For example, a view might have the vie EMPLOYEE/ADDRESS[1]. The PowerCenter Server extracts data for just the f Employee/Address. For more information about pivoting, see “Pivoting Columns” page 51.

The following rules apply to view rows in an XML definition: ♦

A view row must be a type or an element. It cannot be an attribute.

♦

Every view must have a view row, which must be an element or complex type.

♦

The view root is the top level element in a view. It is the parent to all the other element the view. The view row can be the same as the view root unless the view is denormalized.

♦

Two views can have same view row in an XML source or XML Parser transformation.

♦

♦

♦

♦

♦

♦

The view row element must be the lowest multiple-occurring element in the view. A vie cannot contain many-to-many relationships.

If you add a multiple-occurring element to a view with no other multiple-oc elements, you change the view row to the new element by default. If the view already multiple-occurring element, you cannot add another multiple-occurring element.

You do not need to specify a view row when you create an empty view. you add a column to the view, the Designer creates the view row. This is true even if y are only adding a primary key.

You can change a view row at a later time, but you cannot change a view are no schema components in the view. You can specify a view row that consists of a pivoted element, such as: Product/Order[2]/Customer

♦

An effective view row for a view is the path of view rows from the top relationship down to the view row in the view. A view can have multiple effective view rows because it can have multiple hierarchy relationships in the XML definition.

You can specify options in the XML Editor that affect how view rows and effective view rows affect data output. For more information about setting row options, see “Setting XML View Options” on page 100.

Pivoting Columns

Sometimes an element that occurs multiple times is a set of the same elements con different values. For example, an element called Sales that occurs 12 times might con sales figures for each month of the year. Or, an element called Address that occurs twi be a home address and an office address.

If you have this type of element in your XML source and you treat the occurrences as s columns in the source definition, you can use a method called pivoting. Pivoting enabl to treat occurrences of elements as columns in a group.

To pivot occurrences of an element in an XML view, create a column for each occurren want to represent in the definition. In the monthly sales example, if you want to repres 12 occurrences as columns, create 12 sales columns in the view. If you want to represe sales of one quarter, create three columns. When you run a session, the PowerCenter S ignores any XML data for the occurrences that you do not include in the definition

You can pivot columns when you add or edit a view in the XML source definition. F information, see “Adding Columns to Views” on page 82.

You can pivot simple types and complex types. You cannot pivot a primary key colu When you pivot columns in a view, the resulting group structure must follow the ru valid normalized or denormalized view. The Designer displays warnings and errors pivoted columns invalidates a view.

Pivoting affects an element only in the view where you pivot the element. It does n the same element in another view. Note: You cannot pivot columns in an XML target.

Figure 2-19 shows the ADDRESS element of the StoreInfo XML file pivoted into two sets address columns: Figure 2-19. ADDRESS Element Occurrences Pivoted into Columns

First occurrence of Address pivoted to home address columns with prefix HOM_.

Second occurrence of

Address pivoted to office

address columns with prefix OFC_.

XPath shows the two sets of columns that come from the same elements. The first and second occurrences of Address represented as columns in the group:

Using Multiple-Level Pivots

You can pivot more than one level of elements in a view by specifying fixed offsets for a multiple-occurring elements in the XPath for a column. For example, you might have the following elements in a view:

STORE PRODUCT+ PNAME ORDER+ ORDERNAME CUSTOMER+ CUSTNAME

The XPath STORE/PRODUCT[2]/ORDER[1]/ORDERNAME refers to the ordername for the first order for the second product in the store. The XPath STORE/PRODUCT[2]/ORDER/ CUSTOMER[1]refers to the first customer for all orders of the second p

If you pivot a view row, any column in the XML view that occurs below the view r have an XPath that matches XPath of the view row. For example, a view might have the following view row: Transaction/Trade[1]

The following columns have the same occurrence of Trade in the XPath: Transaction/Trade[1]/Date Transaction/Trade[1]/Price Transaction/Trade[1]/Person[1]/Firstname

You cannot create a column with the following XPath in the view: Transaction/Trade[2]/Date

Limitations PowerCenter does not support the following functions: ♦

♦

♦

Concatenated columns. A column cannot be a concatenation of two elements example, you cannot create a column FULLNAME that refers to a concatenation of tw elements FIRSTNAME and LASTNAME.

Composite keys. A key cannot be a concatenation of two elements. For exa cannot create a key CUSTOMERID that refers to a concatenation of two elements LASTNAME and PHONENUMBER. Parsed Lists. PowerCenter stores a list type as one string that contains all does not parse the respective simple types from the string.

Chapter 3

Working with XML Sources This chapter includes the following topics: ♦

Overview, 56

♦

Importing an XML Source Definition, 57

♦

Working with XML Views, 61

♦

Generating Entity Relationships, 63

♦

Generating Hierarchy Relationships, 64

♦

Creating Custom XML Views, 65

♦

Synchronizing XML Definitions, 68

♦

Editing XML Source Definition Properties, 70

♦

Creating XML Definitions from Repository Definitions, 72

♦

Troubleshooting XML Sources, 74

Overview

The Designer provides an XML Wizard that enables you to create XML definitions in the repository. You can import files from a URL or a local machine. You can also import relationa or flat file definitions from a PowerCenter repository. You can create XML definitions from the following types of files: ♦

XML files

♦

XML schema files

♦

DTD files

♦

Relational definitions

♦

Flat file definitions

Note: Informatica recommends you use an XML schema file to create XML source definitions.

When you create XML definitions, you import files with the XML Wizard and organize metadata into XML views. XML views are groups of columns containing the elements an attributes in the XML file. The wizard can generate views for you, or you can create cust views. For more information see “Working with XML Views” on page 61.

You use the XML Wizard to create relationships between views. You can create hierarchy relationships or entity relationships. For more information about creating hierarchy relationships, see “Generating Hierarchy Relationships” on page 64. For more informatio about creating entity relationships, see “Generating Entity Relationships” on page 63.

You can use the XML Wizard to synchronize an XML definitions against an XML schema f if the structure of the schema changes. For more information, see “Synchronizing XML Definitions” on page 68.

Importing an XML Source Definition

When you import a source definition from a DTD or an XML schema file, the Designe provide an accurate definition of the data based on the description provided in the D XML schema file.

When you import a source definition based on an XML file without an associated XML schema, the XML Wizard determines the types and occurrences of the data data represented in the XML file. To ensure that the wizard can provide an accurate d of the data, import from an XML source file that represents the occurrence and type o accurately as possible. Once you create an XML source definition, you cannot change any other source type. Conversely, you cannot change other types of source definitio XML definitions.

The XML Wizard uses keys to relate the XML views and reconstruct the XML hiera can choose to generate views and primary keys, or you can create views and spec When you create custom views, you can select roots and choose how to handle me expansion.

The XML Wizard saves the XML hierarchy and the view information as an XML schem the Repository. When you import an XML definition, your ability to change the cardin and datatype of the elements in the hierarchy depends on the type of file you are imp For example, DTD and XML files do not store datatype information. When you import files to create an XML definition, you can specify datatype, precision, and scale. If yo an XML schema, you can change the precision and scale.

You cannot create an XML source definition from an XML file of exported repository For more information about exporting and importing repository objects, see “Export Importing Objects” in the Repository Guide.

When you import an XML source definition, the Designer uses the encoding decla the XML file. For information on PowerCenter support for XML code pages, see “ Pages” on page 27.

Use the XML Wizard to import XML source definitions. To import an XML file: 1.

Click Sources-Import XML Definition. The Import XML Definition dialog box displays.

Select options to create XML views.

2.

Click Advanced Options.

The XML Views Creation dialog box opens:

3.

Use Advanced Options to specify the way the Designer creates and n You can change the following options: Table 3-1. Create XML Views Options

Option

Description

Override all infinite lengths

You can specify a default length for components with undefined leng such as strings. If you do not set a default length, the precision for th components sets to infinite. This can cause DTM buffer size errors w you run a session with large files.

Analyze elements/attributes inChoose this option to create global declarations of standalone XM standalone XML as global elements or attributes. Otherwise, they become local declarations declarations

If the schema has an enclosure element that can occur more than on Create an XML view for an enclosure and the enclosure element has child elements that can occur more t element once, you can create a separate view for it. An enclosure element is element that has no text content or attributes but has child element Pivot elements into columns

You can pivot leaf elements if they have an occurrence limit. You c pivot elements in source definitions only.

Ignore fixed element and attribute You can ignore fixed values in a schema and allow other element v values in the data.

Table 3-1. Create XML Views Options

Option

Description

Ignore prohibited attributes

In an XML schema or file, you can declare an attribute as prohibited. This allows you to restrict a complex type by prohibiting existing attributes. When you import the schema or file, you can choose to ignore the prohibited attributes.

You can choose to name XML columns by a sequence number or from Generate names for XML columns the element or attribute name in the schema. If you use names, you can add the XML view as a prefix to each column, and you can add the element name as a prefix to all the attributes. 4.

Click OK to apply changes.

5.

Choose the type of file to import. You can choose the following options: ♦

♦

6.

Import the definition from a local XML file or a URL. Create a source from an XML, DTD, or XML schema file. If you import an XML file with an associated DTD or schema, the XML Wizard uses the DTD or schema to generate th XML document. Import the definition from a non-XML source or target. Use this option source definition from flat file or relational definitions. The new source definition contains one group for each input definition plus a root element group. For more information, see “Creating XML Definitions from Repository Definitions” on page 72.

Click Next to complete the XML Wizard.

Multi-line Attributes Values The XML Wizard does not allow attribute values that contain new line characters and span more than one line. When you import a source or target definition from an XML file that contains an attribute with new line characters, the XML Wizard displays an error and does not import the file.

Working with XML Views

The Designer displays views as groups in the source definition. For more informa how the Designer generates groups, see “Working with XML Views” on page 36. Figure 3-1 shows the options for creating views in the XML Wizard: Figure 3-1. The XML Wizard Options to Create Views

You can choose from the following options to create XML views: ♦

♦

Generate entity relationships . If you create entity relationships, the XML generates views for multiple-occurring or referenced elements and complex typ information about generating entity relationships, see “Generating Entity Relat on page 63.

Generate hierarchy relationships. When you create hierarchical relationsh reference to a component expands under its parent element. You can generate or denormalized XML views in a hierarchy relationship. −

−

Normalized XML views. When you generate a normalized XML view, attributes appear only once. Multiple-occurring elements, or elements in onerelationships appear in different views related by keys. For more information the Designer generates normalized views, see “Generating Normalized Views page 38.

Denormalized XML views. When you generate a denormalized XML vie and attributes appear in one view. The Designer does not model many-to-man relationships between elements and attributes in an XML definition. For more information about how the Designer generates denormalized views, see “Gen Denormalized View” on page 40.

♦

♦

♦

Create a custom XML views. You can specify any global element as a root custom XML view. You can choose to reduce metadata explosion for elements, complex types, and inherited complex types. For information about creating custom views, see “Creating Custom XML Views” on page 65.

Synchronize XML definitions . You can update one or more XML definitions wh underlying schemas change. For information about synchronizing XML definitions, see “Synchronizing XML Definitions” on page 68.

Do not create XML views. When you choose not to create XML views, you them later in the XML Editor. For more information about creating views using the XM Editor, see “Creating and Editing Views” on page 81.

Once you create an XML view, you cannot change the configuration options you set for th view. For example, if you create a normalized XML view, you cannot change it to denormalized. You must import a new XML source definition and select the denormalized option.

Generating Entity Relationships

You can generate an XML hierarchy as an entity relationship model. When you gen views as entity relationships, the Designer completes the following actions: ♦

Generates views for multiple-occurring and referenced elements and com

Creates relationships between the views. When the Designer generates entity relationships, it generates different entities f types, global elements, and multiple-occurring elements based on the relationship schema. For a description of how the Designer generates Entity Relationships, se “Generating Entity View Relationships” on page 42.

♦

If you want to create different groups from the default groups, or if you want to c elements from different complex types, you can create custom XML views. For mo information about creating custom views, see “Creating Custom XML Views” on p

When you view an XML source definition in the XML Editor, you can see the relations between each element in the XML hierarchy. For each relationship between views, th Editor generates links based on the type of relationship between the views. To generate entity relationships: 1.

In the Source Analyzer, select Sources-Import XML Definition. The XML Wizar

2.

Navigate to the source you want to import and click Open.

3.

Enter a name for the file and click Next.

4.

Select Entity Relationships and click Finish.

The XML Wizard generates an XML definition that uses entity relationships.

Generating Hierarchy Relationships

When you create hierarchy relationships, each reference to a component is expanded under parent element. The XML Wizard selects the default root and uses default settings to create XML groups. For information about default settings for XML groups, see “Generating Hierarchical View Relationships” on page 38. To generate hierarchy relationships: 1.

In the Source Analyzer, select Sources-Import XML Definition. The XML Wizard open

2.

Navigate to the source you want to import and click Open.

3.

Enter a name for the file and click Next.

4.

Select Hierarchy Relationships.

5.

Select Normalized XML Views or Denormalized XML Views and click Finish The XML Wizard generates XML groups using hierarchy relationships.

Creating Custom XML Views

You can create custom XML views using the XML Wizard. When you create custom you can choose roots and specify how to generate metadata. You can choose to in exclude global elements based on whether the root information applies to the data yo to process. For example, if your schema contains information about stores and custo might want to create an XML definition that processes just the customers.

You can specify how you want to generate metadata associated with the view. You ca metadata explosion for elements, complex types, and inherited complex types by gen entity relationships. If you do not reduce the metadata references, the Designer gen hierarchy relationships and expands all child elements under their parent elemen

For more information about how the Designer generates hierarchy and entity rela see “Working with XML Views” on page 36. To create a custom view using the XML Wizard: 1.

In the Source Analyzer, select Sources-Import XML Definition. The XML Wizard opens.

2.

Navigate to the source you want to import and click Open.

3.

Enter a name for the file and click Next.

4.

Select Create Custom XML Views and click Next Note: To manually create all the XML views in the XML Editor, select Skip Create XML

Views. The XML Wizard creates the schema in the Repository, but does not cre XML views. For more information about creating views in the XML Editor, see “ an XML View” on page 81.

5.

Select root elements from the list of global root elements and click Next.

For information about selecting root elements, see “Selecting Root Elements” on page 6.

Choose to reduce metadata for elements, complex types, or inherited comp click Finish.

For information about reducing metadata explosion, see “Reducing Metadata Explosi on page 66.

Selecting Root Elements When you create a custom view, you can choose from the global elements in the imported schema to set the root for the XML instance document. Global elements are elements in an XML schema hierarchy that fall directly beneath the top root element. Figure 3-2 shows the Root selection page: Figure 3-2. Root Selection Page

Bookstore element selected as root. Book123 element cleared as root element.

Reducing Metadata Explosion

When the Designer creates an XML definition based on an XML schema that uses inheritance, it can expand the metadata for each referenced element or group within the that references it. Or, it can create a separate view for the referenced object and create relationships between it and other views.

If you use references within an XML schema, you might want to reduce the numb the Designer includes the metadata associated with a reference. The XML Wizard the following options to reduce metadata references: ♦

♦

♦

Reduce element explosion. The Designer creates a view for any multip element or any element that is referenced by more than one other element. Eac have multiple hierarchical relationships with other views in the definition.

Reduce complex type explosion . The Designer creates an XML view for complex type or multiple-occurring element. The XML view can have multiple t relationships with other views. If the schema uses inherited complex types, you reduce explosion of inherited complex types. Reduce complex type inheritance explosion . For any inherited type, the creates a type relationship.

Figure 3-3 shows the Reduce Metadata Explosions page: Figure 3-3. Reduce Metadata Explosions Page

When you reduce metadata explosion, the Designer creates entity relationships b XML views it generates.

Synchronizing XML Definitions

When you work with XML definitions, the files or sources you used to create the XML definition might change. For example, you might add a new element or complex type to a XSD file. You can synchronize an XML definition with any of the following repository definitions or files you used to create the XML definition: ♦

Relational source definitions

♦

Relational target definitions

♦

Flat files

♦

URLs

♦

XML files

♦

DTDs

♦

Schema files

Note: Informatica recommends you use schema files to synchronize XML definitions. To synchronize XML source definitions: 1.

In the Source Analyzer, select Sources-Import XML Definition. The XML Wiz

2.

Navigate to the repository definition or file that you used to create a sourc definition, and click Open.

3.

In Step 1 of the Wizard, click Next. The Wizard ignores any change you mak name.

4.

In Step 2 of the XML Wizard, choose to synchronize the XML definition and

5.

The XML Wizard skips to Step 5. In Step 5 of the XML Wizard, choose definition you want to synchronize.

The XML Wizard synchronizes the source with the selected definition.

You can use this method to synchronize XML target definitions. If you modify an X source definition, you may also need to synchronize the target definition. Note: Verify that you synchronize the XML definition with the source you used to create the

definition. If you synchronize an XML definition with a source that you did not use to the definition, the Designer cannot synchronize the definitions and loses it metadata Edit-Revert to Saved to restore the XML definition.

Editing XML Source Definition Properties

After you import an XML source definition, you can edit the source definition properties such as the definition name. You also add metadata extensions. To edit XML source definition properties: 1.

Right-click the source in the Source Analyzer and select Edit.

2.

On the Table tab, edit the following settings as necessary:

3.

Table Settings

Description

Rename button

Edit the name of the source definition and enter a business name.

Business Name

Descriptive name for the source definition. You can edit by clicking the Rename button.

Owner Name

Not applicable for XML files.

Description

Optional description of the source. Character limit is 2,000 bytes/K, where K is the maximum number of bytes for each character in the repository code page. Enter links to business documentation.

Database Type

Indicates the source or database type.

Code Page

Indicates the repository code page. The XML source definition uses the code page of the repository, not the code page defined in the XML source file.

On the Columns tab, you can view information about the columns in the def change column names or values, use the XML Editor.

You can view the following information: Columns Settings Description

Select Table

Displays the source definition you are editing.

Column Name

The name of the column.

Datatype

Displays datatype.

Precision

Displays length of data.

Scale

Displays number of decimal positions for numerical data.

Not Null

Indicates whether the column can accept nulls.

Key Type

When the Designer creates default views for XML sources, it generates the primary and foreign keys.

XPath

Indicates the element referenced by the current column in the XML hierarchy. XPath does not display for generated primary or foreign keys.

Business Name

User-defined descriptive name for the column. If it is not visible in the window, scroll to the right to view or modify the column.

4.

Click the Metadata Extensions tab to create, edit, and delete user-de extensions. For more information about metadata extensions, see the Repository

5.

Click OK.

6.

Choose Repository-Save to save changes to the repository.

Note: You cannot edit the Properties tab for an XML source definition.

Creating XML Definitions from Repository Definitions

You can import an XML source or target definition from relational or flat file definitions in the repository. When you import an XML definition from a repository definition, the XML Wizard creates an XML hierarchy from the relationships between the selected objects. The XML Wizard creates a root element for the hierarchy. You can choose a root from the groups you create, or you can create a separate root and relate the groups to it.

When you create an XML target, the XML Wizard generates keys to relate each group to root. To create an XML definition from repository sources or targets: 1.

In the Source Analyzer, click Sources-Import XML Definition. or In the Warehouse Designer, click Ta rgets-Import XML Definition.

2.

In the XML Import dialog box, click either Sources or Targets. Enter a root name. Selected Source or Target Definitions Add a definition.

List of Repository Sources and Targets Choose input type.

3.

Select a definition from the list of sources or targets. Click the Arrow butto definition to the selected source list.

You can select more than one input, and more than one input type. If the definitions are related through primary and foreign keys, the XML Wizard uses the keys to relate group when it generates the hierarchy. 4.

Click Open. The XML Wizard displays.

5.

Enter the Optional XML Rootname to have the XML Wizard create a separat the root element and relate all other groups to it. The root name defaults to XRoot. Remove the root name if you want to use one of the other groups as the root. The XM Wizard creates a group for each input source or target definition you select and gene

a primary key in each group. It creates a foreign key in each group that points group link key. 6.

Use the XML Wizard to generate the source or target groups.

Troubleshooting XML Sources How can I put two multiple-occurring elements that both have the same parent element into one view? For example, I would like to put all the elements of EMPLOYEE in one view:

Although in this example EMAIL and PHONE belong to the same parent element, they do not belong in the same parent chain. Ordinarily, you cannot put them in the same denormalized view.

To put all the elements of employee in one view, you need to pivot one of the multiple occurring elements. For example, you can create an EMPLOYEE view and follow these st to add all the elements: 1.

Add the EID and EMAIL elements to the EMPLOYEE view.

2.

Pivot the number of occurrences of EMAIL that you want to include. If you get warnin while pivoting any of the occurrences, confirm that you want to proceed.

3.

Add the PHONE element.

Pivoting in this case turns an element into a single occurring element in a view. After you pivot EMAIL, the Groups At element for the EMPLOYEE view is PHONE because it becomes the only multiple-occurring element in the view. I have the following element definition in my DTD:

How can I match the EMPNO and SALARY in the same view?

This example of a DTD element definition is ambiguous. It is equivalent to the following:

In this definition, there appears to be an EMPNO and a SALARY for each employee. However, when the definition is written this way, the number of occurrences for EMPNO separate from and may not match the number of occurrences of SALARY. You can try either of the following solutions: ♦

Rewrite the element definition to make it unambiguous.

In most cases, the EMPLOYEE element is more correctly defined in this way:

Redefined this way, there is one EMPNO and one SALARY for each EMPLOYEE, and both elements go into the same EMPLOYEE view.

♦

Leave the elements in separate views and use the source definition

Even when EMPNO and SALARY are in different views, you can still combine th a mapping. Use two instances of the same source definition and join them in a transformation. For an example of using two instances of an XML source in a ma “Using the XML Source Qualifier in a Mapping” on page 129.

I imported an XML file with the following structure: Book Name Book Name 051022906630

When I import this file, the Designer drops the ISBN element. Why does this happ How can I get the Designer to include the ISBN element? ♦

♦

Use the schema to import the XML definition . When you use an XML XML definition, the Designer reads the first element as simple content because it h child elements. Based on this, the Designer determines that the second instance o also simple content and discards the child element, ISBN. If you use a schema to im the definition, the Designer uses the structure defined in the schema to determine read XML data.

Ensure that the XML instance document accurately represents the assoc you use an XML file to import a source definition, ensure that the document is a representation of the structure in the corresponding XML schema.

Chapter 4

Using the XML Editor This chapter includes the following topics: ♦

Overview, 78

♦

Creating and Editing Views, 81

♦

Maintaining View Relationships, 89

♦

Viewing Schema Components, 93

♦

Validating XML Definitions, 99

♦

Setting XML View Options, 100

♦

Troubleshooting XML Editing, 102

Overview

When you import XML definitions with the Designer, you can create XML definitions usin entity or hierarchy relationships, custom XML views, or no views. After you create a definition, you must use the XML Editor to make changes to it.

Use the XML Editor to create views, modify components, add columns, and maintain vie relationships in the workspace. When you update an XML definition, the Designer propagates the changes to any mapping that includes the source. Some changes to XML definitions can invalidate mappings. Note: If you made significant changes to the source you used to create an XML definition, you

can synchronize it to the XML definition rather than editing the definition manually. For information about synchronizing XML source definitions, see “Synchronizing XML Definitions” on page 68. Figure 4-1 shows the XML Editor:

Figure 4-1. XML Editor

Navigator XML Workspace

Columns Window

The XML Editor uses icons to represent XML component types. To view a legend t describes the icons in the XML Editor, click View-Legend. The Legend dialog box displays.

The XML Editor contains the following windows: ♦

Navigator

♦

Workspace

♦

Columns

XML Navigator Window

The Navigator displays the schema in a hierarchical form and provides informatio selected components. You can expand a component to see components below it in hierarchy.

The Navigator toolbar provides shortcuts to most of the functions that you can comp the Navigator. It also provides navigation buttons that enable you to traverse compo the schema.

Properties Tab

The Properties tab displays information about a component you select in the Navig component is a complex element, you can view element properties in the schema, namespace, type, and content. When you view a simple element or attribute, the tab shows the type and length. The Properties tab also displays any annotations f components.

If you import the definition from an XML file, you can edit the datatypes and card from the Properties tab. If you create the definition from a DTD file, you can edit component type.

You can change the namespace prefix and location for an element if the schema u namespace. The prefix identifies the element or attribute declarations that belong namespace.

Actions Tab

The Actions tab lists options that you can use to see more information about a sel component. It also provides a utility that enables you to reverse changes you mak components in the Navigator.

The following options might display on the Actions tab, depending on the properties of t component you select: ♦ ♦

♦ ♦

♦

♦

ComplexType references. ComplexType hierarchy. component. Element references. Child components. uses.

Displays the references to a selected complex type.

Displays the complex types derived from the selected

Displays the components that reference the selected elem

Displays the global schema components that the selected

Revert simpleType. Changes the type, length, and precision values back to value if you have changed them. XML view references. component.

Displays all the XML views and columns that reference

Workspace Window

The XML Workspace window displays a graphical representation of the XML views and t relationships between the views. You can create XML views in the workspace and define relationships between views. The XML workspace toolbar provides shortcuts to most of the functions that you can do the workspace. You can modify the size of the XML workspace in the following ways: ♦

Hide the Columns window. Click View-Columns Properties.

♦

Hide the Navigator. Click View-Navigator.

♦

Reduce the workspace. Click the Zoom button on the Workspace toolbar.

Columns Window

The Columns window displays the columns for a view that you select in the workspace. Y can use the Columns window to name columns that you add. If you use pivoted columns, you use the Columns window to select and rename occurrences of multiple-occurring elements. You can also specify options such as the Not Null option to prevent null data in instance documents, and the Force Row, Hierarchy or Type Relationship Row, and Non-Recursive Row options. These options affect how the PowerCenter Server writes data to XML targets. For more information about the Columns window, see “Setting XML View Options” on page 100.

Creating and Editing Views

Use the XML Editor to create custom XML views, or to edit XML views that you creat the XML Wizard. To create views, you define the view and specify the columns in the the schema has multiple-occurring elements, you can specify which element occurre include in the view. You can also create special ports for XML target file names, and p through ports for XML Parser and Generator transformations. You can perform the following tasks to edit XML views: ♦

Create XML views.

♦

Add columns to views.

♦

Delete columns from views.

♦

Add reference ports.

♦

Add FileName columns.

♦

Add pass-through fields.

Creating an XML View

You can create views in the XML workspace. When you create an XML definition views, the XML workspace is empty when you open the XML Editor. You can create view and add normal columns, pivoted columns, or a view row column to the view Use the following procedure to add views to the XML definition. To create a new XML view in the workspace: 1.

Choose XML Views-Create XML View.

The XML Editor creates a blank view in the workspace and displays empty col the Columns window. 2.

Enter a name for the view in the Columns window. The name displays on the XML view in the workspace.

3.

Choose Components-XPath Navigator.

The XPath Navigator displays in the Navigator window. Mode Button

4.

Set the Column Mode in the XPath Navigator to View Row Mode in order to view row.

5.

Select the element in the Navigator and drag it to the view in the workspac Editor highlights the view row in blue.

The first time you add a column to a view, the Designer verifies the column can be a v row. This occurs even if you do not specify to add a view row. 6.

To change the view row to another column, right-click the appropriate row and select Set As view row.

Adding Columns to Views

You can add columns to XML views inside the XML workspace. To add columns, select the from the XPath Navigator. You can add components to an XML view when the following conditions are true: ♦

♦ ♦

♦

♦

The component path starts from the element in the schema that is the vie view. The component is not an enclosure element.

The component does not violate normalization or pivoting rules. For example, add more than one multiple-occurring element to a view. You can add an element with mixed content as two different columns: one type, and one as a complex type. Two views can share the same column, and a view might contain multiple columns.

To add columns to a view: 1.

Select the XML view in the workspace.

2.

Highlight a parent element in the Navigator that contains the compo add.

3.

Choose Components-XPath Navigator. The XPath Navigator displays in the Navigator window. Mode Button

4.

Click the Mode button and select to add a column, or a view row. Sele a pivoted column if you have a multiple-occurring element that you want to pi separate columns in the view. Advanced Mode also allows you to add normal c Note: You cannot create pivoted columns in XML target definitions.

5.

Drag a column from the XPath Navigator into the appropriate view in workspace. You can select multiple columns at a time.

The XML Editor validates the column you add. If the column is invalid for the view message displays in the status bar while you are dragging the column. As you add columns to views, they display in the Columns window.

Adding Pivoted Columns

You can pivot any multiple-occurring element in an XML view. You can also pivot view If you add a pivoted column, a default occurrence number displays in the Columns wi This number indicates which occurrence from the schema you want to use in the colu can change the occurrence number to correspond to any occurrence you need. Yo rename the column and add another occurrence. To add pivoted columns to a view: 1.

Select the XML view in the workspace.

2.

Highlight the element you want to pivot in the Navigator. Or, you can highligh element in the parent chain.

3.

Choose Components-XPath Navigator.

4.

Select Advanced Mode

5.

Drag the column to pivot from the XPath Navigator to the view in the XML w The Designer adds a default occurrence in the Columns window. This number indicat which occurrence from the schema you want to use in the column.

Figure 4-2 shows a pivoted Product Column and YTD Sales column. The view contain two occurrences of YTD Sales for the third occurrence of product for Store: Figure 4-2. Pivoted Columns

6.

To change the occurrence number, click the XPath link.

7.

Change the pivot to correspond to the occurrence you need and click OK. Figure 4-3. Specify XPath Pivot Column

8.

You can rename the column and add another occurrence. Note: You cannot change a pivot value in a column XPath if the pivot value is part of a

view row.

For more information about pivoting, see “Pivoting Columns” on page 51.

Expanding a Complex Type

A schema might have a complex type that is a base type for more than one type. For exam a Publication can be a Magazine or a Newspaper. When you create XML views, you can choose how to use Publication, either as a Magazine or a Newspaper type. When you view a complex type in the XPath Navigator, you can view its derived types.

To expand a complex type: 1.

Highlight the complex type in the XPath Navigator. The Expand Complex Types list shows any derived types.

2.

Select the type you want to use. If you add the component to an XML definition contains the type you select.

For more information about complex types, see “Complex Types” on page 19.

Applying Content to the Any Content Element

PowerCenter imports XML schemas that use the ANY content element, however, i use the element in a view, you must define content for it. When you import a sche ANY content element displays in the Schema Navigator with no properties. For example, the following schema element includes an ANY content element:

Figure 4-4 shows the ANY content element in the Schema Navigator: Figure 4-4. Any Content Element in the Schema Navigator

Any Content Element

To apply content to the ANY element: 1.

Click the any link in the Schema Navigator. The Edit Any or AnyAttribute type content dialog box displays.

Figure 4-5 shows the Edit Any or Any Attribute dialog box: Figure 4-5. Edit Any or Any Attribute Dialog Box

Add Type

2.

Click Add Type. A new row displays.

3.

Choose a type from the list of valid elements. Select a cardinality and click The substituted type displays in the Schema Navigator. Figure 4-6. New Type Added to the Any Content Element

ANY Content substituted type

Deleting Columns from Views You can delete columns from views. To delete a column from a view: 1.

Right-click the column in the view in the workspace.

2.

Select Delete This Column. The XML editor prompts you to confirm that you want to delete the column.

3.

Click Yes to confirm. The XML Editor removes the column from the view. The column still displays in the Navigator.

Deleting a Pivoted Column

To delete an occurrence of a pivoted column, select and delete the column from th window. To delete a pivoted column: 1.

Right-click the column you want to delete in the Columns window.

2.

Select Delete-Pivot.

3.

Click Yes to confirm the delete.

Adding a Pass-Through Port

If you use an XML Generator transformation in a pipeline, you can add pass-throu the transformation. Pass-through ports let you pass non-XML data through the XM Generator transformation. When you define the port in the transformation, you ad either the DataInput group in the XML Parser transformation or the DataOutput g the XML Generator transformation.

Once you generate this port, you can add a reference port to pass the data through a the XML definition. For more information about adding pass-through and reference see “Generating Pass-Through Ports” on page 147.

Finding Corresponding Reference Ports

If you have several pass-through ports in an XML definition, you can determine th corresponding reference ports. To determine the referenced port for a pass-through port: 1.

Right-click the pass-through port.

2.

Select Navigate to-Referenced Column. The XML Editor highlights the referenced column in the workspace.

Adding a FileName Column

In a session, the PowerCenter Server outputs a new target XML file each time a n value occurs in the data. You can add a FileName column to an XML view to gener file names for the XML files. The file name overrides the default output file name session properties. To create a FileName column in an XML view: 1.

Right-click the view in the XML Editor.

2.

Select Create FileName Column. The XML Editor creates a new string column in the view.

3.

Change the column name in Columns window.

4.

Exit the XML Editor. The column displays in your XML definition. The XPath is $Filename.

5.

You must set up an Expression transformation or other transformation in yo to generate a file name to pass to the XML target.

Note: When you use XML filename ports, you need to specify the directory name for the file.

For more information about the FileName column, see “Naming XML Files Dynamically” page 116.

Maintaining View Relationships

You can use the XML Editor to maintain relationships between views in an XML d You can maintain relationships by the following methods: ♦

♦

♦

Create a relationship between views . Define relationships between views workspace. Create a type relationship. type view in the workspace.

Define a type relationship between a colum

Recreate entity relationships. options as in the XML Wizard.

Generate new views and relationships usin

Creating Relationships Between Views

You can use the XML Editor to create hierarchical or inheritance relationships bet You cannot create a relationship between an XML Source and a non-XML Source. To create a relationship between XML views: 1.

Right-click the top of the child XML view in the workspace.

2.

Select Create Relationship.

3.

Move the pointer to the parent view to establish a relationship.

As you move the pointer, a link displays between the views, and the XML Editor ve that the relationship is valid. If a relationship is not valid, an error message displa status bar. 4.

If no error message displays, click the parent view to establish the r

The XML Editor creates the relationship and adds the appropriate foreign key

The XML Editor adds the foreign key to Employee.

5.

To view details about the relationship, place the cursor over the link betwe The Editor displays the relationship type and the primary and foreign keys.

Creating Type Relationships

You can create a type relationship between a column in a view and a type view. If the col is pivoted, you can choose which occurrences to include in the relationship. To create a type relationship: 1.

Right-click the column in the view you want to use.

2.

Select Create Relationship.

3.

If the column is pivoted, select which occurrence to use:

4.

Move the pointer to the type view to establish a relationship.

As you move the pointer, a link displays between the views and the XML Editor verifies that the relationship is valid. If a relationship is not valid, an error message displays in th status bar. 5.

If no error message displays, click the parent view to establish the relation The XML Editor creates the relationship:

Recreating Entity Relationships

You can recreate entity relationships for an XML definition using the Recreate Entity Relationships dialog box. This function enables you to generate new XML views using th same options as in the XML Wizard. When you regenerate views, you can choose to keep views you have already created. The XML definition contains all the views.

To recreate entity relationships for an XML definition: 1.

Open the XML definition in the XML Editor.

2.

Highlight the XML root in the Navigator.

If you highlight another component, the XML Editor uses that component as t 3.

Choose XML Views-Create Entity Relationship.

4.

Choose from the following options to reduce metadata explosion: ♦

♦

♦

♦ ♦

5.

Reduce element explosion. For any multiple-occurring or reference ele Wizard creates one XML view with multiple hierarchical relationships.

Reduce complex type explosion . For any multiple-occurring or referen type, the XML Wizard creates one XML view with multiple type relationship schema uses inherited complex types, you can also reduce explosion of inhe complex types. Reduce complex type inheritance explosion . For any inherited type, creates one XML view using multiple type relationships. Share existing XML views.

Refresh shared XML views.

Click Next.

Do not remove existing XML views.

Save existing views but update them.

The Recreate Entity Relationships dialog box displays.

Generate a view. Display child components.

Exclude a child component.

6.

To display a child component, select a shared element or complex type and name.

7.

To exclude a child component, clear the element in the Exclude Child Comp

To generate a new view, select the element or complex type. When you create the new entity relationships, you generate a view with that element as a view root.

Viewing Schema Components

You can perform the following tasks to view components in the Navigator and wor ♦

Update the namespace. Change the location of a schema.

♦

Sort components. Change the order of the schema components in the Navigator

♦

Navigate to components. Find components by navigating from a component to a component or area of the XML Editor window. Arrange views in the workspace. Arrange the views in the workspace hierarchic

♦

Search for components in the schema. Find components in the Navigator.

♦

Search for components in the workspace. Find components in the workspace.

♦

♦

♦ ♦

View XML metadata. View an XML file, schema, or DTD that the XML Editor cre from the XML definition. Preview XML data. Display an XML view using sample data from an Validate XML definition.

Validate views and relationships between the

Updating the Namespace

If the schema uses a namespace, you can change the prefix and schema location f To update the namespace schema location for an element: 1.

Select the element in the Navigator.

2.

Click the Properties tab.

3.

Click the Namespace value.

4.

Update the prefix and schema location.

The schema location for a global namespace is a schema file. You can have mu schema files for a single prefix only with global namespaces.

Sorting Components in the Components Pane You can sort components in the Navigator by type, hierarchy, or namespace. To sort components: 1.

Click Components-Sort By.

2.

Select Component Type, Hierarchy, or Namespace. The components in the Navigator display in the order you specify.

Navigating to Components

If you have large XML definitions you can quickly find components by using the Navigat option. To use this option, select a component to navigate from and select a navigation option. For example, if you click a foreign key, you can navigate to the associated primary or to the column in the Columns window. You can navigate between components in the workspace, the Columns window, and the Navigator. To navigate to components: 1.

Right-click a component in the workspace or in the Columns window.

2.

Select Navigate to.

3.

Select an available option.

You can select from the following options, depending on the component you select to navigate from: ♦ Schema component. Highlights the component in the Navigator. ♦

PowerCenter column. Highlights the column in the Columns window.

♦

Primary key. Highlights the primary key associated with a selected foreign key.

♦

♦ ♦

Referenced column. Highlights the referenced column associated with a pass-throu port in an XML Parser or Generator transformation. XPath Navigator.

Displays the path to the selected the component.

XML view. Highlights a view in the workspace that contains the selected the Columns window.

Arranging Views in the Workspace

When you work with views in the workspace you can move them around in order to add li or view relationships. You can reorganize the views into a hierarchical arrangement. To arrange views in the workspace, Choose Layout-Arrange, or you can right-click the workspace and select Arrange.

Searching for Components in the Schema

You can search for components in the schema and namespaces. You can search for components by name or by type. You can also specify properties to search for, such annotation, a fixed or default value, or a length. You can search for legal values usi enumeration property. To search for components in the schema: 1.

Choose Edit-Search In Schema. The Search Components dialog box displays.

2.

To search by component properties, click the Advanced Options link Properties you can search for.

3.

Enter a name, a type, or a property to search. If you want to search i namespace, click All, and select a namespace from the list.

4.

Click Search. The search results display in the bottom of the dialog box.

Click Advanced Options to search by component properties. Click a search result to view it in the Properties window.

5.

Click a search result to view the component in the Properties window

Searching for Components in XML Views

You can search for components and columns in an XML definition. If you search b component name, you can find all the associated columns in the definition. For exam you search for a component “Number,” the results contain the views and columns tha component “Number.”

To search using a partial key, enter the first few characters of the column or comp

To search for components in XML views: 1.

Choose Edit-Search XML Views. The Search XML Views and Columns dialog box displays.

2.

Enter search criteria. You can search for all column types, regular column generated keys, or other types. Other types include FileName columns, reference Po and pass-through fields.

3.

Click Search. The search results display in the bottom of the dialog box. Figure 4-7 shows the Search XML Views dialog box: Figure 4-7. Search XML Views and Columns

4.

To clear search fields, click New Search.

Viewing a Simple Type Hierarchy

You can view a hierarchy of the XML simple types in the schema definition. To view a hierarchy of simple types, choose View-SimpleType Hierarchy. A window displays a hiera of the simple types.

Viewing Complex Types in the Schema

You can display a hierarchy of the complex types in the schema definition. To view a hierarch of complex types, choose View-ComplexType Hierarchy. A window displays a hierarchy of th complex types in the schema. Select a component from the ComplexType Hierarchy window to navigate to it in the schema.

Viewing XML Metadata You can view an XML definition as an XML, DTD, or XML schema file. To view XML metadata: 1.

If you want to view the metadata as a sample XML document, choos component in the Navigator.

2.

Click View-XML Metadata. The View XML Metadata dialog box displays:

3.

Choose how you want to view the XML definition. If you use multiple namespaces, choose the namespace to use. A default application or text editor displays the metadata.

4.

To save a copy of the XML, DTD, or schema, choose Save As.

5.

Enter a new file name.

If the default display application is a text editor, you need to include the file su the file name and change the file type from.txt to All Files.

Previewing XML Data

You can preview XML data using an XML definition and an external XML file. To prev data, you must have a valid XML definition in the repository and some sample data in external XML file that is valid for the definition. You can view data for one XML view time.

For example, you might import a schema called EmployeeData.xsd and you create definition with several custom views. You have an XML file called Employees.xml contains sample data from 20 employees. You can preview each custom view using data from the XML file.

Use the following procedure to preview data for a view in the XML Editor workspace. To preview the XML data: 1.

Choose XML Views-Preview Data.

2.

Browse for the XML file containing data to preview.

If you have not selected a view from the Workspace, or if you have more than one vie selected, the Preview XML Data dialog box prompts you to select one view.

The Preview XML Data dialog box displays data from the XML file using the view tha you specify. If you want to use a different XML file, click the Select File icon on the dialog box. Figure 4-8 shows the Preview XML Data window: Figure 4-8. Preview XML Data

Validating XML Definitions You can validate an XML definition in the XML Editor. To validate an XML definition: 1.

Open the definition in the XML Editor.

2.

Click inside the workspace.

3.

Choose XML Views-Validate XML Definition. A Validate window displays the results:

Setting XML View Options

The Columns window displays the columns for a selected XML view. You can select the N Null option to prevent null data from occurring in the instance document. For source definitions, you can pivot a multiple-occurring leaf element. For more information about pivoting, see “Pivoting Columns” on page 51. Figure 4-9 show the Columns window: Figure 4-9. Columns Window

If you are working with an XML source definition, the Columns window contains XML View Options. These options give you flexibility about when you generate rows or foreign keys in session. You can select the following options: ♦

All hierarchy foreign keys

♦

Non-recursive row

♦

Hierarchy relationship row

♦

Force row

♦

Type relationship row

All Hierarchy Foreign Keys

Select the All Hierarchy Foreign Keys option to generate key values for all foreign keys i view. By default, the PowerCenter Server generates foreign key values for just the closes parent to the view. Select the All Hierarchy Foreign Keys option to generate values for a foreign keys in the view.

Non-Recursive Row Option

Select Non-Recursive Row for an XML view in a circular relationship. Use it to stop recur after one occurrence of the element during a session.

Hierarchy Relationship Row Option

Select Hierarchy Relationship Row to generate a row only when parent view data occ the child view data in a session. For example, a definition might have a hierarchy that an employee view and an address view. Employee is the parent view. During a session PowerCenter Server generates rows for the address view only when it has data fo employee view.

Force Row Option

Select Force Row to generate a row for the XML view even if the view row element do appear. For example, a view row contains product\sales, you can choose to output pr even if the view row has no sales data in it. You can use this option only for denormali source XML views.

Type Relationship Row Option

Select Type Relationship Row to generate a row only when parent type data occu derived view data in a session. For example, a definition might have a hierarchy t an Address type and a Canadian address type. During a session, the PowerCenter generates rows for the Canadian address view only when it has data for the Addr

Troubleshooting XML Editing

When I validate my XML definition I get an error that says my XML definition is too large. Why does this occur?

If you import an XML file that has components defined with infinite lengths, you can easily exceed the 500 MB limit for total column length. You can change the column lengths in the XML Editor, or you can set an option to override all infinite lengths and reimport the file. For more information about overriding infinite lengths, see “Importing an XML Source Definition” on page 57. I cannot find the DTD or XML schema file that I created when I viewed XML metadata.

The DTD or XML schema file that you can view is a temporary file that the Designer creates only for viewing. If you want to use the file for other purposes, save it with another name and directory when you view it. When I add columns to my XML source views, the hierarchy in my source XML file remains the same.

When you add columns to XML source views, you do not add elements to the underlying hierarchy. The XML hierarchy that you import remains the same no matter how you create the views or how you map the columns in a view to the elements in the hierarchy. You can modify the datatypes and the cardinality of the elements, but you cannot modify the struc of the hierarchy.

Chapter 5

Working with XML Targets This chapter includes the following topics: ♦

Overview, 104

♦

Importing an XML Target Definition from XML Files, 105

♦

Creating a Target from an XML Source Definition, 106

♦

Editing XML Target Definition Properties, 107

♦

Validating XML Targets, 112

♦

Using an XML Target in a Mapping, 114

♦

Troubleshooting, 118

Overview You can create XML target definitions in the following ways: ♦

♦

♦

Import the definition from an XML file. Create a target definition from an or XML schema file. You can import XML file definitions from a URL or a local machine If you import an XML file with an associated DTD, the XML Wizard uses the DTD to generate the XML document. For more information about importing XML files, see “Importing an XML Ta rget Definition from XML Files” on page 105.

Create an XML target definition based on an XML source definition. Drag an XML source definition into the Warehouse Designer. If you create an XML target definition, the Designer creates a target definition based on the hierarchy of the XML definition. For more information about creating an XML target from other definitions, s “Creating a Target from an XML Source Definition” on page 106. Create an XML target based on a relational file definition . You can import target definition from a relational or flat file repository definition. For more informati about importing XML definitions from relational or flat file definitions, see “Creating XML Definitions from Repository Definitions” on page 72.

In addition to creating XML target definitions, you can perform the following tasks with XML targets in the Warehouse Designer: ♦

♦

Edit target properties. Edit an XML target definition to add comments or upd reflect changed target XML, DTD, or XML schema files. For information on editing XM target definitions, see “Editing XML Target Definition Properties” on page 107.

Synchronize target definitions . You can synchronize your target XML definition updated schema if you need to make changes. Synchronizing enables you to update th XML definition instead of reimporting the definition when the schema changes. For more information about synchronizing XML definitions, see “Synchronizing XML Definitions” on page 68.

Importing an XML Target Definition from XML Files

You can import XML definitions from DTD, XML, or XML schema files. You can im local files or reference files using a URL. You can choose from the following options to create XML views: ♦

♦

♦

♦

♦

Create entity relationships . This option enables you to create entities components so they do not expand each time the data references them. You cre relationships between entities instead of creating one large hierarchy.

Create hierarchical relationships. This option creates a root and XML expand under this root. You can choose to create normalized or denormalized vi choose normalized, every element or attribute appears once. One-to-many rela become separate XML views with keys to relate the views. If you create denormali views, all elements and attributes display in one hierarchical group. For more info hierarchical relationships, see “Generating Hierarchical View Relationships” on p Create custom XML views. This option enables you to select multiple roots for XML views and select options for reducing metadata explosion. Skip creating XML views. This option creates no views. You can use create XML views in the workspace at a later time.

Synchronize XML definitions. You can use this option to avoid reimport definition when the associated XML schema file or DTD file changes.

Note: Informatica recommends that you import DTD or XML schema files instead of XML

files. PowerCenter has a size limit of 100K for XML files. If you import an XML fil associated DTD, the XML Wizard uses the DTD.

To import XML target definitions: 1.

In the Warehouse Designer, select Targets-Import XML Definition. Th Definitions window opens. The default displays schema files in a local folder.

2.

Click Local File or URL to browse for XML files.

3.

To browse for DTD or XML files, select the appropriate file extension Type list.

For more information about using the XML Wizard see “Importing an XML Sou Definition” on page 57.

Creating a Target from an XML Source Definition

When you want to create a target definition that closely resembles an existing source definition, you use the source definition or a shortcut to the source definition to create t target definition. You drag the XML source definition into the Warehouse Designer to cre an XML target definition or a relational target definition. Use the following guidelines to create XML targets: ♦

♦

When you create an XML target definition from an XML source definition, duplicate of the XML source definition.

A valid XML source definition does not necessarily create a valid XML targe To ensure that you create a valid target definition, validate the target.

Note: XML target definitions cannot contain pivoted columns.

Use the following guideline to create relational targets: ♦

♦

If you create a relational target, the Designer creates the relational target on the groups in the XML source definition. Each group in the XML source definition becomes a target definition. The Designer creates the same relationship between the target definitions relationship among the groups in the source definition.

To create a target definition from an XML source definition: 1.

Drag an XML source definition from the Navigator into the Warehouse Desi workspace. The XML Export dialog box appears:

2.

Select the type of target you want and click OK. The target definition appears in the Warehouse Designer workspace. If you select relational targets, more than one target definition might appear in the workspace, depending on the source.

3.

Choose Repository-Save to save the target definition in the repository.

Editing XML Target Definition Properties

After you create an XML target definition, you can edit the properties to reflect c the target data, to add business names and comments, or to change the code pag To edit XML target properties: 1.

Open the XML target in the Warehouse Designer.

2.

Right-click and select Edit.

3.

On the Table tab, edit the settings as necessary: Table Settings

Description

Rename button

Edit the name of the target definition and enter a business name.

Business Name

Descriptive name for the target table. Edit the Business Name using the Renam button.

Constraints

Not applicable to XML targets. Any entry is ignored.

Creation Options Not applicable to XML targets. Any entry is ignored. Description

Optional description of target table. Character limit is 2,000 bytes/K, where K is the maximum number of bytes for each character in the repository code page. Enter links to business documentation.

Code Page

Select the code page to use in the target definition. For more information abou XML code pages, see “Code Pages” on page 27.

4.

Table Settings

Description

Database Type

Indicates that the target definition is an XML target.

Keywords

Allows you to keep track of your targets. As development and maintenance work continues, the number of targets increases. While all of these targets may appear in the same folder, they may all serve different purposes. Keywords can help you find related targets. Keywords can include developer names, mappings, or the associated schema. You can use keywords to perform searches in the Repository Manager. For details on keyword searches in the Repository Manager, see “Using the Repository Manager” in the Repository Guide.

On the Columns tab, you can view XML column definitions.

The Columns tab displays the following properties: Columns Settings Description

Select Table

Displays the target definition you are editing. To choose a different definition to edit, select one from the list of definitions you have available in the workspace.

Column Name

Name of the column.

Datatype

XML datatype for the column.

Precision

Size of column. You can change precision only for some datatypes, such as string.

Scale

Number of decimals in a decimal datatype.

Columns Settings Description

5.

Not Null

Indicates if the column can be blank.

Key Type

The type of key the XML Wizard generates to link the views.

XPath

The path through the XML document hierarchy that enables you to locate an item.

On the Properties tab, you can modify the transformation attributes o definition. If you are using a source-based commit session or Transaction Cont transformation with the XML target, you can define how you want to flush dat target. For more information, see “Working with XML Targets in a Session” on

You can edit the following attributes: Columns Settings Description

Select Table

Displays the source definition you are editing. To choose a different source definition to edit, select it from the list.

Duplicate Group Choose one of these options to handle processing duplicate rows in the target: Row Handling - First Row. The PowerCenter Server passes the first duplicate row to the target. Rows following with the same primary key are rejected. - Last Row. The PowerCenter Server passes the last duplicate row to the target. - Error. The PowerCenter Server passes the first row to the target. Rows with duplicate primary keys increment the error count. The session fails when the error count reaches the error threshold. For more information about duplicate group row handling, see “Handling Duplicate Group Rows” on page 159. DTD Reference

DTD or XML schema file name for the target XML file. The PowerCenter Server adds the document type declaration to the XML file when you create it. For more information about using the DTD or schema file name, see “DTD and Schema Reference” on page 160

On Commit

The PowerCenter Server can generate multiple XML documents or append to one XML document after a commit. You can use one of the following options: - Ignore Commit. The PowerCenter server creates an XML document and writes to it at end of file only. - Create New Document. Creates a new XML document at each commit. - Append to Document. Writes to the same XML document after each commit. For more information about flushing XML on commits, see “Flushing XML on Commits” on page 160.

Cache Directory The directory for the XML target cache files. The default is the $PMCacheDir server variable. For more information about working with caches, see “XML Caching Properties” on page 162. Cache Size

6.

The total size in bytes for the XML target cache. The default is 10,000,000 bytes.

On the Metadata Extensions tab, you can create, modify, delete, and promo reusable metadata extensions, as well as update their values. You can also update the

values of reusable metadata extensions. For more information about metadata see the Repository Guide.

7.

Click OK.

8.

Choose Repository-Save.

Validating XML Targets

You can create customized XML views that describe how to extract data to an XML document. However, not all view structures or relationships between views are valid in a XML definition. Some view structures might be valid for an XML source, but not for an XML target. The Designer prevents you from creating ambiguous definitions. PowerCenter validates target XML views when you perform different tasks:

♦

The Designer does limited validation when you save or fetch an XML target repository. The XML Editor validates each step when you edit XML in the XML workspace.

♦

You can validate a target definition in the XML Editor.

♦

PowerCenter validates XML target connections when it validates mappings.

♦

The Designer uses rules to validate the following types of relationships: ♦

Hierarchy relationships

♦

Type relationships

♦

Inheritance relationships

Hierarchy Relationship Validation The Designer uses the following rules to validate hierarchy relationships:

♦

A view that has a root at a type cannot be a standalone view. It must inheritance relationship or it must have a type relationship with another view. An XML target is invalid if it has no views that are rooted at an element. You must connect a view with a multiple-occurring view row to another view.

♦

Two views cannot have the same effective view row.

♦

An XML target is invalid if it has no view root at an element.

♦

♦

♦

♦

You can separate parent and child views by other elements, but if you have a choice of parents for a view, you must use the closest one. Determine the closest parent by the path of the effective view row. One parent comes before the other in the path. Choose the view that comes second in the path.

You must connect all views with the same view root in the same hierarchy. cannot contain multiple trees for the same view root. An XML view can have a hierarchical relationship to itself only if the view view root are identical for the view.

Type Relationship Validation

A type relationship is a relationship between a column and a view. It is not a relat between two views. The following rules apply to type relationships: ♦

♦

A column in view, V1, can have a type relationship to a view, V2, same type, or the V2 view root type is derived from the V1 view root. Both view must be global complex types. If a column in a view has a type relationship to another view, you column.

Inheritance Validation You can create two types of inheritance relationships with XML views: ♦

View-to-view inheritance. A view is a derived type of another view. global complex view roots.

A view can have an inheritance relationship to another view only if its view roo complex type derived from the view root type of the other view. A view can be a parent in multiple inheritance relationships, but a view can be only one inheritance relationship. ♦

Column-to-view inheritance. The column is an element of a local comp and the view is rooted at a global complex type, Type2. Type1 is derived from T

A column in a view can have an inheritance relationship to another view if the c local complex type and the type is derived from the view root type of the other

If a column in a view, V1, has an inheritance relationship to a view, V2, you can the content of V2 into view V1.

Using an XML Target in a Mapping

When you add an XML target to a mapping, you need to following mapping guidelines fo multigroup transformations. For more information about multigroup transformations, see Designer Guide. The following components affect how you map an XML target in a mapping: ♦

Active sources

♦

Root elements

♦

Target port connections

♦

Abstract elements

♦

Transaction control points

♦

FileName columns

Active Sources

An active source is an active transformation the PowerCenter Server uses to generate ro The PowerCenter Server can load data from different active sources to an XML target. However, all target ports within a single group must receive data from the same active s The following transformations are active sources: ♦

Aggregator

♦

Application Source Qualifier

♦

Custom, configured as an active transformation

♦

Joiner

♦

MQ Source Qualifier

♦

Normalizer (VSAM or pipeline)

♦

Rank

♦

Sorter

♦

Source Qualifier

♦

XML Source Qualifier

♦

Mapplet, if it contains one of the above transformations

Selecting a Root Element

If an XML definition has more than one possible root, you can specify a root element for target instance. To specify the root element: 1.

Right-click the target definition in the Mapping Designer and select Edit.

2.

Click the Properties tab.

3.

Click the arrow in the Root Element value column. The Select Root dialog box displays:

4.

Select an element from the list.

Connecting Target Ports

When a session uses a mapping that contains an XML target, the PowerCenter Serve able to create the XML hierarchy in the target and determine where a row of data fits the hierarchical structure. Use the following guidelines when you connect ports in a mapping: ♦

♦

♦

If you connect one port in a group, you must connect both the forei key ports for the group.

If you connect a foreign key port in a group, you must connect the port in the other group. If you do not connect the primary key port of the root g do not need to connect the associated foreign key ports in the other groups.

If you use an XML schema with a default attribute value, you must port to create the default attribute in the target. If you pass a NULL value thro connected port, the PowerCenter Server writes the default value to the target.

You need to ensure that the PowerCenter Server can create a valid XML hierarch session by correctly connecting the XML target ports in a mapping. When you sav validate a mapping with an XML target, the Designer validates the target port co

Connecting Abstract Elements

An abstract element cannot occur directly in an XML instance document. Instead, yo use an element derived from the abstract element. By default, the Designer creates a any abstract complex element. To reduce metadata, elements from the abstract type

repeat in any derived type. When you map data to the abstract type, you need to also ma data to at least one derived type.

During a session, if the PowerCenter Server loads data to an abstract type, then it should al have data for a non-abstract derived type associated with it. If the derived type has no data, then the PowerCenter Server does not write the abstract element in the target XML document. For more information about abstract elements, see “Abstract Elements” on page 21.

Flushing XML Data to Targets

You can flush data to an XML target at each commit point in a session, however each inp group must receive data from the same transaction control point in the mapping. When y create a session based on this mapping, you can append data to the XML file target at ea commit or create a new file at each commit. You can specify either option with the On Commit target property.

When you connect the XML target input groups to multiple transaction control points, th PowerCenter Server writes the data to the XML file target after it processes all source r

For more information about transaction control points, see “Understanding Commit Poin in the Workflow Administration Guide .

Naming XML Files Dynamically

You can add a FileName column to an XML target definition to dynamically create file names for XML documents. When the PowerCenter Server passes data to the FileName column, it overrides the output file name in the target properties. For example, if you pass the string “Harry” to the FileName column, the PowerCenter Server names the XML document Harry. Note: If you are creating a new XML document on each commit, you need to dynamically

name each XML document you create. If you do not do this, the PowerCenter Server overwrites the XML document from the previous commit.

The PowerCenter Server generates a new XML document for each distinct primary key v in the root group of the target. You add a FileName column to set different names for ea document. Each name overrides the output file name in the session properties. For information about creating a FileName column in the XML Editor, see “Adding a FileName Column” on page 87. For information about multiple XML document output see, “Multiple XML Document Output” on page 163.

Example

The following example shows a mapping containing an XML target with a FileName colum The Expression transformation generates a file name from the Country XML element and passes the value to the FileName column. The mapping passes a country to the target ro

which is called Client. Whenever the Client value changes, the PowerCenter Serv new XML file.

Figure 5-1 shows the mapping containing an XML target with a FileName column Figure 5-1. Filename Column in a Mapping

Define the filename.

Filename Column

The PowerCenter Server passes the following rows to the target: Country,Region,Revenue USA,region1,1000 France,region1,10 Canada,region1,100 USA,region2,200 USA,region3,300 USA,region4,400 France,region2,20 France,region3,30 France,region4,40

The session produces the following files by country name: Canada.xml France.xml USA.xml revenue_file.xml.lst

Troubleshooting I imported a source definition from an XML file. Then I imported a target definition from the same XML file. The default groups for the source and target definitions are not the same.

The XML Wizard does not always create the same group structure for a source definition a target definition if you change some of the options when you import the target.

For example, the ContactInfo element in the following DTD is an enclosure element. It h no text content, but has maxOccurs > 1. The child elements also have maxOccurs > 1.
ADDRESS (STREETADDRESS,CITY,STATE,ZIP)> STREETADDRESS (#PCDATA)> CITY (#PCDATA)> STATE (#PCDATA)> ZIP (#PCDATA)>



If you do not create an XML view for an enclosure element in the source definition, you not create the Contactinfo element in the source. The XML Wizard creates the following source and target definitions: Source Definition for HR.dtd

Target Definition for HR.dtd

The wizard does not include the ContactInfo element in the source definition beca option setting. However, the wizard includes the ContactInfo element in the targe

The XML target created from my relational sources contains all elements, but no a How can I modify the target hierarchy so that I can mark certain data as attribute

You cannot modify the component types that the wizard creates from relational ta However, you can view a DTD or AN XML schema file of the target XML hierarchy. Sa DTD or XML schema file to your own directory and filename. Open this new file and m the hierarchy, setting the attributes and elements as needed. Then, you can use this f import a target definition with a new hierarchy. For more information about viewing definitions, see “Viewing XML Metadata” on page 97.

Chapter 6

XML Source Qualifier Transformation This chapter includes the following topics: ♦

Overview, 122

♦

Adding an XML Source Qualifier to a Mapping, 123

♦

Editing an XML Source Qualifier Transformation, 125

♦

Using the XML Source Qualifier in a Mapping, 129

♦

Troubleshooting, 135

Overview Transformation type:

Active Connected

When you add an XML source definition to a mapping, you need to connect it to an XML Source Qualifier transformation. The XML Source Qualifier transformation defines the data elements that the PowerCenter Server reads when it runs a session. It determines how the PowerCenter reads the source data.

You can manually add a source qualifier transformation or you can create a source quali transformation by default when you add a source definition to a mapping. For more information about adding XML Source Qualifier transformations, see “Adding an XML Source Qualifier to a Mapping” on page 123.

You can edit some of the properties and add metadata extensions to an XML Source Qualifie transformation. For more information about editing an XML Source Qualifier, see “Editing an XML Source Qualifier Tr ansformation” on page 125.

When you connect an XML Source Qualifier transformation in a mapping, you must follo some rules to create a valid mapping. For more information about using an XML Source Qualifier transformation in a mapping, see “Using the XML Source Qualifier in a Mappin on page 129.

Adding an XML Source Qualifier to a Mapping

An XML Source Qualifier transformation always has one input/output port for every c in the XML source. When you create an XML Source Qualifier transformation for a so definition, the Designer links each port in the XML source definition to a port in the X Source Qualifier transformation. You cannot remove or edit any of the links. If you re XML source definition from a mapping, the Designer also removes the corresponding Source Qualifier transformation. You can link one XML source definition to one XML Qualifier transformation.

You can link ports of one XML Source Qualifier group to ports of different transforma to form separate data flows. However, you cannot link ports from more than one grou XML Source Qualifier transformation to ports in the same target transformation.

If you drag columns of more than one group to a transformation, the Designer cop columns of all the groups to the transformation. However, it links only the ports o group to the corresponding ports of the columns created in the transformation.

You can add an XML Source Qualifier transformation to a mapping by dragging a source definition into the Mapping Designer workspace or by manually creating o

Automatically Creating an XML Source Qualifier Transformation

When you drag an XML source definition into the Mapping Designer workspace, t Designer creates an XML Source Qualifier transformation by default. To create an XML Source Qualifier transformation by default: 1.

In the Mapping Designer, create a new mapping or open an existing o

2.

Click and drag an XML source definition into the mapping.

The Designer creates an XML Source Qualifier transformation and links each p XML source definition to a port in the XML Source Qualifier transformation.

Manually Creating an XML Source Qualifier Transformation

You can create an XML Source Qualifier transformation in a mapping if you have that contains XML source definitions without Source Qualifiers or if you delete th Source Qualifier transformation from a mapping. To manually create an XML Source Qualifier transformation: 1.

In the Mapping Designer, create a new mapping or open an existing o Make sure that there is at least one XML source definition without a source q the mapping.

2.

From the menu, choose Transformation-Create.

The Create Transformation dialog box appears:

3.

Select XML Source Qualifier transformation, and type a name for the new transformation. The naming convention for XML Source Qualifier transformations is XSQ_TransformationName.

4.

Click Create.

The Designer lists all the XML source definitions in the mapping with no correspondi XML Source Qualifier transformations.

5.

Select a source definition and click OK.

The Designer creates an XML Source Qualifier transformation in the mapping and lin each port of the XML source definition to a port in the XML Source Qualifier transformation.

Editing an XML Source Qualifier Transformation

You can edit XML Source Qualifier transformation properties, such as transforma and description. To edit an XML Source Qualifier transformation: 1.

In the Mapping Designer, open the XML Source Qualifier transformati

2.

On the Transformation tab, edit the following properties: Transformation Setting

Description

Select Transformation

Displays the transformation you are editing. To choose a different transformation to edit, select it from the list.

Rename Button

Edit the name of the transformation.

Description

Description of the transformation.

3.

Click the Ports tab to view the details of the XML Source Qualifier transform Figure 6-1 shows the XML Source Qualifier transformation ports: Figure 6-1. XML Source Qualifier Transformation Ports

Generated Key Sequence Numbers

Use the Sequence column to set start values for generated keys in XML groups. You enter different values for each generated key. Whenever you change these values, th sequence numbers restart the next time you run a session using the transformation. 4.

Click the Properties tab to configure properties that affect how the Power runs the mapping during a session.

Table 6-1 lists the XML Source Qualifier properties you can edit: Table 6-1. XML Source Qualifier Properties

Properties Setting

Description

Select Transformation Displays the transformation you are editing. To choose a different transform edit, select it from the list.

5.

Tracing Level

Determines the amount of information about this transformation the PowerCe Server writes to the session log when it runs the workflow. You can override th tracing level when you configure a session.

Reset

At the end of a session, the PowerCenter Server resets the start values to th values for the current session. For more information, see “Setting Sequence for Generated Keys” on page 128.

Restart

At the beginning of a session, the PowerCenter Server starts the generated sequence for all groups at one. For more information, see “Setting Sequence for Generated Keys” on page 128.

Click the Metadata Extensions tab to create, edit, and delete user-de extensions. Add a metadata extension. Delete a metadata extension.

You can create, modify, delete, and promote non-reusable metadata extensions, a update their values. You can also update the values of reusable metadata extensio more information, see “Metadata Extensions” in the Repository Guide . 6.

Click OK.

7.

Choose Repository-Save to save changes.

Setting Sequence Numbers for Generated Keys

Each view in the XML Source Qualifier definition has a primary key and sequence value for the key. During a session, the PowerCenter Server generates keys from sequence values an increments the values.

At the end of the session, the PowerCenter Server updates each sequence value in the Repository to the current value plus 1. These values become the start values the next tim PowerCenter Server runs a session using the transformation. The Repository maintains the following sequence values: ♦

♦

♦

Default value. The sequence value for a key that displays in the XML Sourc when you first create it. The default value is 1 for each key. Start value. A sequence number value for a key at the start of a session. start values in the XML Source Qualifier transformation before you run a workflow. Current value.

A sequence value for a key during a session.

The start values for the generated keys display in the Sequence column in the XML Sour Qualifier. Note: If you edit the sequence start values on the Ports tab, you must save the changes and exit

the Designer before you run a workflow.

Changing Sequence Start Values

You can change sequence start values before or after a session by using the following op on the XML Source Qualifier transformation Properties tab: ♦

♦

Reset. At the end of a session, the PowerCenter Server resets the start val start values for the current session. For example, at the beginning of a session, the sta value of a key is 2000. At the end of a session, the current value is 2500. When the ses completes, the start value in the Repository remains at 2000. You might use this option when you are testing and you want to generate the same key numbers the next time yo run a session.

Restart. At the beginning of a session, the PowerCenter Server restarts the using the default value. For example, if the start value for a key is 1005, and you selec Restart, the PowerCenter Server changes the start value to 1. You might use this optio your keys are getting large and you will have no duplicate key conflicts if you restart numbering.

Using the XML Source Qualifier in a Mapping

Each group in an XML source definition is analogous to a relational table, and the treats each group within the XML Source Qualifier transformation as a separate s data.

The Designer enforces concatenation rules when you connect objects in a mappin Therefore, you need to organize the groups in the XML source definition so that e contains all the information you require in one pipeline branch.

Consider the following rules when you connect an XML Source Qualifier transform mapping: ♦

You can link ports from only one group in an XML Source Qualifier ports in one input group of another transformation. You can copy the columns of sever groups to one transformation, but you can link the ports of only one group to th corresponding ports in the transformation.

Figure 6-2 shows that ports of two groups in one XML Source Qualifier transfo cannot link to ports in one single input group transformation: Figure 6-2. Linking XML Source Qualifier Transformations to One Input Group

XML SQ Transformation Group 1 Column11 Column12 Group 2 Column21 Column22

♦

♦

X X

Single Input Group Transformation Column1 Column2 Column3 Column4

You can link ports from one group in an XML Source Qualifier transfor in more than one transformation. Each group in an XML Source Qualifier transformation can be a source of data for more than one pipeline branch. Data can pass from to several different transformations.

You can link multiple groups from one XML Source Qualifier transformat different input groups in a transformation. You can link multiple groups from one XML Source Qualifier transformation to different input groups in most multiple inpu transformation, such as a Joiner or Custom transformation. However, you can o multiple groups from one XML Source Qualifier transformation to one Joiner transformation if the Joiner has sorted input. To connect two XML Source Qual transformation groups to a Joiner transformation with unsorted input, you mus two instances of the same XML source. For an example on connecting two XML Qualifier transformations to Joiner transformations, see “Joining Two XML Sour Qualifier Transformation Groups” on page 133.

Figure 6-3 shows how to link multiple groups from one XML Source Qualifier transformation to different input groups in a multiple input group transformation: Figure 6-3. Linking XML Source Qualifier to Multiple Input Group Transformations

XML SQ Transformation1 Group 1 Column11 Column12 Group 2 Column21 Column22 XML SQ Transformation2 Group 1 Column11 Column12 Group 2 Column21 Column22

Joiner Transformation configured for sorted input Column1 Column2 Column3 Column4

(master) (master) (detail) (detail)

Custom Transformation Input Group 1 Column11 Column12 Input Group 2 Column21 Column22

XML Source Qualifier Transformation Example

This section shows an XML Source Qualifier transformation in a mapping. Figure the element hierarchy for the StoreInfo.xml file: Figure 6-4. Sample XML File StoreInfo.xml

You might want to calculate the total YTD sales for each product in the StoreInfo. regardless of region. Besides sales, you also want the names and prices of each p this, you need both product and sales information in the same transformation. Ho when you import the StoreInfo.xml file, the default groups that the Designer creat Product group for the product information and a Sales group for the sales inform

Figure 6-5 shows the default groups for the StoreInfo file with the product and sales information in separate groups: Figure 6-5. Invalid use of XML Source Qualifier Transformation in Aggregator Mapping

You cannot combine groups in a single input group transformation. The Designer cannot pass data from both Product and Sales groups to one input group.

Since you cannot link both the Product and the Sales groups to the same single input gr transformation, you can create the mapping in one of the following ways: ♦ ♦

Use a denormalized group containing all required information.

Join the data from the two groups using a Joiner transformation.

Using One Denormalized Group

You can reorganize the groups in the source definition so that all the information you nee in the same group. For example, you can combine the Product and Sales groups into one denormalized group in the source definition. One denormalized group enables you to pro all the information for the sales aggregation through one data flow.

Figure 6-6 shows a denormalized group Product_Sales containing a combination o from both the Product and Sales groups: Figure 6-6. Using a Denormalized Group in a Mapping

To create the denormalized group, edit the source definition in the Source Analyzer. Y either create a new group or modify an existing group. Add to the group all the produ sales columns you need for the sales calculation in the Aggregator transformation. Yo the XML Editor to create the group and validate it.

For more information about denormalized groups, see “Generating a Denormalize page 40.

Joining Two XML Source Qualifier Transformation Groups

You can also get data from two XML source groups into one data flow by joining d two groups using a Joiner transformation. When you configure the Joiner transfor sorted input, you can link two groups from one XML Source Qualifier transformat instance to the Joiner transformation. When you use a Joiner transformation conf unsorted input, you must use two instances of the same XML source and link a gr each XML Source Qualifier transformation instance to the Joiner transformation.

You can then send the data from the Joiner transformation to an Aggregator transf calculate the YTDSales for each product.

Figure 6-7 illustrates how you can create two instances of the same XML source and join from two XML Source Qualifier transformations: Figure 6-7. Using an XML Source Definition Twice in a Mapping

Troubleshooting When I drag two groups from an XML Source Qualifier transformation to a transformation, the Designer copies the columns but does not link all the ports.

You can link only one group of an XML Source Qualifier transformation to one transformation. When you drag more than one group to a transformation, the Des copies all the column names to the transformation. However, it links the columns first group.

I cannot break the link between the XML source definition and its source qualifier

The XML Source Qualifier transformation columns match the corresponding XML so definition columns exactly. You cannot remove or modify the links between an XML s definition and its XML Source Qualifier transformation. When you remove an XML so definition, the Designer automatically removes its XML Source Qualifier transforma

Chapter 7

Midstream XML Transformations This chapter includes the following topics: ♦

Overview, 138

♦

XML Parser Transformation, 139

♦

XML Generator Transformation, 141

♦

Creating a Midstream XML Transformation, 143

♦

Editing Midstream XML Transformation Properties, 144

♦

Generating Pass-Through Ports, 147

Overview

You can use XML definitions to read or create XML data. However, sometimes you need to extract or generate XML inside a pipeline. For example, you might want to send a message t a TIBCO target containing an XML document as the data field. In this case, you need to generate an XML document before sending the message to TIBCO. You can use a midstream XML transformation to generate the XML. You can create the following types of midstream XML transformations: ♦

♦

XML Parser transformation. The XML Parser transformation reads XML from port and outputs data to one or more groups. XML Generator transformation. The XML Generator transformation reads data or more sources and generates XML. It has a single output port.

Use a midstream XML transformation to extract XML data from messaging systems such TIBCO, MQSeries, or from other sources, such as files or databases. The XML transformation functionality is similar to the XML source and target functionality, excep parses the XML or generates the document in the pipeline.

Midstream XML transformations support the same XML schema components that the XM Wizard and Editor support. In addition, XML transformations support the following functionality: ♦

♦

♦

Pass-through ports. You can use pass-through ports to pass non-XML data thr midstream transformation. These fields are not part of the XML schema definition, bu you can use them to generate denormalized XML groups. You use these fields in the sa manner as top-level XML elements. You can also use a pass-through field as a primary for the top-level group in your XML definition. For more information, see “Generating Pass-Through Ports” on page 147.

Real-time processing. You can use a midstream XML transformation to proces BLOBs from messaging systems. Support for multiple partitions. partition.

You can generate different XML documents for

XML Parser Transformation Transformation type:

Active Connected

The XML Parser transformation is similar to an XML source definition. When the PowerCenter Server processes an XML Parser transformation, it reads a row of X parses the XML, and passes data through output groups. It also can pass non-XM

The XML Parser transformation has one input group, and one or more output groups input group has one input port, “DataInput” which accepts a binary or string data BL an XML document.

When you create a midstream XML Parser transformation, you use the XML Wizard t import an XML, DTD, or XML schema file. For example, you can import the following Employee DTD file:




ADDRESS (STREETADDRESS, CITY, STATE, ZIP)> STREETADDRESS (#PCDATA)> CITY (#PCDATA)> STATE (#PCDATA)> ZIP (#PCDATA)>





Figure 7-1 shows the XML Parser transformation that the Designer creates from the Emp DTD file: Figure 7-1. XML Parser Transformation

DataInput Group

XML Generator Transformation Transformation type:

Active Connected

You can use an XML Generator transformation to combine input that comes from sources to create an XML document. For example, you can use the transformation to the XML data from two TIBCO sources into one TIBCO target. One source might cont employee and salary information, and the other might have employee phone and information.

The XML Generator transformation is similar to an XML target definition. When t PowerCenter Server processes an XML Generator transformation, it writes rows of X The PowerCenter Server can also process pass-through fields containing non-XML d transformation. Figure 7-2 shows the XML Generator transformation: Figure 7-2. XML Generator Transformation

Group 1 TIBCO Source 1 Group 2

Group 3 TIBCO Source 2 Group 4

Output

TIBCO Target

The XML Generator transformation has one or more input groups and one output The output group has one port, “DataOutput,” which allows a binary or string data BL an XML document. This group also contains the output port when you create pass-th fields.

Figure 7-3 shows a sample XML Generator transformation: Figure 7-3. Sample XML Generator Transformation

DataOutput Group

Creating a Midstream XML Transformation

When you create a midstream XML transformation, you use the XML Wizard and Editor to define the XML groups. You can create the transformation in the Transfo Developer and the Mapping Designer. To create a midstream XML transformation: 1.

Open the Transformation Developer or the Mapping Designer.

2.

Click Transformation-Create. The Create Transformation dialog box displays.

3.

Select the Midstream XML Parser or Midstream XML Generator trans

4.

Enter a transformation name, and click Create. The Import XML Definition dialog box displays.

5.

Choose a file to import, and click Open. The XML Wizard displays.

6.

Create the XML definitions using the XML Wizard. For more informati XML Wizard, see “Importing an XML Source Definition” on page 57.

7.

Click Finish in the XML Wizard. The midstream XML transformation displays in the workspace.

8.

To edit the midstream XML transformation properties, double-click th in the workspace.

Editing Midstream XML Transformation Properties

You can edit some of the midstream XML transformation properties. However, because you use the XML Wizard and Editor to define the transformation, you must use these tools to change the XML definition.

If you create a midstream XML transformation in the Mapping Designer, the following ru apply: ♦

♦

If you make the transformation reusable, you can only change some of the properties from the Mapping Designer. You cannot add pass-through ports or metadat extensions. If you create a non-reusable transformation, you can edit the transformation Mapping Designer.

When you configure a midstream XML transformation, you can configure components on the following tabs: ♦

♦ ♦

♦

Transformation tab. Transformation tab. Properties tab.

You can rename the transformation and add a description

You can update the tracing level.

Metadata Extensions tab. You can extend the metadata stored in the reposit associating information with repository objects, such as an XML transformation. For m information, see “Metadata Extensions” in the Repository Guide .

XML Parser or XML Generator tab. You can create pass-through ports using Pass-through ports enable you to pass non-XML data through the transformation. For the XML Parser, you can choose to reset sequence numbers if you use sequence numbering to generate XML column names. For the XML Generator, you can choose to create a new XML document on commits.

Midstream XML Parser Tab Use the Midstream XML Parser tab to modify the size of the DataInput port. You can als add pass-through ports on this tab. For more information about pass-through ports, see “Generating Pass-Through Ports” on page 147. You can access the XML Editor from the Midstream XML Parser Ta b. Click the XML Edit button. Note: When you access the XML Editor, you cannot update Edit Transformations until you

exit the XML Editor.

Figure 7-4 shows the Midstream XML Parser tab: Figure 7-4. Midstream XML Parser Tab

Add a passthrough port. Edit the XML definition. Reset key sequence numbers to beginning values after session completes. Always start key sequence numbers at one.

Table 7-1 shows the options you can change on the Midstream XML Parser tab: Table 7-1. Midstream XML Parser Settings

Transformation Setting Description

Restart

Always start the generated key sequence for all groups at one.

Reset

At the end of a session, reset the value sequence for all generated keys in all groups. This resets the sequence number back to where it was previously.

Note: The options in Table 7-1 affect the generated key numbers. If you do not choose either

option, the sequence numbers in the generated keys increase from session to ses select the Restart or Reset option, it updates the Restart or Reset property that di Initialization Properties tab. You cannot change these options from the Initializati Properties tab, however.

Midstream XML Generator Tab

Use the XML Generator tab to modify the size of the DataOutput port. You can als pass-through ports on these tab. For more information about pass-through ports, “Generating Pass-Through Ports” on page 147.

You can access the XML Editor from the Midstream XML Generator Ta b. Click the XML Editor button. When you access the XML Editor, you cannot edit transformation properti until you exit the XML Editor. Figure 7-5 shows the Midstream XML Generator tab: Figure 7-5. Midstream XML Generator Tab

Table 7-2 shows the options you can change on the XML Generator transformation tab: Table 7-2. Midstream XML Generator Settings

Transformation Setting Description

On Commit

The PowerCenter Server can generate multiple XML documents after a commit. You can use one of the following options: - Ignore Commit. The PowerCenter Server creates the XML document and writes to it at end of file only. Use this option if two different sources are connected to the XML Generator transformation. - Create New Document. Creates a new XML document at each commit. Use this option if you are running a real-time session. When a session uses multiple partitions, the PowerCenter Server generates a separate XML document for each partition, regardless of On Commit settings. If you select Create New Document, the PowerCenter Server creates new documents for each partition.

Note: The Designer sets the transformation scope to all input when you choose to ignore

commits. Its sets the transformation scope to the transaction level if you set On Commit Create New Doc.

Generating Pass-Through Ports

Pass-through ports are columns that pass non-XML data through a midstream XM transformation. For example, you can pass message IDs with XML for MQSeries s targets. You can use message ID to correlate input and output messages for reque replies.

When you define a pass-through port in the midstream transformation, you add it the DataInput group in the XML Parser transformation or the DataOutput group i XML Generator transformation.

Once you generate the port, you use the XML Editor to add a corresponding refere another view in the XML definition. In the XML Parser transformation, the pass-th port is an input port, and the corresponding reference port is an output port. In t Generator transformation, the pass-through port is an output port and the associa reference port is an input port. See Figure 7-6 on page 149 for an example of an X definition with the pass-through port and the reference port. To create a pass-through port in a midstream XML transformation: 1.

Open the transformation in the Transformation Developer or Mapping Designe

2.

Double-click the transformation to open Edit Transformations.

3.

Click the Midstream XML Generator or Midstream XML Parser tab.

The window displays either the DataInput or DataOutput port depending on th transformation type. 4.

Click the Add button to add an output port for the pass-through. A default field displays in the Field Name column.

5.

Modify the field name. You can also modify type, precision, and scale file you used to create the definition.

6.

Click XML Editor to open the XML definition for the transformation. The XML workspace displays the XML views in your XML definition.

7.

Right-click a the top of a view to add the reference port.

8.

Select Add a Reference Port.

The Reference Port dialog box opens.

The dialog box lists the pass-through ports you added in the transformation. 9.

Select the pass-through port that will correspond to the new reference por and click OK.

The corresponding output reference port displays in the view. You can rename the por a more meaningful name in the Columns window.

Output Reference Port Input Pass-Through Port

10.

Click Apply Changes and exit the XML Editor.

11.

Click OK in the transformation.

12.

Choose Repository-Save.

Non-XML data comes through the input port called Pass_thru_field and passes through t corresponding COL_0 reference output port.

Figure 7-6 shows an XML Parser transformation with pass-through ports: Figure 7-6. Pass-Through Ports

Pass-Through Output Port Pass-Through Input Port

Chapter 8

Working with XML Sessions This chapter includes the following topics: ♦

Working with XML Sources in a Session, 152

♦

Working with XML Targets in a Session, 155

♦

Server Handling for XML Targets, 157

♦

Working with Midstream XML Transformations, 165

Working with XML Sources in a Session

When you create a session to read data from an XML source, you can configure source properties for that session. For example, you might want to override the source file name location in the session properties. Figure 8-1 shows the Mapping tab in session properties: Figure 8-1. Properties Settings for an XML Source

Table 8-1 describes the properties you can override for XML readers in a session: Table 8-1. XML Reader Options

XML Source Option

Required/ Optional

Description

Treat Empty Optional Content as Null

Treat empty XML components as null.

Source File Directory

Location of the XML file. By default, the PowerCenter Server looks in the server variable directory, $PMSourceFileDir. You can enter the full path and file name. If you specify both the directory and file name in the Source Filename field, clear this field. The PowerCenter Server concatenates this field with the Source Filename field when it runs the session. You can also use the $InputFile Name session parameter to specify the file directory. For details on session parameters, see “Session Parameters” in the Workflow Administration Guide.

Optional

Table 8-1. XML Reader Options

XML Source Option

Required/ Optional

Description

Source FilenameRequired

Enter the file name, or file name and path. Optionally use the $InputF session parameter for the file name. If you specify both the directory and file name in the Source File Dire field, clear this field. The PowerCenter Server concatenates this field w Source File Directory field when it runs the session. For example, if you “C:\XMLdata\” in the Source File Directory field, then enter “filename.x the Source Filename field. When the PowerCenter Server begins the se it looks for “C:\data\filename.xml”. For details on session parameters, see “Session Parameters” in the Administration Guide .

Source Filetype Required

The source filetype option enables you to configure multiple file source using a file list. Choose Direct or Indirect. The option indicates whether source file contains the source data, or whether it contains a list of files the same file properties. Choose Direct if the source file contains the so data. Choose Indirect if the source file contains a list of files. When you select Indirect, the PowerCenter Server finds the file list a each listed file when it runs the session. For details on file lists, see t Workflow Administration Guide .

Table 8-2 describes the properties you can override for an XML Source Qualifier i Table 8-2. XML Source Qualifier Options for a Session

XML Source Option

Required/ Optional

Validate XML Source

Required

The Validate XML Source option provides flexibility for validating an source against a schema or DTD file. Select Do Not Validate to skip validation, even if the instance document has an associated DTD or s reference. Select Validate Only if DTD is Present to validate only whe XML source has a corresponding DTD or schema file. The session fail instance document specifies a DTD or schema and one is not present. S Always Validate to always validate the XML file. The session fails if the D or schema does not exist or your data is invalid.

Partitionable

Optional

Allows you to create multiple partitions for the source pipeline.

Description

Server Handling for XML Sources

The PowerCenter Server can distinguish empty values from NULL values in an XML You can choose to pass empty strings as NULL values by selecting the Treat Empty C As NULL option in the Mapping tab of the session properties. By default, empty cont not NULL.

You can choose to omit fixed elements from the XML source definition. If the DTD or schema specifies a fixed or default value for an element, the value appears in the XM definition.

You can define attributes as required, optional, or prohibited in an element tag. You can specify fixed or default values for attributes. When a DTD or XML schema contains an attribute with a fixed or default value, the PowerCenter Server passes the value into the pipeline even if the element tag in the instance document does not contain the attribute. the attribute does not have a fixed or default value, the PowerCenter Server passes a NU value for the attribute. A parser error occurs when a required attribute is not present in element or a prohibited attribute appears in the element tag. The PowerCenter Server w this error to the session log.

Working with XML Targets in a Session

When you configure a session to load data to an XML target, you define writer pr the Mapping tab of the session properties. Figure 8-2 shows the XML writer properties in the session properties: Figure 8-2. Properties Settings for an XML Writer

Table 8-3 describes the properties you define in the XML Writer: Table 8-3. XML Writer Options

XML Targets Options

Required/ Optional

Description

Output File Directory Optional

Enter the directory name in this field. By default, the PowerCenter Server write output files in the server variable directory, $PMTargetFileDir. You can enter the full path and file name. If you specify both the directory and file name in the Output Filename field, clear this field. The PowerCenter Server concatenates this field with the Output Filename field when it runs the session. You can also use the $OutputFile Name session parameter to specify the file directory. For details on session parameters, see “Session Parameters” in the Workflow Administration Guide.

Output Filename

Required

Enter the file name, or file name and path. By default, the Workflow Manager names the target file based on the target definition used in the mapping: target_name .xml. If the target definition contains a slash character, the Workflow Manager replaces the slash character with an underscore. Enter the file name, or file name and path. Optionally use the $OutputFile Name session parameter for the file name. If you specify both the directory and file name in the Output File Directory field, clear this field. The PowerCenter Server concatenates this field with the Output File Directory field when it runs the session. For details on session parameters, see “Session Parameters” in the Workflow Administration Guide. Note: If you specify an absolute path file name when using FTP, the PowerCenter Server ignores the Default Remote Directory specified in the FTP connection. When you specify an absolute path file name, do not use single or double quotes.

Validate Target

Optional

Validate XML target data against the schema.

Format Output

Optional

Format the XML target file so the XML elements and attributes indent. Otherwise, each line of the XML file starts in the same position.

XML Datetime Format

Required

Choose local time, the difference in hours between the server time zone and Greenwich Mean Time or Greenwich Mean Time (UTC).

Null Content Representation

Required

Choose how to represent null content in the target. For more information, see “Null and Empty String” on page 158.

Empty String Content Required Representation

Choose how to represent empty string content in the target. For more information, see “Null and Empty String” on page 158.

Server Handling for XML Targets

You can configure some of the settings the PowerCenter Server uses when it load XML target: ♦

♦

♦

♦

♦

♦

♦

♦

♦

Character set. You can configure the PowerCenter Server to run session in either ASCII or Unicode data movement mode. For more information about c sets, see “Character Set” on page 157.

Special characters. The PowerCenter Server adds escape characters to characters in XML targets. For more information about special characters, see Characters” on page 158.

Null and empty string. You can choose how the PowerCenter Server empty strings when it writes data to an XML target. For more information abou empty strings, see “Null and Empty String” on page 158.

Handling duplicate group rows . You can choose how the PowerCenter rows with duplicate primary keys. For more information about handling duplica rows, see “Handling Duplicate Group Rows” on page 159.

DTD and schema reference. You can specify a DTD or schema file na XML file. For more information about specifying a DTD or schema reference, se and Schema Reference” on page 160.

Flushing XML on commits. You can configure the PowerCenter Server flush data to the target. For more information about flushing XML on commits, “Flushing XML on Commits” on page 160.

XML Caching properties. You can specify a cache directory for an XML information about XML caches, see “XML Caching Properties” on page 162.

Session logs for XML targets . You can view session logs for an XML information about locating XML session logs, see “Session Logs for XML Target page 162.

Multiple XML output. The PowerCenter Server outputs a new XML docum in the root changes. For more information about multiple XML output, see “Mu XML Document Output” on page 163.

Character Set

You can configure the PowerCenter Server to run sessions with XML targets in eithe or Unicode data movement mode. XML files contain an encoding declaration that ind the code page used in the file. The most commonly used code pages are UTF-8 and U PowerCenter supports UTF-8 code pages for XML targets only.

PowerCenter supports the same set of code pages for XML files that it supports fo databases and other files. For details on code page compatibility, see “Globalizati Overview” in the Installation and Configuration Guide . For a list of supported cod “Code Pages” in the Installation and Configuration Guide.

For XML targets, PowerCenter uses the code page declared in the XML file. When you run the PowerCenter Server in Unicode data movement mode, the XML target code page must b a superset of the PowerCenter Server code page and the source code page.

Special Characters

The PowerCenter Server adds escape characters to the following special characters in X targets: < & > ”

Null and Empty String

You can choose how the PowerCenter Server handles null data or empty strings when it w elements and attributes to an XML target file. By default, the PowerCenter Server does output element tags for NULL values, but it outputs tags with no content for empty strin

To change these defaults, you can change the Null Content Representation and Empty Strin Content Representation XML target properties. Choose one of the following options for eac property: ♦ ♦

No Tag.

Do not output a tag.

Tag with Empty Content.

Output just the tag.

You can choose to output NULL and empty string values as empty content in element tags or choose not to output element tags in an XML target. You can use both of the following XML target options for leaf elements or any element that has no multi-occurring elements: ♦

♦

NULL Content Representation. Allows you to choose how the PowerCenter Ser outputs NULL values. By default, the PowerCenter Server does not output element tags for NULL values. You can choose to output NULL values as empty content in element tags.

Empty String Content Representation. Allows you to choose how the PowerCen outputs empty string values. By default, the PowerCenter Server outputs empty conten element tags for empty string values. You can choose to not output element tags for a empty string values.

You can specify fixed or default values for elements and attributes. When an element in a XML schema or a DTD has a fixed or default value, the PowerCenter Server inserts the v instead of writing empty content. If the XML schema or DTD does not specify a value for attribute and the attribute has a NULL value, the PowerCenter Server omits the attribut

If a required attribute does not have a fixed value, the attribute must be a projected fiel When an attribute has a fixed value, the PowerCenter Server does not project the column the column does not appear in the target.

The PowerCenter Server does not output prohibited attributes to a target. An error occu when a prohibited attribute appears in an element tag. An error also occurs if a required attribute is not present in an element tag. The PowerCenter Server writes these errors t session log or the error log when you enable row error logging.

Table 8-4 shows the formats of XML file elements and attributes that contain null empty strings: Table 8-4. Null and Empty String Output for XML Targets

Type of Output Type of Data

Target File

Element

Null



Empty string



Null

...

Empty string

...

Attribute

Handling Duplicate Group Rows

Sometimes duplicate rows occur in source data. The PowerCenter Server can pas these rows to an XML target. You can configure duplicate row handling in the XM session properties. You can also configure the PowerCenter Server to write warni in the session log when duplicate rows occur.

The PowerCenter Server writes duplicate rows to the session log. It does not writ XML target rows to the reject file.

The PowerCenter Server handles duplicate rows passed to the XML target root gr differently than it handles rows passed to other XML target groups: ♦

♦

♦

For the XML target root group, the PowerCenter Server always passes target. When the PowerCenter Server encounters duplicate rows, it increases t of rejected rows in the session load summary.

For any XML target group other than the root group, you can configu row handling in the XML target definition in the Mapping Designer. If you choose to warn about duplicate rows, the PowerCenter Server rows for the root group to the session log. Otherwise, it drops the rows withou any error messages.

You can select which row the PowerCenter Server passes to the XML target: ♦

♦

First Row. The PowerCenter Server passes the first row to the target. PowerCenter Server encounters other rows with the same primary key, it incre number of rejected rows in the session load summary.

Last Row. The PowerCenter Server passes the last duplicate row to configure the PowerCenter Server to write the duplicate XML rows to the sessi setting the Warn About Duplicate XML Rows option.

For example, the PowerCenter Server encounters five duplicate rows. If you confi PowerCenter Server to write the duplicate XML rows to the session log, the Power Server passes the fifth row to the XML target and writes the first four duplicate ro session log. Otherwise, the PowerCenter Server passes the fifth row to the XML ta does not write anything to the session log.

♦

Error. The PowerCenter Server passes the first row to the target. When the Server encounters a duplicate row, it increases the number of rejected rows in the ses load summary and increments the error count.

When the PowerCenter Server reaches the error threshold, the session fails. When a se containing an XML target fails, the PowerCenter Server does not write any row to the XML target.

The PowerCenter Server sets an error threshold for each XML group. For details on th error threshold, see “Working with Sessions” in the Workflow Administration Guide .

DTD and Schema Reference

When you edit the XML target in the Warehouse Designer, you can also specify a DTD or schema file name for the target XML file. The PowerCenter Server adds a document type declaration or schema reference to the target XML file and inserts the name of the file y specify. For example, if you have a target XML file with the root element TargetRoot and set the DTD Reference option to TargetD oc .dt d , the PowerCenter Server adds the follow document type declaration after the XML declaration:

The PowerCenter Server does not check that the file you specify exists or that it is valid. The PowerCenter Server does not validate the target XML file against the DTD or schema file you specify. Note: An XML instance document must refer to the full relative path of a schema if a

midstream XML transformation is processing the file. Otherwise, the full path is not requ

Flushing XML on Commits

When you process an XML file or stream, the XML parser takes the entire XML file, parse and writes target XML data at end of file. The On Commit attribute enables you to periodically flush the data to the target before reaching end of file. You can flush data periodically into one target XML document, or you can generate multiple XML documen You might want to flush XML data in the following situations: ♦

♦

Large XML files. If you are processing a large XML file of several gigabytes PowerCenter Server may have reduced performance or it may run out of memory. You can set the On Commit attribute to Append to Doc. This flushes XML data periodically to the target document. Real-time processing. If you process real-time data that requires commits at use Append to Doc.

You can set the On Commit attribute to one of the following values: ♦ ♦

Ignore commit.

Generate and write to the XML document at end of file only

Append to document. Write to the same XML document at the end of each XML document closes at end of file. This option is not available for XML Generator transformations.

♦

Create new document. Create and write to a new document at each multiple XML documents.

Warning: When you create new a document on commit, you need to provide a unique filename

for each document. Otherwise, the PowerCenter Server overwrites the document on the previous commit.

You can flush data only if all groups in the XML target are connected to the same commit or transaction point. The transformation at the commit point generates deno output. The denormalized output contains repeating primary key values for all but th level node in the XML schema. The PowerCenter Server extracts rows from this outp each group in the XML target. You must have only one child group for the root group in the XML target.

Ignoring Commit

You can choose to generate the XML document after the session has read all the s records. This option causes a session to store all of the XML data in cache as it pr you should use this option when you are not processing a lot of data.

Appending to Document on Commit

When you append data to an XML document, you can use a source-based or user-defi commit in the session. Use a single point in the mapping to generate transactions. Al projected groups of an XML target must belong to the same transaction control unit.

For sessions using source-based commits, the single transaction point might be a nearest active source to the XML target, such as the last active transformation be target. For sessions with user-defined commits, the transaction point is a transact generating transformation.

Creating XML Documents on Commit

You can choose to generate a separate XML document for each commit. To generate XML output documents, set On Commit to Create New Document. To define the comm you can turn on source-based commit in the session, or you can generate the commit transaction generating transformation in the mapping. When the PowerCenter Server generates multiple XML documents, it creates file following name format: .number.xml

The base file name is a local file name for regular files and a remote file name for FTP Each XML document that the PowerCenter Server creates contains the base file nam number, starting with 1. The PowerCenter Server generates a file list after writing a output files for the XML target. The file list contains all the output file names. For loc the file list contains a list of all fully qualified file names, one per line in server code p FTP files, the file list contains file names with no path component.

The file list name syntax is: .lst

Each time you run a session, the PowerCenter Server overwrites the target XML files and generates a new file list. The PowerCenter Server places the file list into the XML target directory. Note: The first file that the PowerCenter Server generates does not contain a number. It is the

default base file. This file is included in the list file if the server writes data to it. If, how you use a FileName column to create file names, the server deletes the default file when processing completes.

If the base file name does not have an extension, the PowerCenter Server appends a per and the number at the end of the name. For example, instance “abc” becomes “abc.1”. If extension of a base file name is not “.xml” the PowerCenter Server retains the extension applies a period and a number before the extension. For example “abc.txt” becomes “abc.1.txt.”

XML Caching Properties

The PowerCenter Server uses a data cache to store XML row data while it generates an XML document. The cache size is the sum of all the groups in the XML target instance. It includes a primary key and a foreign key index cache for each XML group and one data cache for all groups.

The default cache size is 10,000,000 bytes. When the memory requirements exceed the cac size, the PowerCenter Server pages data to index and data files in the cache directory. When the session completes, the PowerCenter Server releases cache memory and deletes the cac files.

You can specify the cache directory and cache size for the XML target. The default cache directory is $PMCacheDir, which is a server variable that represents the directory where PowerCenter Server stores cache files by default.

Session Logs for XML Targets

When you run a session with an XML target, the PowerCenter Server writes the target name and group name into the session log. The session log lists the target and group names in the following format: Target Name : : Group Name.

For example, the following session log excerpt records the PowerCenter Server loading a table to the group DEPARTMENT in the target EMP_SALARY: WRITER_1_1_1> WRT_8167 Start loading table [EMP_SALARY::DEPARTMENT] at: Wed Nov 05 08:01:35 2003

Multiple XML Document Output

The PowerCenter Server generates a new XML document for each distinct primary k in the root group of the target. To create separate XML files, you must pass data to th node primary key. When the value of the key changes, the PowerCenter Server creat target file.

The PowerCenter Server creates multiple XML files when the root group has more th distinct primary key value. If the PowerCenter Server receives multiple rows with th primary key value, it chooses the first or last row based on the way you configure dup row handling.

If you pass data to a column in the root group, but you do not pass data to the primary the PowerCenter Server does not generate a new XML document. It writes a warning to the session log indicating that the primary key for the root group is not projected, PowerCenter Server is generating only one document.

Example

The following example includes a mapping that contains a flat file source of country n regions, and revenue dollars per region. The target is an XML file. The root view cont primary key, XPK_COL_0, which is a string. Figure 8-3 shows data mapped to the root of the XML definition: Figure 8-3. Mapping Data to an XML Root

Each time the PowerCenter Server passes a new country name to the root view it gene new target file. Each target XML file contains country name, region, and revenue data country. The PowerCenter Server passes the following rows to the XML target: Country,Region,Revenue USA,region1,1000 Canada,region1,100 USA,region2,200 USA,region3,300 USA,region4,400 France,region1,10 France,region2,20 France,region3,30 France,region4,40

The PowerCenter Server builds the XML files in cache. It creates one XML file for USA, file for Canada, and one file for France. If you specify “revenue_file.xml” as the output fi name in the session properties, the session produces the following files:

- contains the Canada rows revenue_file.1.xml - contains the France rows revenue_file.2.xml - contains the USA rows revenue_file.xml.lst - contains a list of each XML file the session created revenue_file.xml

If the data has multiple root rows with circular references, but none of the root rows has a nu foreign key, the PowerCenter Server cannot find a root row. It outputs the following messag in the session log file: XMLW_31108 Error: An appropriate start row was not found for XML root group [B] with circular reference. No output was generated.

You can add a FileName column to XML targets in order to name XML output documents based on data values.

Working with Midstream XML Transformations

When you configure a session using a midstream XML transformation, you define on the Mapping tab of the session properties. Figure 8-4 shows the XML Generator transformation session properties: Figure 8-4. Properties Settings for an XML Generator Transformation

Table 8-5 describes the properties you define in the XML Generator transformatio Table 8-5. XML Generator Transformation Session Options

XML Targets Options

Required/ Optional

Description

Validate Output

Optional

Validate XML target data against the schema.

Format Output

Optional

Format XML output so the XML elements and attributes indent. Other each line of the XML file starts in the same position.

XML Datetime Format

Required

Select local time, local time with time zone, or UTC. Local time with time is the difference in hours between the server time zone and Greenwich M Time. UTC is Greenwich Mean Time.

Table 8-5. XML Generator Transformation Session Options

XML Targets Options

Required/ Optional

Null Content Representation

Required

Select No Tag or Tag with Empty Content. For more information see “Null and Empty String” on page 158.

Empty String Content Required Representation

Select No Tag or Tag with Empty Content. For more information see “Null and Empty String” on page 158.

Duplicate Group Row Required Handling

Select First Row, Last Row, or Error. For more information, see “Handling Duplicate Group Rows” on page 159.

Orphan Row Handling

Required

Orphan rows are child rows that are missing parent data. Select Ignore to continue the session and ignore the orphan rows. Select Error to abort the session when orphan rows occur.

DTD Reference

Optional

Associated DTD or XML schema file name to add to the XML file the transformation creates. You must fully qualify this file name when you use it with XML Generator transformations. For more information about using the DTD or schema file name, see “DTD and Schema Reference” on page 160.

Cache Size

Required

The total size in bytes for the cache memory used by the transformation. The default is 10,000,000 bytes.

Cache Directory

Required

The directory for the XML cache files. The default is the $PMCacheDir server variable. For more information about working with caches, see “XML Caching Properties” on page 162.

Description

Figure 8-5 shows the XML Parser transformation session properties: Figure 8-5. Properties Settings for an XML Parser Transformation

Table 8-6 describes the properties you define in the XML Parser transformation: Table 8-6. XML Parser Transformation Session Options

XML Targets Options

Required/ Optional

Description

Validate XML Source Optional

Validate XML source data against the schema.

Treat Empty Content Required as Null

Select No Tag or Tag with Empty Content. For more information see “N Empty String” on page 158.

Appendix A

XML Datatype Reference This appendix includes the following topics: ♦

XML and Transformation Datatypes, 170

XML and Transformation Datatypes PowerCenter supports all XML datatypes specified in the W3C May 2, 2001 Recommendation. Table A-1 lists the XML datatypes and compares them to the transformation datatypes that display in the XML Source Qualifier transformation. For details on XML datatypes, see the W3C specifications for XML datatypes at http:// www.w3.org/TR/xmlschema-2. For more information about using transformation expressions and functions to convert datatypes, see “Functions” in the Transformation Language Reference . When you pass data to the target, make sure that it is in the correct format so that the PowerCenter Server writes the data correctly in the target XML file. You can change XML datatypes in XML definitions and in midstream XML transformations if you import an XML file to create the definition. You cannot change XML datatypes when you import them from an XML schema, and you cannot change the transformation datatype for XML sources within a mapping. Table A-1 shows the XML and corresponding Tr ansformation datatypes: Table A-1. XML and Transformation Datatypes

Datatype

Transformation

Range

anyURI

String

1 to 104,857,600 characters

base64Binary

Binary

1 to 104,857,600 bytes

boolean

Small Integer

Precision 5; scale 0

byte

Small Integer

Precision 5; scale 0

date

Date/Time

Jan 1, 1753 AD to Dec 31, 9999 AD (precision to the second)

dateTime

Date/Time

Jan 1, 1753 AD to Dec 31, 9999 AD (precision to the second)

decimal

Decimal

Precision 1 to 28; scale 0 to 28

double

Double

Precision 15, scale 0

duration

String

1 to 104,857,600 characters

ENTITIES

String

1 to 104,857,600 characters

ENTITY

String

1 to 104,857,600 characters

float

Double

Precision 15, scale 0

gDay

Integer

Precision 10; scale 0

gMonth

Integer

Precision 10; scale 0

gMonthDay

Date/Time

Jan 1, 1753 AD to Dec 31, 9999 AD (precision to the second)

gYear

Integer

Precision 10; scale 0

gYearMonth

Date/Time

Jan 1, 1753 AD to Dec 31, 9999 AD (precision to the second)

Table A-1. XML and Transformation Datatypes

Datatype

Transformation

Range

hexBinary

Binary

1 to 104,857,600 bytes

ID

String

1 to 104,857,600 characters

IDREF

String

1 to 104,857,600 characters

IDREFS

String

1 to 104,857,600 characters

int

Integer

Precision 10; scale 0

integer

Integer

Precision 10; scale 0

language

String

1 to 104,857,600 characters

long

Integer

Precision 10; scale 0

Name

String

1 to 104,857,600 characters

Ncname

String

1 to 104,857,600 characters

negativeInteger

Integer

Precision 10; scale 0

NMTOKEN

String

1 to 104,857,600 characters

NMTOKENS

String

1 to 104,857,600 characters

nonNegativeIntegerInteger

Precision 10; scale 0

nonPositiveInteger Integer

Precision 10; scale 0

normalizedString

String

1 to 104,857,600 characters

NOTATION

String

1 to 104,857,600 characters

positiveInteger

Integer

Precision 10; scale 0

QName

String

1 to 104,857,600 characters

short

Small Integer

Precision 5; scale 0

string

String

1 to 104,857,600 characters

time

Date/Time

Jan 1, 1753 AD to Dec 31, 9999 AD (precision to the second)

token

String

1 to 104,857,600 characters

unsignedByte

Small Integer

Precision 5; scale 0

unsignedInt

Integer

Precision 10; scale 0

unsignedLong

Integer

Precision 10; scale 0

unsignedShort

Integer

Precision 10; scale 0

Unsupported Datatypes PowerCenter does not support the following XML datatypes: ♦

binary

♦

century

♦

month

♦

nstring

♦

number

♦

recurringDate

♦

recurringDay

♦

recurringDuration

♦

timeDuration

♦

timeInstant

♦

timePeriod

♦

uriReference

♦

year

XML Date Format PowerCenter supports the following date, time, and datetime datatype format: CCYY-MM-DDThh:mm:ss:sss

You can use this format or any portion of this format if it conforms to the XML schema specifications. For example, an element of type date, time, or datetime may use either of following formats within a session: CCYY-MM

or CCYY-MM-DD/Thh

PowerCenter does not support negative dates for datetime format. The format of the first datetime element in an XML file determines the format of all subsequent values of the element. For example, if the first value of a datetime element uses the format CCYY-MM-DDThh:mm:ss

then all the subsequent values for the same element must follow the same format.

If the PowerCenter Server reads in a value for the same date, time, or datetime element has a different format, even if it is a valid dateTime format, it rejects the row. In this example, if the Informatica reads in a subsequent value with a different format CCYY-MM-DD

then it rejects the row even if the new format is also a valid datetime format.

Tip: If you generate an XML target from a relational source, ensure that you use the supported

date, time, and datetime format specified in the associated schema or DTD file to validation.

Index

A

C

absolute cardinality (XML) description 14 abstract elements description 21 using in a mapping 115 advanced mode using the XPath Navigator 83 all group (XML) description 24 all hierarchy foreign keys option overview 100 ANY element type (XML) in the XML Editor 85 overview 21 append to document flushing XML 161 arrange organizing views in workspace 94 atomic types description 17 attributes DTD syntax 7 XML 60

caching XML properties 162 cardinality absolute 14 relative 15 types 14 child element (XML) overview 4 choice group (XML) description 23 circular references description 47 non-recursive row option 100 code pages importing XML sources 57 XML file 6, 27 columns adding to XML views 82 deleting from an XML view 86 generating names 60 pivoting 51 size limitations 102 Columns window overview 81 setting XML View Options 100 commit flushing XML 160

complex types creating type relationships 90 description 19 expanding 84 extended 19 in XML schemas 19 restricted 19 viewing the hierarchy 96 composite keys (XML) overview 54 concatenated columns (XML) description 54 creating a new XML view in workspace 81 relationships between XML views 89 custom XML groups description 37 skip create view 65

D datatypes unsupported XML 171 XML metadata 13 default value XML attributes 115 default_value for a DTD attribute 7 deleting columns from XML view 86 denormalized XML groups description 40 documentation conventions xxvii description xxvi online xxvii DTD file description 7 metadata from DTD files 32 schema reference 160 DTM buffer size errors fixing 59 duplicate group row handling overview 159

E editing XML source definitions 70 XML Source Qualifier transformation 125

XML targets 104, 107 XML Views 82 element type ANY (XML) overview 21 elements description 3 DTD syntax 7 empty strings XML target files 158 enclosure element (XML) creating views for 59 XML hierarchy 4 encoding declaration describing code page in XML 6 entity relationships generating 42 generating XML views 63 enumeration description 17 searching for values 95 escape characters in XML targets 158

F facets description 17 filename column passing to XML target 116 #FIXED option description 7 flushing data appending to document 161 create new documents 161 ignore commit 161 XML targets 116 Force Row option overview 101 foreign keys generating values for all in a view 100

G generated keys description 36 sequence numbering 126 generating column names setting option 60 global declarations option to create 59

global element (XML) overview 4

H hierarchy (XML) description 13 hierarchy relationships circular references 47 element relationships 13 generating 38 Relationship Row option 101

legend XML Editor icons 79 limitations XML sources and targets 54 lists (XML) description 18 local element (XML) overview 4

M

mappings connecting abstract elements 115 using XML targets 114 XML Source Qualifier transformation 129 XML target ports 115 ignore commit message IDs flushing XML 161 XML Generator transformations 147 ignore fixed element metadata setting option 59 viewing as an XML schema 97 ignore prohibited attributes metadata explosion setting options 60 example 43 #IMPLIED option reducing 66 description 7 metadata extensions importing in XML source qualifiers 127 XML sources 57 in XML sources 71 XML targets 104, 105 in XML targets 110 infinite length columns midstream XML transformation overriding 102 creating 143 infinite precision general properties 144 overriding 59 Generator properties 145 Informatica overview 138 documentation xxvi Parser properties 144 Webzine xxviii pass-through ports 147 reset generated key sequence 145 mode button using the XPath Navigator 83 multiple XML output keys example 162 generated key sequence numbers 126 generating 161 generating multiple foreign keys in a view 100multiple-occurring element (XML) XML view keys and relationships 36 overview 4

I

K

L

N

layout arranging views 94 leaf element (XML) overview 4

name tag description 13 namespace description 11 updating in XML Editor 93

naming columns option 60 Navigator viewing complex types 96 viewing simple types 96 new line character XML attributes 60 non-recursive row option description 100 normalized XML groups description 38 null constraint (XML) description 16 null data XML target files 158

O on commit append to document 161 create new documents 161 ignore commit 161 options 160 options for creating views 59 output files session properties 156

P parent chain (XML) description 5 parent element (XML) description 5 partitionable XML source option 153 passive transformations XML Source Qualifier 122 pass-through ports adding to XML views 144, 145 finding the reference ports 87 generating 147 overview 87 pattern facet description 17

pivoting deleting pivoting columns 86 in Advanced Options 59 rules 51 with Xpath Navigator 82 XML columns 51 ports pass-through 147 XML Source Qualifier transformation 129 XML targets 115 PowerCenter Server handling XML targets 157 precision overriding infinite length 59 prefix updating namespace 93 previewing XML data 97 properties midstream XML transformation 144 XML caching 162 XML Generator transformation 145 XML Parser transformation 144

R reference ports adding to views 147 finding in workspace 87 relative cardinality (XML) description 15 #REQUIRED option description 7 reset midstream generated key sequence 145 restart midstream generated key sequence 145 root element specifying in a target 114

S saving metadata as XML/DTD/schema 97 schema files 9 location in namespace 93 searching for components 95 searching in XML Editor 95 simple types 17

transaction control point searching XML targets 116 in schema 95 transformation in XML Editor 95 select columns XML Source Qualifier 122 creating type relationships 90 transformation datatypes sequence comparing to XML 170 troubleshooting numbering generated keys 126 XML Source Qualifier transformation 135 sequence group (XML) XML sources 74 description 23 XML targets 118 server handling XML sources 153 type relationships XML targets 157 creating in the workspace 90 Type Relationship Row option 101 session logs XML targets 162 session properties output files, XML 156 XML Generator transformation 165 unions (XML) XML Parser transformation 167 description 18 XML sources 152 XML targets 156 simple types description 17 viewing a hierarchy 96 validating single-occurring element (XML) target rules 112 overview 5 validate target option 156 Skip Create XML Views XML definitions 99 setting custom views 65 XML source option 153 source filename view XML sources option 153 arranging in workspace 94 source filetype creating relationships 89 XML source option 153 generating entity relationships 42 sources hierarchical relationships 38 creating XML targets from XML sources 106 setting options 59 special characters view row parsing 158 description 49 start value using the Force Row option 101 generated keys 126 viewing substitution groups XML metadata as XML/DTD/Schema 97 example 45 in XML schema files 24 synchronizing XML definitions 68

U V

W

T targets duplicate group row handling 159 setting DTD/schema reference 160 specifying a root element 114

webzine xxviii

X XML attributes 60 character encoding 27 code pages 27, 57

comparing datatypes to transformation 170 XML metadata datatypes 170 cardinality 14 description 3 datatype 13 metadata from 31 description of types 11 previewing data 97 from DTD file 32 special characters 158 from substitution groups 24, 45 synchronizing definitions with schemas 68 from XML file 31 from XML schema 33 XML definitions hierarchy 13 creating from flat files 72 name 13 creating from relational files 72 null constraint 16 editing source definition properties 70 viewing as a schema 97 synchronizing with sources 68 XML Editor XML Parser transformation adding columns to views 82 example 139 Columns window 81 overview 138 creating a new view 81 session properties 167 deleting columns 86 XML rules editing views 82 pivoting groups 51 icons legend 79 XML source and target groups from relational tables 35 options 59 XML target port connections 115 pass-through fields 144, 145 XML schema searching for components 95 complex types 19 validating definitions 99 metadata from 33 setting default attributes 115 XML Generator transformation example 141 XML Source Qualifier transformation overview 138 adding to mapping 123 pass-through ports 147 automatically creating 123 session properties 165 editing 125 manually creating 123 XML groups overview 122 all group 24 port connections 129 choice group 23 troubleshooting 135 custom 37 using in a mapping 129 denormalized groups 40 element and attribute groups 23 XML sources modifying source groups 57 creating targets from 106 normalized groups 38 editing XML definitions 70 options for creating 59 importing 57 source and target groups from relational tables 35limitations 54 substitution groups 24, 45 partitionable option 153 server handling 153 XML hierarchy session properties 152 child element 4 source filename 153 creating hierarchy relationships 64 source filetype option 153 enclosure element 4 source location 152 global element 4 troubleshooting 74 leaf element 4 validate option 153 local element 4 multiple-occurring element 4 XML targets parent chain 5 active sources 114 parent element 5 creating 104 single-occurring element 5 creating from XML sources 106 duplicate group row handling 159

editing 104, 107 editing target properties 110 flushing data 116 groups from relational tables example 35 importing 104, 105 in sessions 155 limitations 54 multi-line attributes 60 On Commit session property 116 outputting multiple files 161 overview 104 port connections 115 server handling 157 session log entry 162 session properties 156 setting default attributes 115 setting DTD/schema reference 160 troubleshooting 118 using in mapping 114 validate option 156 XML View Options setting in the Columns window 100 XML views adding columns 82 adding pass-through fields 144, 145 creating 61 creating a new 81 creating hierarchy relationships 64 creating relationships between 89 creating with XML Wizard 61 generating custom views 65 generating entity relationships 63 pivoting columns 51 Skip Create XML View option 65 XML Wizard generating custom XML views 65 generating entity relationships 63 generating hierarchy relationships 64 importing sources 57 selecting root elements 66 synchronizing XML definitions 68 XPath See also XML Path expanding complex types 84 using the Navigator 82