How to modify the attributes by putting dynamic node paths

I have a code that selects all elements and their child nodes

DECLARE @x XML
DECLARE @node_no int
DECLARE @count int
DECLARE @max INT, @i INT
EXECUTE return_xml '1', NULL, @x output

Declare @temp Table
 (
  id int not null identity(1,1), ParentNodeName varchar(max), NodeName varchar(max), NodeText varchar(max)
 )

INSERT INTO @temp
SELECT  
  t.c.value('local-name(..)', 'varchar(max)') AS ParentNodeName,
        t.c.value('local-name(.)', 'varchar(max)') AS NodeName,
        t.c.value('text()[1]', 'varchar(max)') AS NodeText
    FROM    @x.nodes('/booking//*') AS t(c)

select * from @temp

Now I want to modify the attributs by putting dynamic node paths

SET @x.modify
   ('
    insert attribute MyId {sql:variable("@i")}
    as first
    into (ParentNodeName/NodeName::*[position() = sql:variable("@i")])[1]
   ')

where id = id of temp table

any Idea how can I modify my whole xml this way as I am having a untyped xml and have to add an attribute in every node

ansaurus

tags:

views:

answers:

How to modify the attributes by putting dynamic node paths

related questions