logo
down
shadow

How to use XML input for Update / Set statement in SQL Server?


How to use XML input for Update / Set statement in SQL Server?

Content Index :

How to use XML input for Update / Set statement in SQL Server?
Tag : sql , By : Hugo Hernan Buitrago
Date : November 18 2020, 03:49 PM

hope this fix your issue When I need to update a table through a stored procedure I use something like the following for this which usually works for me (using SQL Server 2012):
UPDATE A
SET    title = b.title,
       summary = b.summary,
       post = b.post,
       departmentID = b.departmentID
FROM   LogTable A
       JOIN (SELECT title=[Xml_Tab].[Cols].value('(title)[1]', 'nvarchar(100)'),
                    summary=[Xml_Tab].[Cols].value('(summary)[1]', 'nvarchar(500)'),
                    post=[Xml_Tab].[Cols].value('(post)[1]', 'nvarchar(max)'),
                    departmentID=[Xml_Tab].[Cols].value('(departmentID)[1]', 'int'),
                    PostID=[Xml_Tab].[Cols].value('(postID)[1]', 'int')
             FROM   @xmlMain.nodes('/root') AS [Xml_Tab]([Cols]) ) B
         ON a.postID = b.postID 

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

How to use Update statement with XML input in SQL Server?


Tag : sql , By : user121350
Date : March 29 2020, 07:55 AM
To fix the issue you can do we can use MERGE, select XML values into a temporary table and use MERGE
BEGIN

    SET NOCOUNT ON; 

    BEGIN TRANSACTION;

        BEGIN    

            IF object_id('tempdb.dbo.#tmpList') IS NOT null DROP TABLE #tmpList
            create table #tmpList(
                id                      varchar(255),
                departmentID            int,
                role                    int
            )

            insert into #tmpList
            SELECT          ParamValues.ntid.value('.', 'varchar(255)'),
                            @departmentID,
                            @role
            FROM            @xmlUsers.nodes('/users/ntid') AS ParamValues(ntid)

            MERGE  RC_PermissionsUsers as PU
            USING ( select id, departmentID, role from #tmpList) T
                    ON PU.ntid = T.id
            WHEN MATCHED THEN 
                UPDATE SET departmentId = T.departmentID,
                           role = T.role
            WHEN NOT MATCHED THEN
                INSERT ( ntid, departmentID, role)  
                   VALUES ( T.id, T.departmentID, T.role)           


        END

    COMMIT TRANSACTION;  
END

Update multiple data in SQL Server with ONE update statement


Tag : sql-server , By : Matt Logan
Date : March 29 2020, 07:55 AM
should help you out I save multiple records into my SQL Server table in one go, 5 records of data to be exact. , You can use this.
UPDATE T SET 
    T.AM = SRC.AM,
    T.PM = SRC.PM,
    T.NotAvailable = SRC.NotAvailable
FROM [dbo].[tb_UserAvailability] T
INNER JOIN 
    (VALUES
        (@Monday, @MondayAM, @MondayPM, @MondayNA),
        (@Tuesday, @TuesdayAM, @TuesdayPM, @TuesdayNA),
        (@Wednesday, @WednesdayAM, @WednesdayPM, @WednesdayNA),
        (@Thursday, @ThursdayAM, @ThursdayPM, @ThursdayNA),
        (@Friday, @FridayAM, @FridayPM, @FridayNA) ) AS SRC (Day, AM, PM, NotAvailable) ON T.Day = SRC.Day
Where T.UserID = @UserID

Using two updates where second update statement is using column from first update statement as input


Tag : sql , By : Myatus
Date : March 29 2020, 07:55 AM
Hope this helps In DB2 and the SQL standard you use a feature called FINAL_TABLE to do this. In Oracle you use a statement called "RETURNING".
cf - https://blog.jooq.org/tag/final-table/

SQL Server - How To Properly Update Table With User Defined Function In Update Statement?


Tag : sql , By : hellboy32
Date : March 29 2020, 07:55 AM

How to disable a trigger before an update statement and enable it again after the update statement in SQL Server?


Tag : sql , By : Mariamario
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • SQL grouping/counting on a string split function
  • ORDER BY and TOP statements in SQL works different on listing down the records and why?
  • SQL Server Computed Column: how to set it to NOT NULL, without persistence
  • Is nested select statement better than where statement?
  • Postgresql sequential scan slow performance on 500 million rows
  • Aggregate first order data to all orders - Microsoft SQL Server
  • Sorting comma delimited datasets in row
  • Question about the correct data structure
  • Showing two temp tables data in a single table
  • How can I find out the query execution time in Oracle SQL using Toad?
  • convert quarter number to month name in sql
  • Find max value from column that has a json object with key-value pairs
  • case statement based on 3 columns in 1 table
  • How to write a SQL query to select batsman wiht highest score than the previous year
  • Find Substring in SQL
  • Using VALUE to make temporary table
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com