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

Question

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

sql-serversql-server-2008t-sqlxpathxquery

7

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

目前我在SSIS脚本组件中解析它，但完成时间太长了。我希望使用XQUERY能够找到一个简单的解决方案，但我找不到我需要的内容。

这是我收到的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>

所以，如果我看第一个属性元素，我会在我的数据库中将DISMISS_SETTING列的值分配为DEFAULT。此外，重要的是要注意值的顺序和组合可能不按特定顺序出现。

- Dave L.

4个回答

1

如果您正在寻找一个TSQL解决方案，并且您的结果表格应该如下所示的模式：

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

你应该使用我接下来描述的一组脚本。首先，你需要创建动态存储过程，该存储过程构建动态查询，使你有可能将数据插入到表中的列中，这些列的名称在运行时（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

接下来，使用这个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)

接着，把数据对 [名称，值] 提取出来，并存储在表变量 @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

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

请注意，从XML中提取数据并插入到主表中的逻辑可以包装在其他存储过程中，例如mysp_xml_update (@data xml)，然后以以下简洁的方式执行：exec mysp_xml_update N'<properties>....</properties>。

尽管如此，请使用SQL Fiddle尝试代码。

更新：

根据评论中的要求 - 应该执行一个大的更新，而不是逐列更新。为此，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

- jwaliszko

Jaroslaw，太棒了。有没有一种不使用XML变量的简单方法来处理这个？例如，我有一堆要处理的XML行。 - Dave L.

@Dave L. 你好，很高兴听到这个消息。不幸的是，我担心我并不真正理解评论中的问题 - 你能详细说明一下你想要实现什么吗？ - jwaliszko

有没有一种方法可以在不迭代数据库中的每个记录的情况下完成，而不是一次只处理一条记录？ - Dave L.

@Dave L. 好的，您的意思是一次将所有数据推送到表中，而不是逐列顺序更新。这是可以做到的-请检查答案的更新。我只选择了更简单的方法-删除先前的值并放置新值，而不是更新（更简单的脚本）。为了使它们全部一起正常工作，需要进行一些额外的美化更改（例如，declare @T table(id int identity, name nvarchar(50), value nvarchar(50)) 需要更改为 declare @T HashTable 等），但应该很简单。 - jwaliszko

1

你可以通过从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

- JamieSee

1

我决定更新我的现有答案（只是出于好奇和教育目的）。我推送了另一个版本，以保留两个版本并保持追踪改进部分的可能性：

第一种方法的更新-每列顺序插入/更新(使用游标，删除冗余临时表)：

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

    -- 如果存在，则更新行
    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        
    -- 如果更新语句失败，则插入该行
    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)

    -- 声明优化游标(fast_forward指定forward_only、read_only游标，启用性能优化)
    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       
            -- 在此处执行SP (顺便说一下:不能从select语句中执行SP)
            exec mysp_update @table_name, @name, @value        
            -- 获取下一行
            fetch next from mycursor into @name, @value
        end 
    close mycursor;
    deallocate mycursor;
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)

    -- 声明优化游标(fast_forward指定forward_only、read_only游标，启用性能优化)
    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$', '')

    -- 如果存在，则更新行  
    exec sp_executesql @update_statement      
    -- 如果更新语句失败，则插入该行
    if (@@

<p>使用方式如下：</p>

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

- jwaliszko

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mikael Eriksson · Accepted Answer

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