Update several records in a table with a single SQL Server MERGE (from XML)

Question

Is it possible to use SQL Server 2012 MERGE to update more than one records in a table from XML stream?

My table is as follows:

A few times per day I receive the following XML envelope:

<WeatherForecastUpdate>
    <Location Temperature="67" Id="56">
        <Humidity>78%</Humidity>
        <Condition>Rain</Condition>
        <Wind>5mph</Wind>
    </Location>
    <Location Temperature="72" Id="783">
        <Humidity>51%</Humidity>
        <Condition>Clear</Condition>
        <Wind>5mph</Wind>   
    </Location>
</WeatherForecastUpdate>

I need to update the weather data based on LocationID from XML. I know how to MERGE each single message (location), but I wonder if there is a way to update all table records within a single MERGE.

Answer 1

Yes you can! At first convert xml to table, then MERGE:

DECLARE @x xml = N'<WeatherForecastUpdate>
    <Location Temperature="67" Id="56">
        <Humidity>78%</Humidity>
        <Condition>Rain</Condition>
        <Wind>5mph</Wind>
    </Location>
    <Location Temperature="72" Id="783">
        <Humidity>51%</Humidity>
        <Condition>Clear</Condition>
        <Wind>5mph</Wind>   
    </Location>
</WeatherForecastUpdate>'

;WITH cte AS (
    SELECT  t.v.value('@Temperature','int') Temperature,
            t.v.value('@Id','int') Id,
            t.v.value('(./Humidity)[1]','nvarchar(5)') Humidity,
            t.v.value('(./Condition)[1]','nvarchar(10)') Condition,
            t.v.value('(./Wind)[1]','nvarchar(10)') Wind
    FROM @x.nodes('/WeatherForecastUpdate/Location') as t(v)
)

MERGE YourTable as t
USING cte as s
ON t.LocationID = s.ID
WHEN MATCHED THEN
    UPDATE SET  Temp = s.Temperature,
                Humidity = s.Humidity,
                Wind = s.Wind

Another way to use simple UPDATE:

UPDATE t
SET Temp = s.Temperature,
    Humidity = s.Humidity,
    Wind = s.Wind
FROM YourTable t
INNER JOIN cte s
    ON t.LocationID = s.ID