XML DML (Xpath) query to disallow dupliacte columns. It should test if a column exist before inserting into it

Question

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

When i use this query to add a node to an existing column. Every time it is ran it adds a column.

What i want to achieve is that it should check if a particular node exist with a mapping name and if it exists that dont add this node. else add it.

Link that i am following is http://msdn.microsoft.com/en-us/library/ms175466

Answer 1

Put your XML in a variable, extract the mapping name and use that in the where clause.

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