trying to extract data from xml and update 3 tables
ALTER PROCEDURE [dbo].[usp_UpdateOrderDetail]
@Request XML = '<Request/>'--,
--@Response XML = '<Response/>' OUTPUT
As
BEGIN
DECLARE @OrderID BIGINT
DECLARE @AddressID BIGINT
SET @OrderID = @Request.value('(Order/OrderID)[1]', 'BIGINT')
IF @OrderID > 0
BEGIN
-- this is where I am confused
UPDATE Orders O join Customers C on O.OrderID=C.OrderID SET
C.ContactName = T.C.value('ContactName[1]', 'varchar(50)'),
Phone = T.C.value('Phone[1]', 'varchar(50)'),
OrderDate = T.C.value('OrderDate[1]', 'varchar(50)')
FROM @Request.nodes('//Orders') as T(C)
WHERE OrderID = T.C.value('OrderID[1]', 'BIGINT')
END
ELSE
BEGIN
INSERT INTO Orders
(
ContactName
, Phone
, OrderDate
)
SELECT
T.C.value('ContactName[1]', 'varchar(50)')
, T.C.value('Phone[1]', 'varchar(50)')
, T.C.value('OrderDate[1]', 'varchar(50)')
FROM
@Request.nodes('//Order') AS T (C)
END
DECLARE @Counter INT
SET @Counter = 1
-- possibly more code here
END
The portion in the first comment throws an error saying incorrect syntax...!
I'll be receiving data from almost all the columns of all 3 tables (it's not included here for convenience). How do I join these 3 tables and update appropriate columns in each table?