Hello, everyone, I am doing the Oracle SQL statement, but I want to return a variable, and the output value is missing.How do I output this v_xml document?
Hi dwx,
You were close Another user recommended reading the technology connectors guide - definitely a useful resource, in this case. The "statement" field in the DB Connector expects a stored procedure request with a second assumption that the data being retrieved is already prepared as an XML result set.
This is super easy to do and there's some more example code in the documentation but in short, an example would look like the following (SQL server example):
Statement: EXEC sp_retrieveOrders
EXEC sp_retrieveOrders
On your database server, it'd be expected that there's stored procedure code would look something like:
SELECT Orders.orderNumber, Orders.orderStatus, Orders.orderDateTime, Orders.customer, OrderLines.lineNumber, OrderLines.item, OrderLines.quantity, OrderLines.price FROM Orders, OrderLines WHERE OrderLines.orderNumber = Orders.orderNumber FOR XML AUTO, ELEMENTS, root('DataArea');
SELECT
Orders.orderNumber,
Orders.orderStatus,
Orders.orderDateTime,
Orders.customer,
OrderLines.lineNumber,
OrderLines.item,
OrderLines.quantity,
OrderLines.price
FROM Orders, OrderLines
WHERE OrderLines.orderNumber = Orders.orderNumber
FOR XML AUTO, ELEMENTS, root('DataArea');
There's also sample code in the docs for Oracle. Good luck!
