Parse XML mit T-SQL und XQUERY – search nach bestimmten Werten

Ich habe einige properties einer Anwendung, die mir in XML-Form übergeben wird. Ich muss die Eigenschaft nach Namen parsing und den Wert der entsprechenden Spalte in meiner database zuordnen.

Ich analysiere es derzeit in einer SSIS-Skriptkomponente, aber es dauert bis zu langer time. Ich hatte gehofft, dass es eine einfache Lösung für diese mit XQUERY geben würde, aber ich kann nicht finden, was ich suche.

Hier ist ein Beispiel für die xml Ich empfange:

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

Also, wenn ich das erste Eigentums-Element betrachte, würde ich den Wert DEFAULT meiner Spalte DISMISS_SETTING in meiner database zuordnen. Auch ist es wichtig zu beachten, die Reihenfolge und Kombinationen der Werte können in keiner bestimmten Reihenfolge kommen.

Verwenden Sie die Methode value () (xml datatyp) , um einen Wert aus Ihrem XML zu extrahieren. Überprüfen Sie den Namen, den Sie in einem Prädikat im XQuery-Ausdruck wünschen.

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

Sie können dies tun, indem Sie den Namen und den Wert aus dem xml extrahieren und um den Namen schwenken. Allerdings können Sie dies nicht mit beliebigen Namen bei der Abfrage time zu tun. Wenn du das brauchst, dann bist du wahrscheinlich besser dran, den PIVOT zu entfernen und nur die Namen und Wertspalten zu verwenden, die von der inneren Abfrage bereitgestellt werden.

 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 

Wenn du nach einer TSQL-Lösung suchst und wenn ich deine Ergebnistabelle so aussehen soll wie auf dem folgenden Schema:

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

Du solltest einen Satz von Skripten verwenden, die ich in einem Moment beschreibe. Anfänglich müssen Sie eine dynamische gespeicherte Prozedur erstellen, die dynamische Abfragen erstellt – es gibt Ihnen die Möglichkeit, Ihre data in Tabellen unter solchen Spalten einzufügen, welche Namen bis zur Laufzeit nicht bekannt sind (die time Ihres 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 

Als nächstes verwenden Sie diese XQuery / SQL-statement zu extrahieren (aus XML) Informationen, die Sie suchen:

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

Danach werden extrahierte datapaare [Name, Wert] in der Tabellenvariable @T gespeichert. Schließlich Iteration über solche temporären Metadaten und legen Sie Werte in den entsprechenden Spaltennamen Ihrer Haupttabelle ein:

 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 

Präsentierte Lösung ermöglicht es Ihnen, veränderliche Anzahl von Knoten in der XML, ohne eine bestimmte Bestellung zur Verfügung gestellt.

Beachten Sie, dass die Logik, die für die dataextraktion aus XML und die Einfügung in die Haupttabelle zuständig ist, in eine zusätzliche gespeicherte Prozedur, z. B. mysp_xml_update (@data xml) und dann in der folgenden sauberen Weise ausgeführt werden kann: exec mysp_xml_update N'<properties>....</properties> .

Trotzdem probier den Code selbst mit SQL Fiddle .

AKTUALISIEREN:

Wie im Kommentar angefordert – sollte ein großes Update ausgeführt werden, anstatt sequentiell Spalte nach Spalte zu aktualisieren. Zu diesem Zweck sollte mysp_update zB in folgender Weise modifiziert werden:

 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 

Ich beschloss, meine bestehende Antwort zu aktualisieren (nur für Neugierde von Alternativen und imageungszwecken). Ich schob einen anderen, um beide Versionen zu behalten und die Möglichkeit der Verfolgung der Teile, die verbessert wurden, zu bewahren:

  1. Aktualisierung des ersten Ansatzes – sequentielles Einfügen / Aktualisieren für jede Spalte (Verwendung des Cursors, Entfernen der redundanten temporären Tabelle):

     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 
  2. Aktualisierung des zweiten Ansatzes – 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 
  3. Und endgültig, ganz neu, dritter Ansatz (dynamische Bulk-Fusion mit Pivot, keine Loops, keine Cursor):

     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 

Der Gebrauch folgt:

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