IBM Informix XML User's Guide
Contents
1. IBM Informix Version 11 70 IBM Informix XML User s Guide E j 9 IBM Informix Version 11 70 IBM Informix XML User s Guide 9 Note Before using this information and the product it supports read the information in Notices on page B 1 This document contains proprietary information of IBM It is provided under a license agreement and is protected by copyright law The information contained in this publication does not include any product warranties and any statements provided in this manual should not be interpreted as such When you send information to IBM you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you Copyright IBM Corporation 1996 2010 US Government Users Restricted Rights Use duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp Contents Introduction About this publication Types of users Assumptions about your locale 2 What s new in IBM Informix XML User s Guide version dL 70 Example code conventions Additional documentation Compliance with Industry Standards Syntax Diagrams k ds Se How to read a command line syntax diagram Keywords and punctuation Identifiers and names How to Provide Documentation Feedback Chapter 1 Publishing SQL result sets in XML XML publishing The idsxmlvp virtual processor class Special charac2. XML functions 1 10 These functions generate an XML schema and result in XML format Purpose These functions are identical to genxml and genxmlclob but they also generate an XML schema The genxmlschema syntax genxm schma row element gt lt IBM Informix XML User s Guide The genxmlschemaclob syntax genxml schemac l ob row element gt lt Parameters row The rows and columns to return element The name of the element that contains the result set Example 1 SELECT genxmlschema customer customer FROM customers lt xml version 1 0 encoding en US 819 gt xs schema xmins xs http www w3 org 2001 XMLSchema targetNamespace http www ibm xmlns http www ibm com ElementFormDefault qualified gt lt xs element name customer gt lt xs complexType gt xs sequence lt xs element name customer num type xs serial gt lt xs element name fname type xs char 15 lt xs element name Iname type xs char 15 lt xs element name company type xs char 20 gt lt xs element name address1 type xs char 20 lt xs element name city type xs char 15 gt lt xs element name state type xs char 2 lt xs element name zipcode type xs char 5 gt lt xs element name phone type xs char 18 gt xs sequence xs complexType xs element xs schema customer
3. functions on page 1 9 Every XML document must have a header These functions provide a quick method of generating a header Evaluates an XPATH expression on an XML column document or string The extract and extractclob XML functions on page 1 3 Similar to the Oracle extract function Returns the value of the XML node The extractvalue and extractvalueclob XML functions on page 1 5 Similar to the Oracle extractvalue function Copyright IBM Corp 1996 2010 1 1 Table 1 1 XML publishing functions continued Action Function Comments Verify whether a specific The existsnode XML Similar to the Oracle exists node exists in an XML function on page 1 4 function document Parse an XML document to The idsxmlparse XML determine whether it is well function on page 1 11 formed XML publishing XML publishing provides a way to transform results of SOL queries into XML structures When you publish an XML document using the built in XML publishing functions you transform the result set of an SOL query into an XML structure optionally including an XML schema and header You can store the XML in the database server for use in XML based applications The input XML must be in nested elements within an XML document with each column being an XML element rather than an attribute This format is sometimes called
4. 1 lt xml version 1 0 lt xml version 1 0 gt lt xsl stylesheet encoding ISO 889 1 lt doc gt Hello xmlns xsl http www w3 org world doc 1999 XSL Transform version 1 0 gt lt xsl output encoding US ASCII gt lt xsl template match doc gt lt out gt lt xsl value of select gt lt out gt lt xsl template gt lt xsl stylesheet gt The XML document is transformed by the XSL stylesheet by calling xsltrasform select xsltransform info style from xmldocs where id 1 The following XML document is returned lt xml version 1 0 encoding US ASCII gt lt out gt Hello world out The xsltransformAsClob function Use this function to return a CLOB document resulting from an XSL transform Purpose The xsltransformAsClob function transforms an XML document with an XSL stylesheet and XSLT parameter The returned document is of type CLOB The xsltransformAsClob syntax xIstransformAsClob xml_document xs l_document gt lt Parameters xml document The XML document or fragment to transform The document can be of type LVARCHAR or CLOB xsl document The XSL stylesheet document that is applied to the XML document The XSL stylesheet can be of type LVARCHAR or CLOB The xsltransformAsBlob function 2 2 Use this function to return a BLOB document resulting from an XSL transform Purpose The xsltransformAsBlob function transforms an XML doc
5. DELETE syntax elements the elements must be separated by a comma If no separator is given assume that you use a blank to separate each syntax element If a syntax element is preceded by the symbol that element is defined elsewhere The string following the symbol is the name of a syntax fragment rather than a literal For example the line 2 1 0P1 means that you should refer to a separate syntax fragment The following words and symbols are used next to the dotted decimal numbers Specifies an optional syntax element A dotted decimal number followed by the symbol indicates that all the syntax elements with a corresponding dotted decimal number and any subordinate syntax elements are optional If there is only one syntax element with a dotted decimal number the symbol is displayed on the same line as the syntax element for example 5 NOTIFY If there is more than one syntax element with a dotted decimal number the symbol is displayed on a line by itself followed by the syntax elements that are optional For example if you hear the lines 5 5 NOTIFY and 5 UPDATE you know that syntax elements NOTIFY and UPDATE are optional that is you can choose one or none of them The symbol is equivalent to a bypass line in a railroad diagram Specifies a default syntax element A dotted decimal number followed by the symbol and a syntax element indicates that the syntax element is the default option for all syntax e
6. FOR UPDATE 4 FOR UPDATE are shown below the main FOR READ ONLY line one of which you might specify NEXT m PRIOR PREVIOUS I index name 34 table name Table Reference gt e NEXT T4 2 2 2 2 2 2 2 T PRIOR PREVIOUS V T4 2 2 2 2 2 2 T ijndex_name table name gt gt Table Reference gt lt The values below the main line are optional one of which you might specify If you do not specify an item the value above the line will be used as the default Optional items Several items are allowed a comma must precede each repetition Reference to a syntax segment Table Reference view table synonym Table Reference view table synonym Syntax segment Copyright IBM Corp 1996 2010 ix How to read a command line syntax diagram x Command line syntax diagrams use similar elements to those of other syntax diagrams Some of the elements are listed in the table in Creating a no conversion job n d device D database gt gt gt onpladm create job job pug t table gt lt 1 Setting the Run Mode S server T targ
7. Returns the value of the XML node in contrast to extract which returns the XML node Purpose Returns a value from evaluated XML column document or string For details on XPATH see http www w93 org TR xpath The extractvalue syntax extractvalue xml string xpath expression gt lt The extractvalueclob syntax gt gt extractvalueclob xml_string xpath_expression gt lt Parameters xml string The XML string or document to evaluate xpath expression An XPATH expression For extractvalue the string or document size cannot exceed 32739 For larger strings or documents use extractvalueclob Specify an absolute XPath string with an initial slash Omit the initial slash to indicate a path relative to the root node If no match is found these functions return an empty string Example 1 This example returns the value given name of the person who is identified in the XPATH expression No XML tags are returned SELECT extractvalue col2 personnel person 3 name given FROM tab Chapter 1 Publishing SQL result sets in XML 1 5 The output is the given name Jason Example 2 This example returns the number of docks in several cities SELECT warehouse name extractvalue e warehouse spec Warehouse Docks Docks FROM warehouses e WHERE warehouse spec IS NOT NULL WAREHOUSE NAME Docks Liverpool England 2 Taipei Taiwan 1 Buenos Aires Argenti
8. c from t order by c d AS vt cl c2 c3 The genxmlelem and genxmlelemclob XML functions These functions publish each element in the document separately Chapter 1 Publishing SQL result sets in XML 1 7 1 8 Purpose These functions return each column value as separate elements in contrast to genxml which returns column values as attributes of the row element The genxmlelem syntax gt gt genxml el em row element gt lt The genxmlelemclob syntax gt gt genxmlelemclob row element gt lt Parameters row The rows and columns to return element The name of the element that contains the result set Example 1 This example uses the table Table 1 3 on page 1 7 The first parameter specifies the table name to retrieve all columns from the table The second parameter specifies to place the output in an XML tag classes SELECT genxmlelem classes classes from classes where classid 1 The query returns one row classes row lt classid gt 1 lt classid gt lt class gt 125 lt class gt lt subject gt Chemi stry lt subject gt row classes Example 2 This query returns a list of all employees from the employee table SELECT genxmlelemclob employee employee FROM employee employee row lt givenname gt Roy lt givenname gt fami 1 yname gt Connor lt fami lt address gt lt address1 gt
9. format of syntax diagrams 1 keyboard 1 shortcut keys 1 syntax diagrams reading in a screen reader A 1 C compliance with standards vii D Disabilities visual reading syntax diagrams 1 Disability 1 Dotted decimal format of syntax diagrams 1 idsxmlvp 1 3 industry standards vii S Screen reader reading syntax diagrams 1 Shortcut keys keyboard 1 standards vii Syntax diagrams reading in a screen reader 1 V Visual disabilities reading syntax diagrams 1 X XML function existsnode 1 4 extract 1 3 extractclob 1 3 extractvalue 1 5 extractvalueclob 1 5 genxml 1 6 genxmlclob 1 6 genxmlelem 1 8 genxmlelemclob 1 8 genxmlquery 1 10 genxmlqueryclob 1 10 genxmlqueryhdr 1 9 genxmlqueryhdrclob 1 9 genxmlschema 1 10 genxmlschemaclob 1 10 idsxmlparse 1 11 xsltransform function 2 1 Copyright IBM Corp 1996 2010 xsltransformAsBlob function 2 2 xsltransformAsClob function 2 2 X 2 IBM Informix XML User s Guide Printed in USA SC27 3545 00
10. row customer num gt 101 lt customer lt fname gt Ludwig lt fname gt lt name gt Pauli lname lt gt 11 Sports Supplies lt company gt lt address1 gt 213 Erstwild Court lt address1 gt lt city gt Sunnyvale lt city gt lt state gt CA lt state gt lt zipcode gt 94086 lt zipcode gt lt phone gt 408 789 8075 lt phone gt lt row gt The idsxmlparse XML function Parse an XML document or fragment to determine whether it is well formed Purpose This function returns an XML document or fragment if the input XML is well formed The idsxmlparse syntax Chapter 1 Publishing SOL result sets in XML 1 11 1 12 idsxmlparse xml document Parameters xml document The XML document or fragment to parse to determine whether it is well formed Example 1 SELECT idsxmlparse purchaseOrder poNo 124356 gt lt customerName gt ABC Enterprises lt customerName gt lt i temNo gt F123456 lt itemNo gt lt purchaseOrder gt AS PO FROM systables where tabid 1 lt purchaseOrder poNo 124356 gt lt customerName gt ABC Enterprises lt customerName gt lt i temNo gt F123456 lt itemNo gt lt purchaseOrder gt IBM Informix XML User s Guide gt lt Chapter 2 Transforming documents with XSLT functions Apply XSL stylesheets and transforms to XML documents Extensible Stylesheet Language XSL uses XML elements to describe the for
11. you can repeat that same item more than once 2 If a dotted decimal number has an asterisk next to it and several items have that dotted decimal number you can use more than one item from the list but you cannot use the items more than once each In the previous example you can write HOST STATE but you cannot write HOST HOST 3 The symbol is equivalent to a loop back line in a railroad syntax diagram Specifies a syntax element that must be included one or more times A dotted decimal number followed by the symbol indicates that this syntax element must be included one or more times For example if you hear the line 6 1 data area you must include at least one data area If you hear the lines 2 2 HOST and 2 STATE you know that you must include HOST STATE or both As for the symbol you can only repeat a particular item if it is the only item with that dotted decimal number The symbol like the symbol is equivalent to a loop back line in a railroad syntax diagram Appendix Accessibility 3 A 4 IBM Informix XML User s Guide Notices This information was developed for products and services offered in the U S A IBM may not offer the products services or features discussed in this document in other countries Consult your local IBM representative for information on the products and services currently available in your area Any reference to an IBM product program or service is not intended to state or impl
12. 123 First Street lt address1 gt lt city gt Denver lt city gt lt state gt C0 lt state gt lt zipcode gt 80111 lt zipcode gt lt address gt lt phone gt 303 555 1212 lt phone gt lt row gt lt employee gt IBM Informix XML User s Guide The genxmlqueryhdr and genxmlqueryhdrclob XML functions Returns the result set of a query in XML with the XML header Purpose These functions are exactly the same as genxmlquery and genxmlqueryclob except they include an XML header An XML header specifies document properties such as the document encoding the document type definition DTD and XML stylesheet XSL The following example shows a typical XML header lt xml version 1 0 encoding UTF 8 gt lt DOCTYPE Server SYSTEM opt pdos etc pdosird dtd gt The encoding that is returned is the same as that of the operating system The genxmlqueryhdr syntax genxml queryhdr row query gt lt The genxmlqueryhdrclob syntax genxmlqueryhdrclob row query gt lt Parameters row The rows and columns to return query The SQL query whose result set will be returned as an XML document with a header Example 1 EXECUTE FUNCTION genxmlqueryhdr manufact set SELECT FROM manufact lt xml version 1 0 encoding IS0 8859 1 7 lt DOCTYPE manufact_set SYSTEM manufact_set dtd gt lt xml stylesheet type text xsl href manufact set xsl
13. L 92 Intermediate and Full Level and X Open SQL Common Applications Environment CAE standards The IBM Informix Geodetic DataBlade Module supports a subset of the data types from the Spatial Data Transfer Standard SDTS Federal Information Processing Standard 173 as referenced by the document Content Standard for Geospatial Metadata Federal Geographic Data Committee June 8 1994 FGDC Metadata Standard IBM Informix Dynamic Server IDS Enterprise Edition Version 11 50 is certified under the Common Criteria For more information see Common Criteria Introduction viii Informix XML User s Guide Syntax Diagrams Syntax diagrams use special components to describe the syntax for statements and commands Table 2 Syntax Diagram Components Component represented in PDF Component represented in HTML Meaning Statement begins FOR READ ONLY DD gt gt a n eee ua gt Statement continues on next line HERE RE Statement continues from previous line Pe Lene beck gt lt Statement ends SELECT Les SELECT 22 525 Required item ICE Sea Mo EE gece Optional item LOCAL oaia LOCAL ALL cmn FE ju gau item with m DISTINCT nly one item must be DISTINCT 4 __ UNIQUE i present UNIQUE rcc Optional items with choice
14. d stored procedure language SPL The IBM Informix Guide to SQL Tutorial shows how to use basic and advanced SOL and SPL routines to access and manipulate the data in your databases The IBM Informix Database Design and Implementation Guide shows how to use SQUL to implement and manage your databases See the documentation notes files for a list of the publications in the documentation set of your IBM Informix database server Types of users This publication is written for the following users Database users Database administrators Database server administrators Database application programmers This publication assumes that you have the following background A working knowledge of your computer your operating system and the utilities that your operating system provides Some experience working with relational databases or exposure to database concepts Some experience with computer programming and XML Some experience with database server administration operating system administration or network administration If you have limited experience with relational databases SOL or your operating system refer to the IBM Informix Getting Started Guide for your database server for a list of supplementary titles Assumptions about your locale IBM Informix products can support many languages cultures and code sets AII the information related to character set collation and representation of numeric data currency da
15. et Notes 1 SeepageZ 1 This diagram has a segment named Setting the Run Mode which according to the diagram footnote is on page Z 1 If this was an actual cross reference you would find this segment on the first page of Appendix Z Instead this segment is shown in the following segment diagram Notice that the diagram uses segment start and end components Setting the run mode Lal L a To see how to construct a command correctly start at the upper left of the main diagram Follow the diagram to the right including the elements that you want The elements in this diagram are case sensitive because they illustrate utility syntax Other types of syntax such as SQL are not case sensitive The Creating a No Conversion Job diagram illustrates the following steps 1 Type onpladm create job and then the name of the job 2 Optionally type p and then the name of the project 3 Type the following required elements e n e d and the name of the device e D and the name of the database IBM Informix XML User s Guide t and the name of the table 4 Optionally you can choose one or more of the following elements and repeat them an arbitrary number of times S and the server name T and the target server name The run mode To set the run mode follow the Setting the Run Mode segment diagram to type f optionally type d p or a and then optionally type lor u 5 Fol
16. eturn all rows The second parameter row is the name of the XML element that contains each returned row SELECT genxml classes row from classes The following lines show the results of the query in XML The attributes in the rows are the names of the table columns row classid 1 class 125 subject Chemistry gt lt row classid 2 class 250 subject Physics gt lt row classid 3 class 375 subject Mathematics gt lt row classid 4 class 500 subject Biology gt Example 2 From the same table as Example 1 this example returns only the columns classid and class SELECT genxml row classid class row from classes lt row classid 1 class 125 gt lt row classid 2 class 250 gt lt row classid 3 class 375 gt row classid 4 class 500 gt Example 3 This example uses genxmlclob because a large result set is expected SELECT genxmlclob row Customers Customid Orders Orderid Customers ContactName row From Customers Orders Where Customers CustomerID Orders orderid This sample output shows only the first three rows row Customerid ALFKI Orderid 10643 ContactName Maria Anders gt lt row Customerid ALFKI Orderid 10692 ContactName Maria Anders gt lt row Customerid ALFKI Orderid 10702 ContactName Maria Anders gt Enforcing order You can enforce the order of elements in XML document SELECT genxml row cl c2 c3 row FROM SELECT a b
17. gt manufact set row manu code SMT manu code manu name Smith manu name lead time 3 lead time lt row gt lt row gt lt manu_code gt ANZ lt manu_code gt lt manu_name gt Anza lt manu_name gt lt lead_time gt 5 lt lead_time gt lt row gt lt row gt lt manu_code gt NRG lt manu_code gt lt manu_name gt Norge lt manu_name gt lt lead_time gt 7 lt lead_time gt lt row gt lt row gt lt manu_code gt HSK lt manu_code gt Chapter 1 Publishing SQL result sets in XML 1 9 manu name Husky manu name lead time 5 lead time lt row gt lt manufact_set gt The genxmlquery and genxmlqueryclob XML functions These functions take a SQL query as a parameter and return the result set in XML Purpose Use these functions to retrieve results with each column in an element The genxmlquery syntax genxml query row query gt lt The genxmlclobelemclob syntax genxml queryc l ob row query gt lt Parameters row The rows and columns to return query The SQL query whose result set will be returned as XML Example 1 EXECUTE FUNCTION genxmlquery manufact set SELECT FROM manufact manufact set row manu 5 code manu name Smith manu name lead time 3 lead time lt row gt lt manufact_set gt The genxmlschema and genxmlschemaclob
18. her USERID or SYSTEMID but not both The dotted decimal numbering level denotes the level of nesting For example if a syntax element with dotted decimal number 3 is followed by a series of syntax elements with dotted decimal number 3 1 all the syntax elements numbered 3 1 are subordinate to the syntax element numbered 3 Certain words and symbols are used next to the dotted decimal numbers to add information about the syntax elements Occasionally these words and symbols might occur at the beginning of the element itself For ease of identification if the word or symbol is a part of the syntax element the word or symbol is preceded by the backslash character The symbol can be used next to a dotted decimal number to indicate that the syntax element repeats For example syntax element FILE with dotted decimal number 3 is read as 3 FILE Format 3 FILE indicates that syntax element FILE repeats Format 3 FILE indicates that syntax element FILE repeats Characters such as commas which are used to separate a string of syntax elements are shown in the syntax just before the items they separate These characters can appear on the same line as each item or on a separate line with the same dotted decimal number as the relevant items The line can also show another symbol that provides information about the syntax elements For example the lines 5 1 5 1 LASTRUN and 5 1 DELETE mean that if you use more than one of the LASTRUN and
19. l rights reserved If you are viewing this information softcopy the photographs and color illustrations may not appear Trademarks IBM the IBM logo and ibm com are trademarks or registered trademarks of International Business Machines Corp registered in many jurisdictions worldwide Other product and service names might be trademarks of IBM or other companies A current list of IBM trademarks is available on the Web at Copyright and trademark information at http www ibm com legal copytrade shtml Adobe the Adobe logo and PostScript are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States and or other countries Intel Itanium and Pentium are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries Java and all Java based trademarks are trademarks of Sun Microsystems Inc in the United States other countries or both Linux is a registered trademark of Linus Torvalds in the United States other countries or both Microsoft Windows and Windows NT are trademarks of Microsoft Corporation in the United States other countries or both UNIX is a registered trademark of The Open Group in the United States and other countries Other company product or service names may be trademarks or service marks of others Notices B 3 B 4 IBM Informix XML User s Guide Index A Accessibility 1 dotted decimal
20. lable This information contains examples of data and reports used in daily business operations To illustrate them as completely as possible the examples include the names of individuals companies brands and products All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental COPYRIGHT LICENSE This information contains sample application programs in source language which illustrate programming techniques on various operating platforms You may copy IBM Informix XML User s Guide modify and distribute these sample programs in any form without payment to IBM for the purposes of developing using marketing or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written These examples have not been thoroughly tested under all conditions IBM therefore cannot guarantee or imply reliability serviceability or function of these programs The sample programs are provided AS IS without warranty of any kind IBM shall not be liable for any damages arising out of your use of the sample programs Each copy or any portion of these sample programs or any derivative work must include a copyright notice as follows your company name year Portions of this code are derived from IBM Corp Sample Programs Copyright IBM Corp enter the year or years Al
21. lements that share the same dotted decimal number Only one of the syntax elements that share the same dotted decimal number can specify a symbol For example if you hear the lines 2 FILE 2 1 KEEP and 2 1 DELETE you know that KEEP is the default option for the FILE keyword In this example if you include the FILE keyword but do not specify an option default option KEEP is applied A default option also applies to the next higher dotted decimal number In IBM Informix XML User s Guide this example if the FILE keyword is omitted default FILE KEEP is used However if you hear the lines 2 FILE 2 1 2 1 1 KEEP and 2 1 1 DELETE the default option KEEP only applies to the next higher dotted decimal number 2 1 which does not have an associated keyword and does not apply to 2 FILE Nothing is used if the keyword FILE is omitted Specifies a syntax element that can be repeated zero or more times A dotted decimal number followed by the symbol indicates that this syntax element can be used zero or more times that is it is optional and can be repeated For example if you hear the line 5 1 data area you know that you can include more than one data area or you can include none If you hear the lines 3 3 HOST and 3 STATE you know that you can include HOST STATE both together or nothing Notes 1 If a dotted decimal number has an asterisk next to it and there is only one item with that dotted decimal number
22. llowing methods Send email to docinf us ibm com e Go to the information center at http publib boulder ibm com infocenter idshelp v117 index jsp and open the topic that you want to comment on Click the feedback link at the bottom of the page fill out the form and submit your feedback Add comments to topics directly in the Informix information center and read comments that were added by other users Share information about the product documentation participate in discussions with other users rate topics and more Find out more at http publib boulder ibm com infocenter idshelp v117 topic com ibm start doc contributing Feedback from all methods is monitored by the team that maintains the user documentation The feedback methods are reserved for reporting errors and omissions in the documentation For immediate help with a technical problem contact IBM Technical Support For instructions see the IBM Informix Technical Support website at http www ibm com planetwide We appreciate your suggestions O Copyright IBM Corp 1996 2010 xiii XIV Informix XML User s Guide Chapter 1 Publishing SQL result sets in XML Several functions let you publish XML from SQL queries There are two of XML functions for each action depending on the size of total result set after adding all the tags necessary to publish it in XML format One that returns a maximum of LVARCHAR 32739 One that returns a CLOB da
23. low the diagram to the terminator Keywords and punctuation Keywords are words reserved for statements and all commands except system level commands When a keyword appears in a syntax diagram it is shown in uppercase letters When you use a keyword in a command you can write it in uppercase or lowercase letters but you must spell the keyword exactly as it appears in the syntax diagram You must also use any punctuation in your statements and commands exactly as shown in the syntax diagrams Identifiers and names Variables serve as placeholders for identifiers and names in the syntax diagrams and examples You can replace a variable with an arbitrary name identifier or literal depending on the context Variables are also used to represent complex syntax elements that are expanded in additional syntax diagrams When a variable appears in a syntax diagram an example or text it is shown in lowercase italic The following syntax diagram uses variables to illustrate the general form of a simple SELECT statement SELECT column name FROM table name gt When you write a SELECT statement of this form you replace the variables column name and table name with the name of a specific column and table Syntax Diagrams Xi xii Informix XML User s Guide How to Provide Documentation Feedback You are encouraged to send your comments about IBM Informix user documentation Use one of the fo
24. mat of a document You can use the XSLT functions that IBM Informix provides to apply XSL transformations XSLT to XML documents resulting in a document in a different XML schema HTML PDF or any defined type XSL and XSLT are standards defined by the World Wide Web Consortium which you can find at http www w3 org xslt IBM Informix supports the XSLT version 1 0 standard for style sheets and uses the Xalan XSLT processor and the Xerces Java parser The documents take arguments of an XML file and a stylesheet The output is a new document whose type is determined by the XSLT transform The input arguments can be lvarchar clob or blob The xsltransform function Use this function to return a document up to 32 739 bytes Purpose The xsltransform function transforms an XML document with an XSL stylesheet and XSLT parameter The returned document is of type LVARCHAR The xsltransform syntax x lstransform xml 51 document gt lt Parameters xml document The XML document or fragment to transform The document can be of type LVARCHAR CLOB or BLOB xsl document The XSL stylesheet document that is applied to the XML document The XSL stylesheet can be of type LVARCHAR CLOB or BLOB Sample Column info contains an XML document and column style contains an XSL stylesheet O Copyright IBM Corp 1996 2010 2 1 Table 2 1 A row in table xmldocs id info style
25. na Seattle USA The genxml and genxmlclob XML functions 1 6 Return rows of SOL results as XML elements Use genxmlclob if the returned row is greater than LVARCHAR 32739 Purpose Use these functions to create an XML row element for each row that is returned from an SQL query Each column is an attribute of the row element Use genxml for returned row values that are LVARCHAR 32739 or less For larger values use genxmlclob which returns a CLOB These aggregate functions process the rows before an ORDER BY is completed If order is important use the derived table queries to get the result set in the correct order and then apply the function on the result set See Enforcing order on page for details The genxml syntax genxml root element rows gt lt The genxmlclob syntax genxmlclob root element rows gt lt Parameters root_element The table name or names of columns to return To return all columns specify the table name rows The name given to the XML element of the returned row Example 1 This example shows how to retrieve XML rows from an SQL query on the following table IBM Informix XML User s Guide Table 1 3 The classes table classid class subject 1 125 Chemistry 2 250 Physics 3 375 Mathematics 4 500 Biology The first parameter classes is the name of the table which indicates to r
26. navigation keys Related accessibility information IBM is committed to making our documentation accessible to persons with disabilities Our publications are available in HTML format so that they can be accessed with assistive technology such as screen reader software You can view the publications in Adobe Portable Document Format PDF by using the Adobe Acrobat Reader IBM and accessibility See the IBM Accessibility Center at http www ibm com able for more information about the IBM commitment to accessibility Dotted decimal syntax diagrams The syntax diagrams in our publications are available in dotted decimal format which is an accessible format that is available only if you are using a screen reader In dotted decimal format each syntax element is written on a separate line If two or more syntax elements are always present together or always absent together the elements can appear on the same line because they can be considered as a single compound syntax element Each line starts with a dotted decimal number for example 3 or 3 1 or 3 1 1 To hear these numbers correctly make sure that your screen reader is set to read punctuation All syntax elements that have the same dotted decimal number for example all syntax elements that have the number 3 1 are mutually exclusive Copyright IBM Corp 1996 2010 A 1 A 2 alternatives If you hear the lines 3 1 USERID and 3 1 SYSTEMID your syntax can include eit
27. ocessor is created automatically for XML publishing The XML functions that Informix provides run in a virtual processor named idsxmlvp As of this release the idsxmlvp virtual processor is created automatically when you use an XML function The idsxmlvp virtual processor class on New editions and product names IBM Informix Dynamic Server editions were withdrawn and new Informix editions are available Some products were also renamed The publications in the Informix library pertain to the following products IBM Informix database server formerly known as IBM Informix Dynamic Server IDS IBM OpenAdmin Tool OAT for Informix formerly known as OpenAdmin Tool for Informix Dynamic Server IDS BM Informix SOL Warehousing Tool formerly known as Informix Warehouse Feature For a Windows host computer supporting more than one installation of the same version you must install the product in a different directory each time Example code conventions vi Examples of SOL code occur throughout this publication Except as noted the code is not specific to any single IBM Informix application development tool If only SQL statements are listed in the example they are not delimited by semicolons For instance you might see the code in the following example CONNECT TO stores demo DELETE FROM customer WHERE customer num 121 IBM Informix XML User s Guide COMMIT WORK DISCONNECT CURRENT To
28. of the IBM Customer Agreement IBM International Program License Agreement or any equivalent agreement between us Any performance data contained herein was determined in a controlled environment Therefore the results obtained in other operating environments may vary significantly Some measurements may have been made on development level systems and there is no guarantee that these measurements will be the same on generally available systems Furthermore some measurements may have been estimated through extrapolation Actual results may vary Users of this document should verify the applicable data for their specific environment Information concerning non IBM products was obtained from the suppliers of those products their published announcements or other publicly available sources IBM has not tested those products and cannot confirm the accuracy of performance compatibility or any other claims related to non IBM products Questions on the capabilities of non IBM products should be addressed to the suppliers of those products All statements regarding IBM s future direction or intent are subject to change or withdrawal without notice and represent goals and objectives only All IBM prices shown are IBM s suggested retail prices are current and are subject to change without notice Dealer prices may vary This information is for planning purposes only The information herein is subject to change before the products described become avai
29. pression gt lt The extractclob syntax extractclob xml string xpath expression gt lt Parameters xml string The XML string or document to evaluate xpath expression An XPATH expression For extract the string or document size cannot exceed 32739 For larger strings or documents use extractclob Chapter 1 Publishing SQL result sets in XML 1 3 Specify an absolute XPath string with an initial slash Omit the initial slash to indicate a path relative to the root node If no match is found these functions return an empty string Example 1 This example evaluates the XML contained in column col2 of table tab and returns the given name for Jason Ma SELECT extract col2 personnel person 8 id Jason Ma name given FROM tab lt given gt Jason lt given gt Example 2 This example is similar to the first except the entire name is returned SELECT extract col2 personnel person id Jason Ma name FROM tab name fami ly gt Ma lt fami ly lt given gt Jason lt given gt lt name gt Example 3 In this example only the second column contains XML SELECT warehouse name extract warehouse spec Warehouse Docks lvarchar 256 Number of Docks FROM warehouses WHERE warehouse spec IS NOT NULL WAREHOUSE NAME Number of Docks Liverpool England lt Docks gt 2 lt Docks gt Taipei Taiwan lt Docks gt 1 lt Docks gt Buenos Aires Argentina lt Dock
30. s gt 4 lt Docks gt Seattle USA lt Docks gt 3 lt Docks gt The existsnode XML function 1 4 Determines whether the XPath evaluation results in at least one XML element You can also specify a namespace to identify the mapping of prefixes that are specified in the XPath_string to the corresponding namespaces Purpose Determines whether traversal of an XML document using a specified path results in any nodes Returns 1 if one or more nodes are found otherwise returns 0 The existsnode syntax gt p existsnode xml_document xpath_expression namespace 4 Parameters xml document The XML document or fragment to evaluate The document can be of type LVARCHAR or CLOB IBM Informix XML User s Guide xpath expression The XPATH expression to search for XML nodes Specify an absolute XPath string with an initial slash Omit the initial slash to indicate a path relative to the root node If no match is found these functions return an empty string namespace Use this parameter to identify the mapping of one or more prefixes that are specified in xpatli expression to the corresponding namespace Example 1 This example query returns a list of warehouse IDs and names for every warehouse that has an associated dock SELECT warehouse id warehouse name FROM warehouses WHERE existsnode warehouse spec Warehouse Docks 1 The extractvalue and extractvalueclob XML functions
31. statement may not apply to you This information could include technical inaccuracies or typographical errors Changes are periodically made to the information herein these changes will be incorporated in new editions of the publication IBM may make improvements and or changes in the product s and or the program s described in this publication at any time without notice Any references in this information to non IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk Copyright IBM Corp 1996 2010 B 1 B 2 IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you Licensees of this program who wish to have information about it for the purpose of enabling i the exchange of information between independently created programs and other programs including this one and ii the mutual use of the information which has been exchanged should contact IBM Corporation J46A G4 555 Bailey Avenue San Jose CA 95141 1003 USA Such information may be available subject to appropriate terms and conditions including in some cases payment of a fee The licensed program described in this document and all licensed material available for it are provided by IBM under terms
32. ta type The input XML must include every value inside an element not as an attribute For example employee lt givenname gt Roy lt givenname gt lt familyname gt Connor lt familyname gt lt address gt lt address1 gt 123 First Street lt address1 gt lt city gt Denver lt city gt lt state gt C0 lt state gt lt zipcode gt 80111 lt zipcode gt lt address gt lt phone gt 303 555 1212 lt phone gt lt employee gt The XML functions are summarized in the following table Table 1 1 XML publishing functions Action Function Comments Return rows of SQL results as XML elements The genxml and genxmlclob XML functions on page 1 6 Similar to FOR XML RAW in Microsoft SOL Server Return each column value as separate elements The genxmlelem and ecenxmlelemclob XML Similar to FOR XML AUTO ELEMENTS in Microsoft SOL Server Return an XML schema and result in XML format The genxmlschema and ecenxmlschemaclob XML functions on page 1 10 Similar to FOR XML AUTO XMLSCHEMA in Microsoft SOL Server Returns the result set of a query in XML format The genxmlquery and ecenxmlqueryclob XML functions on page 1 10 These functions accept a SOL query as a parameter Returns the result set of a query in XML with the XML header The genxmlqueryhdr and ecenxmlqueryhdrclob XML
33. te and time is brought together in a single environment called a Global Language Support GLS locale This publication assumes that your database uses the default locale This default is en us 8859 1 ISO 8859 1 on UNIX platforms or en us 1252 Microsoft 1252 in Windows environments This locale supports U S English format conventions for displaying and entering date time number and currency values It also supports the ISO 8859 1 on UNIX and Linux or Microsoft 1252 on Windows code set which includes the ASCII code set plus many 8 bit characters such as and fi Copyright IBM Corp 1996 2010 V If you plan to use nondefault characters in your data or in SQL identifiers or if you plan to use other collation rules for sorting character data you need to specify the appropriate nondefault locale For instructions on how to specify a nondefault locale and for additional syntax and other considerations related to GLS locales see the IBM Informix GLS User s Guide What s new in IBM Informix XML User s Guide version 11 70 This publication includes information about new features and changes in existing functionality The following changes and enhancements are relevant to this publication For a comprehensive list of all new features for this release see the IBM Informix Getting Started Guide Table 1 What s new in IBM Informix XML User s Guide for version 11 70 Overview Reference Virtual pr
34. ters in XML functions The extract and extractclob XML fonctions The existsnode XML function i The extractvalue and extractvalueclob XML functions 5 The genxml genxmlclob XML functions Eos The genxmlelem and genxmlelemclob XML functions pe 2 The genxmlqueryhdr and genxmlqueryhdrclob XML functions The genxmlquery and genxmlqueryclob XML functions The genxmlschema and genxmlschemaclob XML functions The idsxmlparse XML function Chapter 2 Transforming documents with XSLT functions The xsltransform function The xsltransformAsClob function The xsltransformAsBlob function Appendix Accessibility Accessibility features for IBM Informix produce Accessibility features Keyboard navigation Related accessibility and accessibility Dotted decimal syntax diagrams Notices Trademarks Index Copyright IBM Corp 1996 2010 Xiii 1 1 1 2 1 2 1 3 1 4 155 1 6 1 7 o 19 1 10 1 10 1 11 2 1 22 22 1 1 1 1 1 1 B 1 X 1 iii iv Informix XML User s Guide Introduction About this publication This publication includes information about using built in functions for XML publishing with IBM Informix You should be familiar with the IBM Informix Guide to SOL Syntax which contains all the syntax descriptions for SOL an
35. the FOR XML AUTO ELEMENTS format The genxmlschema function and other XML publishing functions cannot publish BYTE or TEXT columns into an XML document These functions take the input column as ROW types and then publish them BYTE and TEXT are not allowed in ROW types so cannot be used in these publishing functions ROW types are unsupported in distributed queries These functions use ROW types As a result these publishing functions cannot be used in distributed queries or use a synonym referring to a non local object Before you run XML functions run the following statement to allow multiple lines EXECUTE PROCEDURE ifx allow newline t Run the following statements on the database server before running the examples in this book The CREATE DATABASE statement uses a dbspace named datadbs You must either create a dbspace named datadbs or substitute datadbs with the name of an existing dbspace EXECUTE PROCEDURE ifx allow newline t CREATE DATABASE demo xml IN datadbs with log CREATE TABLE tab col2 lvarchar INSERT INTO tab VALUES personnel person id Jason Ma gt name fami 1y gt Ma lt fami ly lt given gt Jason lt given gt lt name gt lt person gt lt personnel gt The idsxmlvp virtual processor class The XML functions that IBM Informix provides run in a virtual processor class named idsxmlvp 1 2 IBM Informix XML User s Guide The idsxmlvp virtual processor is created automaticall
36. ument with an XSL stylesheet and XSLT parameter The returned document is of type BLOB The xsltransformAsBlob syntax x lstransformAsBlob xmi 51 document gt lt IBM Informix XML User s Guide Parameters xml document The XML document or fragment to transform The document can be of type LVARCHAR or BLOB xsl document The XSL stylesheet document that is applied to the XML document The XSL stylesheet can be of type LVARCHAR or BLOB Chapter 2 Transforming documents with XSLT functions 2 3 2 4 IBM Informix XML User s Guide Appendix Accessibility IBM strives to provide products with usable access for everyone regardless of age or ability Accessibility features for IBM Informix products Accessibility features help a user who has a physical disability such as restricted mobility or limited vision to use information technology products successfully Accessibility features The following list includes the major accessibility features in IBM Informix products These features support Keyboard only operation nterfaces that are commonly used by screen readers The attachment of alternative input and output devices Tip The information center and its related publications are accessibility enabled for the IBM Home Page Reader You can operate all features by using the keyboard instead of the mouse Keyboard navigation This product uses standard Microsoft Windows
37. use this SOL code for a specific product you must apply the syntax rules for that product For example if you are using an SOL API you must use EXEC SOL at the start of each statement and a semicolon or other appropriate delimiter at the end of the statement If you are using DB Access you must delimit multiple statements with semicolons Tip Ellipsis points in a code example indicate that more code would be added in a full application but it is not necessary to show it to describe the concept being discussed For detailed directions on using SQL statements for a particular application development tool or SOL API see the documentation for your product Additional documentation Documentation about this release of IBM Informix products is available in various formats All of the product documentation including release notes machine notes and documentation notes is available from the information center on the web at http publib boulder ibm com infocenter idshelp v117 index jsp Alternatively you can access or install the product documentation from the Quick Start CD that is shipped with the product Compliance with Industry Standards IBM Informix products are compliant with various standards IBM Informix SQL based products are fully compliant with SQL 92 Entry Level published as ANSI X3 135 1992 which is identical to ISO 9075 1992 In addition many features of IBM Informix database servers comply with the SQ
38. y that only that IBM product program or service may be used Any functionally equivalent product program or service that does not infringe any IBM intellectual property right may be used instead However it is the user s responsibility to evaluate and verify the operation of any non IBM product program or service IBM may have patents or pending patent applications covering subject matter described in this document The furnishing of this document does not grant you any license to these patents You can send license inquiries in writing to IBM Director of Licensing IBM Corporation North Castle Drive Armonk NY 10504 1785 U S A For license inquiries regarding double byte DBCS information contact the IBM Intellectual Property Department in your country or send inquiries in writing to Intellectual Property Licensing Legal and Intellectual Property Law IBM Japan Ltd 1623 14 Shimotsuruma Yamato shi Kanagawa 242 8502 Japan The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION AS IS WITHOUT WARRANTY OF ANY KIND EITHER EXPRESS OR IMPLIED INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF NON INFRINGEMENT MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE Some states do not allow disclaimer of express or implied warranties in certain transactions therefore this
39. y the first time you use an XML function If you want to increase the number of idsxmlvp virtual processors use one of the following methods Add the following line to your onconfig file substituting n with the number of virtual processors you want to start and restart the database server VPCLASS idsxmlvp num n As user informix run the following command while the database server is running substituting n with the number of virtual processors you want to start onmode p n idsxmlvp Special characters in XML functions The XML functions of the database server automatically handle special characters When a SQL result set contains special characters the XML function will automatically handle it These special characters are listed in the following table Table 1 2 Special characters handled by XML functions Character Resolved in XML as Less than amp lt Greater than gt amp gt Double quote amp quot Apostrophe amp apos Ampersand amp amp amp The extract and extractclob XML functions Evaluates an XPATH expression on an XML column document or string These functions are identical except that extractclob returns a CLOB instead of LVARCHAR Purpose Returns an XML fragment of the evaluated XML column document or string For details on XPATH see http www w3 org The extract syntax extract xml string xpath ex
Download Pdf Manuals
Related Search