Hello everyone, For my BizTalk application I need to receive "STRUCTURED" XML from SQL Server so I decided to use FOR XML EXPLICIT.
I have a pretty simple scenario. I have 2 tables in SQL. One is a table for invoices, the other one a table for items. One invoice can have many items. I use
a stored procedure to retrieve invoices. Right now the returned XML only retrieves the items for the last invoice. So if the SP returns 3 invoices, the first 2 will only have the receipt number and supplierIdCompany and the last record will be complete with
the items (as expected).
My SP is as follows
ALTER PROCEDURE [dbo].[RetrieveDeliveryreceipts]
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT 1 AS TAG, NULL AS PARENT,
tblDeliveryReceipts.dlrReceiptNumber AS [tblDeliveryReceipts!1!ReceiptNumber!ELEMENT],
tblDeliveryReceipts.dlrSupplierIdCompany AS [tblDeliveryReceipts!1!SupplierIdCompany!ELEMENT],
NULL AS [tblDeliveryReceiptLines!2!ItemDescription!ELEMENT],
NULL AS [tblDeliveryReceiptLines!2!Quantity!ELEMENT]
WHERE tblDeliveryReceipts.dlrReceiptNumber = 'B 00000010' OR tblDeliveryReceipts.dlrReceiptNumber = 'B 00000011' OR tblDeliveryReceipts.dlrReceiptNumber = 'B 00000012'
SELECT 2 AS TAG, 1 AS PARENT,
View Complete Post