How to modify the attributes by putting dynamic node paths
- by sam
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
