Task overview:
The XML Extender provides two methods for retrieving data:
Table 12. The XML Extender default cast functions
Casting used in select clause | Return type | Description |
---|---|---|
varchar(XMLVARCHAR) | VARCHAR | XML document in VARCHAR |
clob(XMLCLOB) | CLOB | XML document in CLOB |
varchar(XMLFile) | VARCHAR | XML file name in VARCHAR |
EXEC SQL SELECT db2xml.varchar(order) from sales_tab
For example, you might have your XML document stored as XMLFILE and you want to operate on it in memory, you can use the Content() UDF, which can take an XMLFILE data type as input and return a CLOB.
The Content() UDF performs two different retrieval functions, depending on the specified data type. It:
XMLFILE to CLOB: Retrieves data from a file and exports to a CLOB locator.
Content(xmlobj XMLFile)
XML type to external file: Retrieves the XML content that is stored as an XML data type and exports it to an external file.
Content(xmlobj XML type, filename varchar(512))
Where:
In the example below, a small C program segment with embedded SQL illustrates how an XML document is retrieved from a file to memory. This example assumes that the column BOOK is of the XMLFILE type.
EXEC SQL BEGIN DECLARE SECTION; SQL TYPE IS CLOB_LOCATOR xml_buff; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO SALES_DB EXEC SQL DECLARE c1 CURSOR FOR SELECT Content(order) from sales_tab EXEC SQL OPEN c1; do { EXEC SQL FETCH c1 INTO :xml_buff; if (SQLCODE != 0) { break; } else { /* do whatever you need to do with the XML doc in buffer */ } } EXEC SQL CLOSE c1; EXEC SQL CONNECT RESET;