XML parsing using T-SQL and XQUERY - Finding specific values

Question

I have some properties of the XML application passed to me. I need to parse a property by name and assign a value to the corresponding column in my database.

I am currently parsing it into an SSIS script component, but it is suitable for completion. I was hoping this would be an easy solution for this using XQUERY, but I cannot find what I am looking for.

Here is the xml example that I get:

<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> 

So, if I were looking for the first element of a property, I would assign a DEFAULT value to my DISMISS_SETTING column in my database. In addition, it is important to note that the order and combinations of values ​​can occur in a specific order.

Answer 1

Use value () Method (xml Data Type) to extract the value from your XML. Check the name you want in the predicate in the XQuery expression.

 select @XML.value('(/properties/property[name = "DISMISS_SETTING"]/value/text())[1]', 'nvarchar(100)') as DISMISS_SETTING, @XML.value('(/properties/property[name = "SHOW_SETTING"]/value/text())[1]', 'nvarchar(100)') as SHOW_SETTING, @XML.value('(/properties/property[name = "DEFAULT_SETTING"]/value/text())[1]', 'nvarchar(100)') as DEFAULT_SETTING 

SQL Fiddle

Answer 2

If you are looking for a TSQL solution, and if the result table should look like the diagram below:

 | DISMISS_SETTING | SHOW_SETTING | DEFAULT_SETTING | |-----------------|--------------|-----------------| | DEFAULT | DEFAULT | DEFAULT | 

you should use a set of scripts that I will describe in a minute. Initially, you need to create a dynamic stored procedure that builds dynamic queries - it gives you the opportunity to insert your data into the table under columns whose names are unknown until runtime (XML parsing time):

 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) the information you are 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, the extracted data pairs [name, value] are stored in the table variable @T . Finally, repeat this temporary metadata and paste the values ​​into the 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 

The presented solution allows you to have a variable number of nodes in XML, provided without any specific order.

Note that the logic responsible for retrieving data from XML and pasting it into the main table can be wrapped in an additional stored procedure, for example. mysp_xml_update (@data xml) and then executed as follows: exec mysp_xml_update N'<properties>....</properties> .

However, try the code itself using SQL Fiddle .

UPDATE:

As stated in the comment, one big update should be done instead of sequentially updating the column by column. To do this, mysp_update should be changed, for example. in the 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 

Answer 3

You can do this by extracting the name and value from xml and rotating it. However, you cannot do this with arbitrary names found at query time. If you need it, you are probably better off removing PIVOT and just use the name and value columns provided by the internal query.

 DECLARE @xml xml SET @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>' SELECT [DISMISS_SETTING], [SHOW_SETTING], [DEFAULT_SETTING] FROM ( SELECT properties.property.value(N'./name[1]', N'nvarchar(MAX)') AS propertyName , properties.property.value(N'./value[1]', N'nvarchar(MAX)') AS propertyValue FROM @xml.nodes(N'/properties/property') AS properties(property) ) AS properties PIVOT (MIN(propertyValue) FOR propertyName IN ([DISMISS_SETTING], [SHOW_SETTING], [DEFAULT_SETTING])) AS settings 

Answer 4

I decided to update my existing answer (just for the sake of curiosity of alternatives and educational goals). I clicked another to save both versions and keep track of the improved parts:

• Updating the first approach - sequential insert / update for each column (using the cursor, deleting the redundant temporary table):

   create procedure mysp_update (@table_name nvarchar(50), @column_name nvarchar(50), @column_value nvarchar(50)) as begin set nocount on; declare @rows_count int declare @query nvarchar(500) declare @parm_definition nvarchar(100) = N'@column_value nvarchar(50)' -- Update the row if it exists set @query = N'update ' + quotename(@table_name) + N'set ' + quotename(@column_name) + N' = @column_value' exec sp_executesql @query, @parm_definition, @column_value = @column_value -- Insert the row if the update statement failed if (@@rowcount = 0) begin set @query = N'insert into ' + quotename(@table_name) + N'(' + quotename(@column_name) + N') values (@column_value)' exec sp_executesql @query, @parm_definition, @column_value = @column_value end end go create procedure mysp_xml_update (@table_name nvarchar(50), @data xml) as begin set nocount on; declare @name nvarchar(50), @value nvarchar(50) -- Declare optimized cursor (fast_forward specifies forward_only, read_only cursor with performance optimizations enabled) declare mycursor cursor fast_forward for 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) open mycursor fetch next from mycursor into @name, @value while @@fetch_status = 0 begin -- Execute SP here (btw: SP cannot be executed from select statement) exec mysp_update @table_name, @name, @value -- Get the next row fetch next from mycursor into @name, @value end close mycursor; deallocate mycursor; end go 

• Update of the second approach - bulk insert / update:

   create procedure mysp_xml_update (@table_name nvarchar(50), @data xml) as begin set nocount on; declare @name nvarchar(50), @value nvarchar(50) -- Declare optimized cursor (fast_forward specifies forward_only, read_only cursor with performance optimizations enabled) declare mycursor cursor fast_forward for 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) declare @insert_statement nvarchar(max) = N'insert into ' + quotename(@table_name) + N' ($columns$) values (''$values$)' declare @update_statement nvarchar(max) = N'update ' + quotename(@table_name) + N' set $column$=''$value$' open mycursor fetch next from mycursor into @name, @value while @@fetch_status = 0 begin set @insert_statement = replace(@insert_statement, '$columns$', quotename(@name) + ',$columns$') set @insert_statement = replace(@insert_statement, '$values$', @value + ''',''$values$') set @update_statement = replace(@update_statement, '$column$', quotename(@name)) set @update_statement = replace(@update_statement, '$value$', @value + ''',$column$=''$value$') fetch next from mycursor into @name, @value end close mycursor; deallocate mycursor; set @insert_statement = replace(@insert_statement, ',$columns$', '') set @insert_statement = replace(@insert_statement, ',''$values$', '') set @update_statement = replace(@update_statement, ',$column$=''$value$', '') -- Update the row if it exists exec sp_executesql @update_statement -- Insert the row if the update statement failed if (@@rowcount = 0) begin exec sp_executesql @insert_statement end end go 

• And the final, completely new, third approach (dynamic joint merging with rotary, without loops, without cursors):

   create procedure mysp_xml_update (@table_name nvarchar(50), @data xml) as begin set nocount on; declare @columns nvarchar(max), @scolumns nvarchar(max), @kvp nvarchar(max)='', @query nvarchar(max) select @columns = coalesce(@columns + ',', '') + quotename(x.value(N'(name)[1]', N'nvarchar(50)')), @scolumns = coalesce(@scolumns + ',', '') + 's.' + quotename(x.value(N'(name)[1]', N'nvarchar(50)')), @kvp = @kvp + quotename(x.value(N'(name)[1]', N'nvarchar(50)')) + '=s.' + quotename(x.value(N'(name)[1]', N'nvarchar(50)')) + ',' from @data.nodes(N'/properties/property') as xtbl(x) select @kvp = left(@kvp, len(@kvp)-1) set @query = ' merge ' + quotename(@table_name) + ' t using ( select ' + @columns + ' from ( select props.x.value(N''./name[1]'', N''nvarchar(50)'') as name, props.x.value(N''./value[1]'', N''nvarchar(50)'') as value from @data.nodes(N''/properties/property'') as props(x) ) properties pivot ( min(value) for name in (' + @columns + ') ) settings ) s (' + @columns + ') on (1=1) when matched then update set ' + @kvp + ' when not matched then insert (' + @columns + ') values (' + @scolumns + ');' exec sp_executesql @query, N'@data xml', @data = @data end go 

Use the following:

 exec mysp_xml_update N'mytable', N'<properties> <property> <name>DEFAULT_SETTING</name> <value>NEW DEFAULT 3</value> </property> <property> <name>SHOW_SETTING</name> <value>NEW DEFAULT 2</value> </property> </properties>'