I created a store procedure to incoming sales order, i followed example in ion tecnical reference but example it's very simple and it show only how create a multiple BOD separated with dataarea <> but without verbs
SELECT(SELECT MySalesOrder.orderNumber as orderID, MySalesOrder.orderStatus, MySalesOrder.orderDateTime, MySalesOrder.customer, MySalesOrderLine.lineNumber, MySalesOrderLine.item, MySalesOrderLine.quantity, MySalesOrderLine.price FROM Orders as MySalesOrder, OrderLines as MySalesOrderLine WHERE MySalesOrderLine.orderNumber = MySalesOrder.orderNumber and MySalesOrder.lastModification >= @lastTime and MySalesOrder.lastModification < @currentTime FOR XML AUTO, ELEMENTS, type).query( 'for $i in /MySalesOrder return <DataArea>{$i}</DataArea>');
I created new one with same schema of salesorder but the result is incomplete
my query
select (
select
'infor' as 'Process/Tenant',
'300' as 'Process/AccountingEntityID',
'S_300' as 'Process/LocationID',
'Add' as 'Process/ActionCriteria/ActionExpression/@actionCode';,
(SELECT
SalesOrder.Customer As 'SalesOrderHeader/CustomerParty/ID',
(SELECT SalesOrderLine.LinesNo as 'LineNumber'
FROM LinesInterfaccia as SalesOrderLine WHERE SalesOrder.OrderNo = SalesOrderLine.OrderNo
FOR XML path('SalesOrderline'), type )
FROM
HeaderInterfaccia as SalesOrder
FOR XML Path('SalesOrder'), TYPE)
FOR XML Path('DataArea'), TYPE)
the resuls is
<DataArea>
<Process>
<Tenant>infor</Tenant>
<AccountingEntityID>300</AccountingEntityID>
<LocationID>S_300</LocationID>
<ActionCriteria>
<ActionExpression actionCode="Add" />
</ActionCriteria>
</Process>
<SalesOrder>
<SalesOrderHeader>
<CustomerParty>
<ID>BP00003184</ID>
</CustomerParty>
</SalesOrderHeader>
<SalesOrderline>
<LineNumber>10</LineNumber>
</SalesOrderline>
<SalesOrderline>
<LineNumber>20</LineNumber>
</SalesOrderline>
</SalesOrder>
<SalesOrder>
<SalesOrderHeader>
<CustomerParty>
<ID>BP00003184</ID>
</CustomerParty>
</SalesOrderHeader>
<SalesOrderline>
<LineNumber>10</LineNumber>
</SalesOrderline>
</SalesOrder>
</DataArea>
the problem is after complete first order don close tag dataarea but only at the end.
it works fine only if have one order
Any suggests to solve this query
A
