As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.
The XML adapter is not FOC and so you must either have it already or buy it.
However should you have it / buy it,
The supported method for doing this is documented in the WebFocus Adapter documentation
The specific bits that you need are (this actually is using 2 NTEXT or clob columns and not a TEXT column, so the automatic generation of the XML segments may not work on auto generation of the master and you may have to create the master using the manual method. If I get the time I'll test it and let you know)
Procedure: How to Access XML Data From an RDBMS Using Web Console or DMC Tools Using the Web Console or the DMC Create Synonym facility, generate a synonym for an RDBMS data source that contains a column of XML data. Regardless of the data type used to contain the XML data in the native data source, it will be mapped as a TX column in the synonym's Master File. (For example, for DB2 Version 9, the XML data type is mapped to TX; for many RDBMSs the CLOB data type is mapped to TX.)
Open the generated Master File in the Synonym Editor. The Master File appears in the right pane in Text View. For example, the Master File for a Progress data source might look as follows:
FILE=XMLPRO1 ,SUFFIX=SQLPRO ,$ SEGNAME=XMLPRO1 ,SEGTYPE=S0 ,$ FIELD=FLD1 ,FLD1 ,A2 ,A2 ,MISSING=ON ,$ FIELD=FLD2 ,FLD2 ,TX50 ,TX ,MISSING=ON ,$ FIELD=FLD3 ,FLD3 ,TX50 ,TX ,MISSING=ON ,$ Notice that this example has two TX columns, each of which contains different data and requires a separate segment declaration in the Master File.
In the navigation pane, click a column described as TX. The pop-up menu contains two options for mapping XML. (These options appear only for TX columns.)
Choose either:
Map Internal XML. This option reads the XML data directly and creates the structure. (No schema is needed to map the xml.) The resulting Master File contains the definition of the XML data, represented as a new segment called SEGSUF=XML, which appears in the Text View pane following the original RDBMS segment. The Master File might now look as follows:
FILE=XMLPRO1 ,SUFFIX=SQLPRO ,$ SEGNAME=XMLPRO1 ,SEGTYPE=S0 ,$ FIELD=FLD1 ,FLD1 ,A2 ,A2 ,MISSING=ON ,$ FIELD=FLD2 ,FLD2 ,TX50 ,TX ,MISSING=ON ,$ FIELD=FLD3 ,FLD33,TX50 ,TX ,MISSING=ON ,$ SEGMENT=ORDER, SEGTYPE=S0,POSITION=FLD2,PARENT=XMLPRO1,SEGSUF=XML, $ FIELDNAME=ORDER, ALIAS='Order', USAGE=A1, ACTUAL=A1, $ FIELDNAME=KEY, ALIAS='key', USAGE=A10, ACTUAL=A10, $ FIELDNAME=CUSTOMER, ALIAS='Customer', USAGE=A10, ACTUAL=A10, $ FIELDNAME=STATUS, ALIAS='Status', USAGE=A10, ACTUAL=A10, $ FIELDNAME=TOTALPRICE, ALIAS='TotalPrice', USAGE=A10, ACTUAL=A10, $ FIELDNAME=DATE, ALIAS='Date', USAGE=A10, ACTUAL=A10, $ FIELDNAME=PRIORITY, ALIAS='Priority', USAGE=A10, ACTUAL=A10, $ FIELDNAME=CLERK, ALIAS='Clerk', USAGE=A10, ACTUAL=A10, $ FIELDNAME=SHIPPRIORITY, ALIAS='ShipPriority', USAGE=A10, ACTUAL=A10, $ FIELDNAME=COMMENT, ALIAS='Comment', USAGE=A10, ACTUAL=A10, $ SEGMENT=PORDER, SEGTYPE=S0,POSITION=FLD3,PARENT=XMLPRO1,SEGSUF=XML $ FIELDNAME=PORDER, ALIAS='Order', USAGE=A1, ACTUAL=A1, $ FIELDNAME=KEY, ALIAS='key', USAGE=A10, ACTUAL=A10, $ FIELDNAME=CUSTOMER, ALIAS='Customer', USAGE=A10, ACTUAL=A10, $ FIELDNAME=ADDRESS, ALIAS='Address', USAGE=A10, ACTUAL=A10, $ FIELDNAME=STATE, ALIAS='State', USAGE=A10, ACTUAL=A10, $ Map External XML. This option opens the XML Definition Document pane, where you are prompted for the directory in which the XML data resides, as well as the schema (.dtd or .xsd) that describes the data. When you click the Add Mapping button, the SEGSUF=XML segment is generated within the existing RDBMS Master File, using the structure dictated by the associated .dtd or .xsd.
Tip: If you have a TX column that contains multiple XML formats (for example, a name field and an address field), you can choose Map External XML multiple times to create a separate SEGSUF=XML segment for each format.
From the Synonym Editor's File menu, save the updated Master File.
Procedure: How to Access XML Data From an RDBMS Manually Suppose that you have a table in an RDBMS with one or more columns storing XML data. In order to report from the XML data, following these steps:
Create the Master File for the relational data source using the format for that DBMS.
Create a Master File for the XML document in the column of the RDBMS table. If there are two XML documents with different formats, you must create a Master File for each one.
Manually combine the Master Files. On each root segment for the XML Master File, add three fields: position, parent and segsuf. The POSITION keyword identifies the field containing the XML document. The PARENT field describes the original data source. The field SEGSUF defines the root segment of an XML document representing sub-tree. The total length of all fields in the Master File must not exceed the FOCUS limitation of 32k. If it does, the query will fail.
OK I've tested this in 7.6.6 and it works almost as documented.
Most importantly there was no requirement to install the XML adapter
The Issue I found was for the automatic generation of the master is that all the column lengths were wrong. After manual adjustment everything Worked fine.
Now we hit the real issue. You are 7.1.4. So naturally you cannot use what I posted above.
I suspect for 7.1.4 you will require the XML adapter as according to the documentation you must generate the XML part of the description against the DTD describing the XML.
However I would try the manual method described above to generate the required multi-segment master as the one generated automatically in 7.6.6 bears only passing similarity to the documentation as it contains extra attributes that the 7.1.4 adapter would not understand.
Below is the 7.1.3 adapter documentation
Accessing XML Documents From Relational DBMS CLOB Fields Accessing XML Documents From Relational DBMS CLOB Fields XML documents might be stored in any fields or columns in any data source. Reporting from such documents is supported by defining their structure as sub-trees attached to a parent segment which describes the original data. The synonym creation process must be run against the data in the DBMS and against the DTD describing the XML document. The two Master Files must then be manually combined to make the DTD Master File a child of the Master File created against the DBMS. A FILEDEF is not needed in this instance.
Focal Point Forums 