-- Declare and populate XML variable
DECLARE @inValues XML
SET @inValues = 
'<object>
<property propID="14" propType="4" propValue="Blah blah text" />
<property propID="217" propType="2" propValue="Some other text" />
<property propID="111" propType="6" propValue="Some other text" />
</object>'

-- Create temp table (drop first if exists)
IF OBJECT_ID('tempdb..#SCENE_PROPERTY_LINK') IS NOT NULL
	DROP TABLE #SCENE_PROPERTY_LINK
CREATE TABLE #SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NVARCHAR(MAX))

-- Populate temp table
INSERT INTO #SCENE_PROPERTY_LINK 
SELECT 1, 1, 14, 'Some old text'
INSERT INTO #SCENE_PROPERTY_LINK
SELECT 2, 2, 217, 'Some other old text'
INSERT INTO #SCENE_PROPERTY_LINK
SELECT 3, 3, 111, 'Yet more old text'

-- Return current values
SELECT * FROM #SCENE_PROPERTY_LINK

-- Perform update based on incoming XML
---- Note that ID 111 is not updated due to propType = 6
UPDATE	SPL
SET	SPL.PROPERTY_VALUE = I.ID.value('@propValue', 'VARCHAR(MAX)')
FROM	@inValues.nodes('/object/property') AS I(ID)
JOIN	#SCENE_PROPERTY_LINK AS SPL
ON	SPL.PROPERTY_ID = I.ID.value('@propID', 'INT')
WHERE	I.ID.value('@propType', 'INT') <> 6

-- Return new values
SELECT * FROM #SCENE_PROPERTY_LINK

quote:

---

Originally posted by karthik_padbanaban

check this out.,

http://www.eggheadcafe.com/articles/xmlserializer_bulkload.asp

---