Write SQL output from IBM i Db2 to IFS in XML format using QSYS2.IFS

I am attempting to write XML output from a Db2 file to the IFS.

Running this SQL statement from System i Navigator SQL gives me the proper output on the navigator window:

select xmlelement (name "root",  
    xmlelement (name "EmployeeList",
      XMLAGG (
        XMLELEMENT (NAME "Employee",
          XMLFOREST (
            trim(id) as "ID",
            trim(firstname) as "FirstName",
            trim(lastname) as "LastName"
        )
      )
    )
  )
)
FROM myfile;

Below line feeds and spacing added for clarity:

<root>
 <EmployeeList>
  <Employee>
   <ID>1</ID>
   <FirstName>ROBERT</FirstName>
   <LastName>JONES</LastName>
  </Employee>
  <Employee>
   <ID>2</ID>
   <FirstName>SMITH</FirstName>
   <LastName>FRED</LastName>
  </Employee>
 </EmployeeList>
</root>

I have no problem writing to the IFS if I use constants like this:

CALL QSYS2.IFS_WRITE(
  PATH_NAME =>'/myFileInXML.xml',
  OVERWRITE => 'REPLACE',
  FILE_CCSID => 1208,
  LINE => ('<root><EmployeeList><Employee><ID>99</ID><FirstName>Joe</FirstName><LastName>Arbuckle</LastName></Employee></EmployeeList></root>'
 )
);

However, if I combine the select with the IFS write like this, I get an error my argument is not valid:

CALL QSYS2.IFS_WRITE(
  PATH_NAME =>'/myFileInXML2.xml',
  OVERWRITE => 'REPLACE',
  FILE_CCSID => 1208,
  LINE => (

select xmlelement (name "root",  
    xmlelement (name "EmployeeList",
      XMLAGG (
        XMLELEMENT (NAME "Employee",
          XMLFOREST (
            trim(id) as "ID",
            trim(firstname) as "FirstName",
            trim(lastname) as "LastName"
        )
      )
    )
  )
)
FROM myfile

 )
);

SQL State: 07006 Vendor Code: -301 Message: [SQL0301] Input variable *N or argument 4 not valid. Cause . . . . . : The value in relative position 4 in the statement is a type that is not compatible with the requested operation. The value is variable *N, entry 4 in a descriptor area, or argument *N in a CALL statement. A name *N indicates that a user's descriptor area was used or that a constant or special register was specified on the CALL statement. Recovery . . . : Do one of the following and try the request again: -- Use a variable that is the correct type. -- Specify an argument in the CALL that is the correct type. -- Change the type specified for parameter 4 in the DECLARE PROCEDURE statement.

Advice would be appreciated on how to create a stream file on IFS from Db2 output in XML format.

Edit - also tried this technique:

create table myfilexml (info xml);

insert into myfilexml
select xmlelement (name "root",  
    xmlelement (name "EmployeeList",
      XMLAGG (
        XMLELEMENT (NAME "Employee",
          XMLFOREST (
            trim(id) as "ID",
            trim(firstname) as "FirstName",
            trim(lastname) as "LastName"
        )
      )
    )
  )
)
FROM myfile;

Problem here it it only works if there is only one record in the file. The error here is:

SQL State: 2200L Vendor Code: -20345 Message: [SQ20345] XML value not a well-formed document. Cause . . . . . : The XML value is not a well-formed document. An XML value that is being stored in a table must be a well-formed XML document with a single root element. Recovery . . . : Change the XML value to be a well-formed document with a single root element. Try the request again.

CodePudding user response：

use the xmlserialize function to convert the XMLELEMENT to a CLOB.

Here it is a query which writes the contents of a source member to a stream file in XML format:

call  qsys2.ifs_write( path_name=>'/home/steve/xml.txt',    
   overwrite=>'REPLACE', file_ccsid=>1208, line=>  (           
select xmlserialize(xmlelement (name "root",                   
    xmlelement (name "srcmbr",                                 
      XMLAGG (                                                 
        XMLELEMENT (NAME "srcline",                            
          XMLFOREST (                                          
            trim(srcseq) as "srcseq",                          
            trim(srcdat) as "srcdat",                          
            trim(srcdta) as "srcdta"                           
        ))))                                                   
)  as clob(50k)) srccode                                       
FROM qrpglesrc    ))

CodePudding user response：

You need to write the xml document into a variable and then write it into the file

Which looks like

CREATE tABLE myfile(ID int, FirstName varchar(20),LastName  varchar(20))

  EXEC SQL select xmlelement (name "root",  
    xmlelement (name "EmployeeList",
      XMLAGG (
        XMLELEMENT (NAME "Employee",
          XMLFOREST (
            trim(id) as "ID",
            trim(firstname) as "FirstName",
            trim(lastname) as "LastName"
        )
      )
    )
  )
)
INTO :xml_doc
FROM myfile;
CALL QSYS2.IFS_WRITE(
  PATH_NAME =>'/myFileInXML.xml',
  OVERWRITE => 'REPLACE',
  FILE_CCSID => 1208,
  LINE => (:xmo__doc
 )
);

db<>fiddle here