If you are looking for a TSQL solution and if I your result table should look like this shown on the schemat below:
| DISMISS_SETTING | SHOW_SETTING | DEFAULT_SETTING |
|-----------------|--------------|-----------------|
| DEFAULT | DEFAULT | DEFAULT |
you should use set of scripts I'll describe in a moment. Initially you need to create dynamic stored procedure which builds dynamic queries - it gives you the possibility to insert your data into table under such columns, which names aren't known until runtime (the time of your XML parsing):
create procedure mysp_update (@table_name nvarchar(50), @column_name nvarchar(50), @column_value nvarchar(50))
as
begin
declare @rows_count int
declare @query nvarchar(500)
declare @parm_definition nvarchar(100)
-- Get rows count in your table using sp_executesql and an output parameter
set @query = N'select @rows_count = count(1) from ' + quotename(@table_name)
exec sp_executesql @query, N'@rows_count INT OUTPUT', @rows_count OUTPUT
-- If no rows - insert the first one, else - update existing
if @rows_count = 0
set @query = N'insert into ' + quotename(@table_name) + N'(' + quotename(@column_name) + N') values (@column_value)'
else
set @query = N'update ' + quotename(@table_name) + N'set ' + quotename(@column_name) + N' = @column_value'
set @parm_definition = N'@column_value nvarchar(50)'
exec sp_executesql @query, @parm_definition, @column_value = @column_value
end
go
Next, use this XQuery / SQL statement to extract (from XML) information you're looking for:
-- Define XML object based on which insert statement will be later created
declare @data xml = N'<properties>
<property>
<name>DISMISS_SETTING</name>
<value>DEFAULT</value>
</property>
<property>
<name>SHOW_SETTING</name>
<value>DEFAULT</value>
</property>
<property>
<name>DEFAULT_SETTING</name>
<value>DEFAULT</value>
</property>
</properties>'
-- Declare temporary container
declare @T table(id int identity, name nvarchar(50), value nvarchar(50))
-- Push the extracted nodes values into it
insert into @T(name, value)
select
x.value(N'(name)[1]', N'nvarchar(50)'),
x.value(N'(value)[1]', N'nvarchar(50)')
from
@data.nodes(N'/properties/property') AS XTbl(x)
After that, extracted pairs of data [name, value] are stored in table variable @T. Finally, iterate over such temporary metadata and insert values in appropriate column names of your main table:
declare @name nvarchar(50), @value nvarchar(50), @current_id int = 1
-- Fetch first row
select @name = name, @value = value
from @T where id = @current_id
while @@rowcount = 1
begin
-- Execute SP here (btw: SP cannot be executed from select statement)
exec mysp_update N'TableName', @name, @value
-- Fetch next row
set @current_id = @current_id + 1
select @name = name, @value = value
from @T where id = @current_id
end
Presented solution allows you to have mutable number of nodes in the XML, provided without any specific order.
Note that the logic responsible for data extraction from XML and insertion to the main table, can be wrapped within additional stored procedure e.g. mysp_xml_update (@data xml) and then executed in following clean way: exec mysp_xml_update N'<properties>....</properties>.
Nevertheless, try the code yourself using SQL Fiddle.
UPDATE:
As requested in the comment - one big update should be executed instead of sequentially updating column by column. For that purpose mysp_update should be modified e.g. in following way:
create type HashTable as table(name nvarchar(50), value nvarchar(50))
go
create procedure mysp_update (@table_name nvarchar(50), @set HashTable readonly)
as
begin
-- Concatenate names and values (to be passed to insert statement below)
declare @columns varchar(max)
select @columns = COALESCE(@columns + ', ', '') + quotename(name) from @set
declare @values varchar(max)
select @values = COALESCE(@values + ', ', '') + quotename(value, '''') from @set
-- Remove previous values
declare @query nvarchar(500)
set @query = N'delete from ' + quotename(@table_name)
-- Insert new values to the table
exec sp_executesql @query
set @query = N'insert into ' + quotename(@table_name) + N'(' + @columns + N') values (' + @values + N')'
exec sp_executesql @query
end
go
