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")))) FROM PRODUCT p, INVENTORY iWHERE p.Pid = i.Pid 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.".
Help
