XmlElement how it works....

[paolinosal]

Hi all,

I create a function like this (I try to recreate my need starting from sample db2 schema):

Create or Replace Function testxml1 (Dept char(3) )
    Returns Clob(1000000)
    Language Sql

Begin

Declare Event CLob(1000000) ;

 Select
    XmlSerialize(Xmlagg(xmlRow(workdept as "department", empno  As "id", firstnme As "name",
                   lastname As "surname" option row "event") ) as clob ccsid 1208  Excluding Xmldeclaration )
    into Event
    From sample/employee
    where workdept = dept;
  Return Event;

End
;

Then I want to obtain an xmldocument , but if I make this select the BIF XmlElement translate the xml markup symbols <, > into &lt; &gt;
what I'm doing wrong?

many thanks.

Select XmlSerialize(XmlElement(name "monthly", testxml1('D11'), testxml1('E21') ) as clob ccsid 1208  including Xmldeclaration )
    From Sysibm / Sysdummy1;

[B.Hauser]

XML is stored in an internal format.
With XMLSERIALIZE you convert the internal format into a character representation.
In your Select-Statement you need to convert the character representation back into XML with XMLPARSE

Select XMLSERIALIZE(XMLELEMENT(Name "monthly", Xmlparse(DOCUMENT TESTXML1('D11')),
                                               Xmlparse(DOCUMENT TESTXML1('E21')))
                    As Clob Ccsid 1208 Including XMLDECLARATION)
   From SYSIBM / SYSDUMMY1;

But why to convert into character and back into XML, and why not passing XML directly?

Create Or Replace Function TESTXML2(DEPT Char(3))
   Returns XML
   Language Sql
   Begin
      Declare EVENT XML;
      Select XMLAGG(XMLROW(WORKDEPT As "department",
                           EMPNO As "id",
                           FIRSTNME As "name",
                           LASTNAME As "surname"
                       Option Row "event"))
         Into EVENT
         From SAMPLE / EMPLOYEE
         Where WORKDEPT = DEPT;
      Return EVENT;
   End ;

If it is already XML there is no need to use XMLPARSE in your select statement
Birgitta

[paolinosal]

Hi Birgitta,

I made a lot of test, but I can't find the correct solution.

If I create a function than return an XML and then I try:

Select testxml1('D11')
    From Sysibm / Sysdummy1;

I receive the error sq20398.

the same if I try:

Select XmlSerialize(XmlElement(name "department",  testxml1('D11'), testxml1('E21') ) as clob ccsid 1208   including Xmldeclaration )
    From Sysibm / Sysdummy1;

because the xml returned by Testxml1 have no root element, my idea is to concatenate this piece of xml into an xmldocument.
Thanks

[paolinosal]

I found a solution,

Birgitta, what do you think about?

Create Or Replace Function Testxml1 (Dept Char(3))
    Returns Xml
    Language Sql
    Begin
        Declare Event Xml;
        Select  XmlElement(name "event",
                        XmlAgg(XmlConcat(XmlElement(name "id", Empno),
                              XmlElement(name "name", Firstnme ),
                              XmlElement(name "surname", Lastname )) ))
            Into Event
            From Sample / Employee
            Where Workdept = Dept;
        Return Event;
    End

;

And so I can put it into a clob:

Select Xmlserialize((XmlElement(name "department",  
                                                        testxml1('D11'),
                                                       testxml1('E21') ))
                               as clob (100000) ccsid 1208 INCLUDING XMLDECLARATION)
    From Sysibm / Sysdummy1;