Question

我的表格中有一栏,显示Xml,与以下表格一样:

<memberHours type="TeamHours[]"> 
<item> 
  <member type="String">Bill</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
<item> 
  <member type="String">John</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
<item> 
  <member type="String">Sally</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
</memberHours>

我需要找到所有成员,只要成员与John相等,然后由Joon 接替John。由于我的xml储存在Nvarchar(max)一栏中,我写上了一个功能,把该栏放在我可以使用的xml变量上。我无法列举的是,如何找到哪些项目与这一价值相匹配,如何仅取而代之(即John )。

我是2008年服务器。

Answer 1

研究了以下“MSDN”条款:

replace Value of (XMLDML)

Specifically, you might try something like this:

-- Setup test data
declare @table table (
    col nvarchar(max) not null
)
insert into @table select
 <memberHours type="TeamHours[]"> 
<item> 
  <member type="String">Bill</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
<item> 
  <member type="String">John</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
<item> 
  <member type="String">Sally</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
</memberHours> 

-- Set search/replace vars
declare @oldval nvarchar(max) =  John 
declare @newval nvarchar(max) =  Jon 
declare @oldcol xml
declare @newcol xml

-- Loop over records fitting the search
while exists (
    select null
    from (
        select cast(col as xml) as col
        from @table
    ) as a
    where col.exist( /memberHours/item/member[(text()[1]) eq sql:variable("@oldval")] ) = 1
) begin

    -- Grab a record as xml
    set @oldcol = (
        select top 1 col
        from (
            select cast(col as xml) as col
            from @table
        ) as a
        where col.exist( /memberHours/item/member[(text()[1]) eq sql:variable("@oldval")] ) = 1
    )
    set @newcol = @oldcol

    -- Modify xml data
    while @newcol.exist( /memberHours/item/member[(text()[1]) eq sql:variable("@oldval")] ) = 1 begin
        set @newcol.modify( 
            replace value of (/memberHours/item[member=sql:variable("@oldval")]/member/text())[1] with sql:variable("@newval")
         )
    end

    -- Update table
    update @table
    set col = cast(@newcol as nvarchar(max))
    where cast(cast(col as xml) as nvarchar(max)) = cast(@oldcol as nvarchar(max)) -- Cast both for equality test!

end

-- Test output
select * from @table

友情链接