I've been trying to query an item with its sublist at one time and one way is to use for xml and subquery. I use this strategy with SQL Server and works well.
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 O O.OrderId = OI.OrderId FOR XML PATH (& # 39; OrderItem & # 39;), ROOT (& # 39; OrderItems & # 39;) ) as order items OF ORDERS O
I only need the list of order items to be xml, the details of the order are correct as is.
googling ways to apply the XML result in ibm db2, i found in their documentaion on their website:
I found an excerpt:
SELECT XMLELEMENT (NAME "saleProducts", XMLAGG (XMLELEMENT (NAME "prod", XMLATTRIBUTES (p.Pid AS "id"), XMLFOREST (filename as "name", i.quantity as "numInStock"))))) FROM PRODUCT p, INVENTORY iWHERE p.Pid = i.Pid
that 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, OI Order Items O O.OrderId = OI.OrderId
But at runtime, IBM db gives an error message:
OLE DB provider "IBMDA400" on linked server "XXX" returned message "SQL0104: Token" OrderItems "invalid.
Cause. . . . . : A syntax error has been detected on the "OrderItems" token. 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 seems to be valid until this point. Recovery. . . :
Do one or more of the following, and retry the request:
Check the SQL statement in the "OrderItems" token area. Correct the statement.
The error can be a missing comma or quotation mark, a misspelled word or a link with the order of the clauses.
If the error token is, correct the SQL statement because it does not end with a valid clause. ".