Modify several xml attributes, based on a list

Question

From a previous post: SQL Server XML add attribute if non-existent

What I would like to do is be able to modify multiple tags. Below is the code that shows what I would like to do, but cannot, since I get the error: The argument 1 of the XML data type method "exist" must be a string literal. Is there a way to modify the XML using variables, rather than literals?

ALTER FUNCTION [dbo].[ConvertXmlData](@xmlData XML)
RETURNS XML
AS
BEGIN

    DECLARE @tags TABLE (
      ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
      TAG VARCHAR(25)
    )

    INSERT INTO @tags
    SELECT 'xxx' UNION   
    SELECT 'yyy'

    DECLARE @counter INT
    DECLARE @count   INT
    DECLARE @id      INT
    DECLARE @tag     VARCHAR(25)
    DECLARE @exist   VARCHAR(100)
    DECLARE @insert  VARCHAR(100)
    DECLARE @existX  VARCHAR(100)
    DECLARE @insertX VARCHAR(100)

    SET @exist   = 'descendant::{0}[not(@runat)]'
    SET @insert  = 'insert attribute runat { "server" } into descendant::{0}[not(@runat)][1]'
    SET @counter = 1
    SELECT @count = COUNT(*) FROM @tags

    WHILE @counter <= @count BEGIN

      SELECT @tag = TAG FROM @tags WHERE ID = @counter
      SET @existX = REPLACE(@existX,   '[0]', @tag)
      WHILE @xmlData.exist(@existX) = 1 BEGIN
        SET @xmlData.modify(REPLACE(@insertX, '[0]', @tag));
      END

      SET @counter = @counter + 1
    END    

  RETURN @xmlData
END

score 2 · Accepted Answer · answered Jul 07 '11 at 17:42

You can't use a variable as an argument to the xml-functions but you can use variables (and table columns) in the literal expression.

I guess this does what you want. At least it should give you an idea of what you can do.

declare @xmlData xml
set @xmlData = 
'<something>
   <xxx id="1"/>
   <xxx id="2" runat="server" />
   <xxx id="3"/>
   <yyy id="3" />
   <zzz id="1"/>
 </something>'

declare @tags table
(
  id int identity(1,1) primary key,
  tag varchar(25)
)

insert into @tags
select 'xxx' union   
select 'yyy'

declare @tag varchar(25)
declare @id int

select top 1
       @id = id,
       @tag = tag
from @tags
order by id

while @@rowcount > 0
begin
  while @xmlData.exist('descendant::*[local-name() = sql:variable("@tag") and not(@runat)]') = 1 
  begin
    set @xmlData.modify('insert attribute runat { "server" } into descendant::*[local-name() = sql:variable("@tag") and not(@runat)][1]');
  end

  select top 1
       @id = id,
       @tag = tag
  from @tags
  where id > @id
  order by id
end    

select @xmlData

Result:

<something>
  <xxx id="1" runat="server" />
  <xxx id="2" runat="server" />
  <xxx id="3" runat="server" />
  <yyy id="3" runat="server" />
  <zzz id="1" />
</something>

Modify several xml attributes, based on a list

1 Answers1