使用T-SQL和XQUERY解析XML - 搜索特定值

I have some properties of an application being passed to me in XML form. I need to parse out the property by name and assign the value to the appropriate column in my database.

我有一些应用程序的属性以XML格式传递给我。我需要按名称解析属性并将值分配给我的数据库中的相应列。

I am currently parsing it out in a SSIS script component but it takes way to long to complete. I was hoping there would be an easy solution for this using XQUERY, but I cannot find what I am looking for.

我目前正在SSIS脚本组件中解析它,但它需要很长时间才能完成。我希望有一个简单的解决方案,使用XQUERY,但我找不到我要找的东西。

Here is an example of the xml I am receiving:

这是我收到的xml的一个例子:

<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 at the first property element I would assign the value DEFAULT to my DISMISS_SETTING column in my database. Also, it's important to note the order and combinations of the values can come across in no specific order.

所以,如果我查看第一个属性元素,我会将值DEFAULT分配给我的数据库中的DISMISS_SETTING列。此外,重要的是要注意,值的顺序和组合可能没有特定的顺序。

4 个解决方案

#1

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

使用value()方法(xml数据类型)从XML中提取值。在XQuery表达式中检查谓词中所需的名称。

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

#2

You can do this by extracting the name and value from the xml and pivoting about the name. However, you cannot do this with arbitrary names found at query time. If you need that, you're probably better off removing the PIVOT and just using the name and value columns provided by the inner query.

您可以通过从xml中提取名称和值并围绕名称进行旋转来完成此操作。但是,您无法使用查询时找到的任意名称执行此操作。如果您需要,最好删除PIVOT并使用内部查询提供的名称和值列。

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

#3

If you are looking for a TSQL solution and if I your result table should look like this shown on the schemat below:

如果您正在寻找TSQL解决方案,并且我的结果表看起来如下所示:

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

你应该使用我将在稍后描述的一组脚本。最初,您需要创建动态查询的动态存储过程 - 它使您可以在这些列下将数据插入到表中,这些列在运行时(XML解析的时间)之前是未知的:

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:

接下来,使用此XQuery / SQL语句从您正在寻找的信息中提取(来自XML):

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

之后,提取的数据对[名称,值]存储在表变量@T中。最后,迭代这些临时元数据并在主表的相应列名中插入值:

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.

提供的解决方案允许您在XML中具有可变数量的节点,无需任何特定顺序。

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

注意,负责从XML提取数据和插入主表的逻辑可以包装在附加的存储过程中,例如, mysp_xml_update(@data xml)然后以下面的方式执行:exec mysp_xml_update N' .... 。

Nevertheless, try the code yourself using SQL Fiddle.

不过,请使用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:

根据评论中的要求 - 应该执行一个大的更新,而不是按列顺序更新。为此目的,应该修改mysp_update,例如以下方式:

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

#4

I decided to refresh my existing answer (just for curiosity of alternatives and educational purposes). I pushed another one to keep both versions and preserve the possibility of tracking the parts which were improved:

我决定更新我现有的答案(只是为了好奇的替代品和教育目的)。我推了另一个以保留两个版本并保留跟踪改进的部件的可能性:

Update of the first approach - sequential insert/update for each column (usage of cursor, removal of 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 final, completely new, third approach (dynamic bulk merge with pivot, no loops, no 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

The usage is 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>'

#1