Hello,
I'm using DBMS_XMLGEN on 11gR2.
The PL/SQL Pages and Types Reference 11gR2 (Jul 2013) documentation states the following:
GETXML Functions
This function gets the XML document. The function is overloaded.
Syntax
Gets the XML document by fetching the maximum number of rows specified. It
appends the XML document to the CLOB passed in. Use this version of GETXML
Functions to avoid any extra CLOB copies and to reuse the same CLOB for subsequent
calls. Because of the CLOB reuse, this GETXML Functionscall is potentially more
efficient:
DBMS_XMLGEN.GETXML (
ctx IN ctxHandle,
tmpclob IN OUT NCOPY CLOB,
dtdOrSchema IN number := NONE)
RETURN BOOLEAN;
The Oracle XML DB Developer's Guide 11gR2 (Feb. 2014) documentation states the following:
| Parameters
|
|---|
getXML(ctx IN ctxHandle, clobval IN OUT NCOPY clob, dtdOrSchema IN number:= NONE);
| ctx(IN) - The context handle obtained from calling newContext. clobval(IN/OUT) - the CLOB to which the XML document is to be appended, dtdOrSchema(IN) - whether you should generate the DTD or Schema. This parameter is NOT supported. Use this version of the getXML function, to avoid any extra CLOB copies and if you want to reuse the same CLOB for subsequent calls. This getXML call is more efficient than the next flavor, though this involves that you create the LOB locator. When generating the XML, the number of rows indicated by the setSkipRows call are skipped, then the maximum number of rows as specified by the setMaxRows call (or the entire result if not specified) is fetched and converted to XML. Use the getNumRowsProce |
I'm trying to join 2 XML document segments together. I am using 2 hierarchical queries. One to generate hierarchical header information from one table and another to generate hierarchical detail information from a separate table. My code is generally as follows:
DECLARE
hdrctx DBMS_XMLGEN.ctxHandle;
detailctx DBMS_XMLGEN.ctxHandle;
hdrdoc CLOB;
success BOOLEAN;
hdrqry VARCHAR2(2000) := 'SELECT LEVEL, XMLELEMENT(EVALNAME HTAGNAME, HTAGVAL) FROM HDRTABLE
CONNECT BY HSUBSEQ = PRIOR HGRPSEQ';
--
detailqry VARCHAR2(2000) := 'SELECT LEVEL, XMLELEMENT(EVALNAME DTAGNAME, DTAGVAL) FROM DETAILTABLE
CONNECT BY DSUBSEQ = PRIOR DGRPSEQ';
BEGIN
hdrctx := DBMS_XMLGEN.newContextFromHierarchy(hdrqry);
detailctx := DBMS_XMLGEN.newContextFromHierarchy(detailqry);
DBMS_LOB.createtemporary(hdrdoc,TRUE);
DBMS_XMLGEN.getXML(hdrctx,hdrdoc);
--dbms_output.put_line(hdrdoc);
DBMS_XMLGEN.getXML(detailctx,hdrdoc);
DBMS_XMLGEN.closeContext(hdrctx);
DBMS_XMLGEN.closeContext(detailctx);
dbms_output.put_line(hdrdoc);
DBMS_LOB.FREETEMPORARY(hdrdoc);
END;
I have also tried the following iteration:
BEGIN
hdrctx := DBMS_XMLGEN.newContextFromHierarchy(hdrqry);
detailctx := DBMS_XMLGEN.newContextFromHierarchy(detailqry);
DBMS_LOB.createtemporary(hdrdoc,TRUE);
hdrdoc := DBMS_XMLGEN.getXML(hdrctx);
--dbms_output.put_line(hdrdoc);
DBMS_XMLGEN.getXML(detailctx,hdrdoc);
DBMS_XMLGEN.closeContext(hdrctx);
DBMS_XMLGEN.closeContext(detailctx);
dbms_output.put_line(hdrdoc);
DBMS_LOB.FREETEMPORARY(hdrdoc);
END;
Both documentation sources indicate that the 3 parameter overload version of getXML appends to the CLOB parameter that is passed in yet I am not able to get the detail to append to the header. If I uncomment the first put_line I will get an xml document output containing the header information as expected. However, the second put_line outputs an xml document that contains only the detail information and none of the header information. It is not appending as stated in the documentation, but is replacing instead. Any ideas what's going on here and how I can get the append functionality as specified in the documentation?
An additional but trivial issue is the difference in the documentation. The packages and types documentation indicates that the 3 parameter getXML overload returns a BOOLEAN value. The XML DB Dev Guide does not indicate this. When I use this, the call works as a procedure and not as a function, but I'm not passing in the 3rd parameter, which should default to NONE. I just want to make sure that using 2 explicit parameters and 1 implicit is not ending up with me calling the wrong version.
Thank you in advance for any help you can give me on this,
Paul