sql-server - XML DML(Xpath)查询禁止重复的列。它应该在插入列之前测试是否存在

update serializedvalue set value.modify('
insert
    <GridDataVisibleColumn>
      <FilterBehavior>StronglyTyped</FilterBehavior>
      <FilterBarMode>Immediate</FilterBarMode>
      <AllowFilter>false</AllowFilter>
      <AllowSort>true</AllowSort>
      <AllowDrag>true</AllowDrag>
      <AllowGroup>true</AllowGroup>
      <AllowResize>true</AllowResize>
      <ShowColumnOptions>false</ShowColumnOptions>
      <HeaderText>DeskLabel</HeaderText>
      <IncrementSeed>1</IncrementSeed>
      <IsIdentity>false</IsIdentity>
      <IsReadOnly>true</IsReadOnly>
      <MappingName>Deskl</MappingName>
      <MinimumWidth>0</MinimumWidth>
      <Width>
        <UnitType>Auto</UnitType>
        <Value>1</Value>
      </Width>
      <DataType>String</DataType>
      <UpdateMode>LostFocus</UpdateMode>
      <IsHidden>false</IsHidden>
    </GridDataVisibleColumn>
    as last into (/GridDataTableProperties/VisibleColumns)[1]

')
where   Token like '%gridsettings%'

当我使用此查询向现有列添加节点时。每次运行它都会添加一个列。
我想要实现的是，它应该检查是否存在具有映射名称的特定节点，以及是否存在不添加此节点的节点。否则，加上它。
我正在关注的链接是http://msdn.microsoft.com/en-us/library/ms175466

最佳答案

将XML放入变量中，提取映射名称并在where子句中使用该名称。

declare @XML xml
set @XML = '
    <GridDataVisibleColumn>
      <FilterBehavior>StronglyTyped</FilterBehavior>
      <FilterBarMode>Immediate</FilterBarMode>
      <AllowFilter>false</AllowFilter>
      <AllowSort>true</AllowSort>
      <AllowDrag>true</AllowDrag>
      <AllowGroup>true</AllowGroup>
      <AllowResize>true</AllowResize>
      <ShowColumnOptions>false</ShowColumnOptions>
      <HeaderText>DeskLabel</HeaderText>
      <IncrementSeed>1</IncrementSeed>
      <IsIdentity>false</IsIdentity>
      <IsReadOnly>true</IsReadOnly>
      <MappingName>Deskl</MappingName>
      <MinimumWidth>0</MinimumWidth>
      <Width>
        <UnitType>Auto</UnitType>
        <Value>1</Value>
      </Width>
      <DataType>String</DataType>
      <UpdateMode>LostFocus</UpdateMode>
      <IsHidden>false</IsHidden>
    </GridDataVisibleColumn>'

declare @MappingName varchar(50)
select @MappingName = @XML.value('(/GridDataVisibleColumn/MappingName/text())[1]', 'varchar(50)')

update serializedvalue set value.modify('
  insert
    sql:variable("@XML")
    as last into (/GridDataTableProperties/VisibleColumns)[1]
')
where Token like '%gridsettings%' and
      value.exist('/GridDataTableProperties
                    /VisibleColumns
                      /GridDataVisibleColumn
                        [MappingName = sql:variable("@MappingName")]') = 0