0

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?

Table diagram

billinkc
  • 59,250
  • 9
  • 102
  • 159
who-aditya-nawandar
  • 1,334
  • 9
  • 39
  • 89
  • hi link paha ekda: http://stackoverflow.com/questions/11890614/update-multiple-attributes-on-sql-server-xml-data-field-at-once aajun tya nantar http://www.codeproject.com/Articles/261994/Transferring-Data-from-a-Single-XML-Stream-into-Mu try karun paha – Freelancer May 07 '13 at 12:24
  • 1
    It's not possible. Use one `update` per table. – Alexey A. May 07 '13 at 14:09

0 Answers0