I've been trying to Query an item with its sublist all in one go and one way is to use for xml and subquery. I've been using this strategy with sql server and works fine.

The relationship is Order and OrderItems

for sql server, the query might look like this:

Select O.OrderId, O.ShippingDate,
         SELECT OI.ProductId, OI.Description, OI.Cost 
         FROM OrderItems OI 
         WHERE O.OrderId = OI.OrderId
         FOR XML PATH('OrderItem'), ROOT('OrderItems')
    ) as OrderItems 
    FROM Orders O 

I need only the order items list to be xml, the order details is fine as is.

googling ways to apply xml result in ibm db2, I found in their documentaion on their website: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.xml.doc/doc/c0052638.html

I found some snippet:

    SELECT XMLELEMENT (NAME "saleProducts",
                   XMLAGG (XMLELEMENT (NAME "prod",
                           XMLATTRIBUTES (p.Pid AS "id"),
                           XMLFOREST (p.name as "name",
                                      i.quantity as "numInStock")))) 

which I applied as

SELECT O.OrderId, O.ShippingDate, 
        XMLELEMENT (NAME "OrderItems",
               XMLAGG (XMLELEMENT (NAME "OrderItem",
                       XMLATTRIBUTES (OI.ProductId AS "ProductId"),
                       XMLFOREST (OI.Description as "Description",
                                  OI.Cost as "Cost"))))
    FROM Orders O, OrderItems OI
    WHERE O.OrderId = OI.OrderId 

But on execution, IBM db gives an error message:

OLE DB provider "IBMDA400" for linked server "XXX" returned message "SQL0104: Token "OrderItems" was not valid. Valid tokens: ) ,. Cause . . . . . : A syntax error was detected at token "OrderItems". Token "OrderItems" is not a valid token. A partial list of valid tokens is ) ,.
This list assumes that the statement is correct up to the token.
The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . :
Do one or more of the following and try the request again: Verify the SQL statement in the area of the token "OrderItems". Correct the statement.
The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. If the error token is , correct the SQL statement because it does not end with a valid clause.".


  • 2
    If you're using Db2 for i, you should probably reference documentation for that platform and your exact version, not Db2 for LUW 9.7. – mustaccio Apr 29 '19 at 1:44
  • SQL Reference for Db2 for i 7.3 (other version available) – Charles Apr 29 '19 at 17:10
  • No luck, all versions in their docs are using the same syntax. I gues it's an older version of ibm which does not support XML. – Carl Apr 30 '19 at 8:55
  • What version of DB2 for i are you running? The error suggests it may be a data provider issue rather than database issue. – topshot May 3 '19 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.