I have been trying to query an item with its secondary list at one time and one way is to use it for xml and subquery. I have been using this strategy with the SQL server and it works fine.
The relationship is order and order items.
for the sql server, the query might look like this:
Select O.OrderId, O.ShippingDate, ( SELECT OI.ProductId, OI.Description, OI.Cost OF OrderItems OI WHERE O.OrderId = OI.OrderId FOR XML PATH (& # 39; OrderItem & # 39;), ROOT (& # 39; OrderItems & # 39;) ) as order items OF OR Orders
I just need the list of items in the order to be xml, the details of the order are fine as they are.
Google's ways to apply the xml result in ibm db2, I found in your documentation on your website:
I found a piece of code:
SELECT XMLELEMENT (NAME "saleProducts", XMLAGG (XMLELEMENT (NAME "prod", XMLATTRIBUTES (p.Pid AS "id"), XMLFOREST (p.name as "name", i.quantity as "numInStock")))) FROM THE PRODUCT p, INVENTORY iWHERE p.Pid = i.Pid
that I applied as
SELECT O.OrderId, O.ShippingDate, XMLELEMENT (NAME "Order items", XMLAGG (XMLELEMENT (NAME "Order item", XMLATTRIBUTES (OI.ProductId AS "ProductId"), XMLFOREST (OI. Description as "Description", OI. Cost as "Cost")))) Of Orders O, OrderItems OI WHERE O.OrderId = OI.OrderId
But in the run, IBM db gives an error message:
The OLE DB provider "IBMDA400" for the linked server "XXX" returned the message "SQL0104: The token" OrderItems "was not valid. Valid Tokens 🙂 ,.
Why . . . . . : A syntax error was detected in the "OrderItems" token. The "OrderItems" token is not a valid token. A partial list of valid tokens is) ,.
This list assumes that the statement is correct until the token.
The error may be earlier in the declaration, but the syntax of the declaration seems to be valid until this moment. Recovery . . :
Do one or more of the following actions and try the request again:
Verify the SQL statement in the "OrderItems" token area. Correct the statement.
The error could be a missing comma or quote, it could be a misspelled word or it could be related to the order of the clauses.
If the error token is, correct the SQL statement because it does not end with a valid clause. "