How to add xml attribute to all nodes of xml document?

Question

I have a db table called XmlDocument with XML field, which is used to store the xml. And this is a example of stored xml:

<ol type="decimal" id="id-a4bb3b6d-9908-4972-8b02-72e286795b7a">
    <item id="id-92332716-4561-4874-d353-39fdf993fa89">
        <para id="id-a3fae837-59d8-4702-b689-f869137a92a7">Item1</para>
    </item>
    <item id="id-d8031e83-5b68-47cb-f535-41bee9682f4a">
        <para id="id-8746fe56-3160-4543-bf28-5f1e1de83ec0">Item2</para>
    </item>
</ol>

My goal is to write a tsql script which will add revNumber attribute to all nodes of the document, which doesn't have this attribute. This is how it should look like:

<ol revNumber="1.1" type="decimal" id="id-a4bb3b6d-9908-4972-8b02-72e286795b7a">
    <item revNumber="1.2" id="id-92332716-4561-4874-d353-39fdf993fa89">
        <para revNumber="1.3" id="id-a3fae837-59d8-4702-b689-f869137a92a7">Item1</para>
    </item>
    <item revNumber="1.0" id="id-d8031e83-5b68-47cb-f535-41bee9682f4a">
        <para revNumber="1.2" id="id-8746fe56-3160-4543-bf28-5f1e1de83ec0">Item2</para>
    </item>
</ol>

If the node already have this attribute, we should skip it. We can use something like that, but it works only for one node:

SET @requestXML.modify(
'insert 
(        
    attribute revNumber {sql:variable("@revNum") }
)
into (/ol)[1]')

But the structure of the document could be different. What is the best way to do it?

Answer 1

Finally i found a solution:

declare @xml XML = '
<root>
    <text>test</text>
    <text>test</text>
    <text>test</text>
    <text>test</text>
</root>
'
while @xml.exist('//*[not(@TEST)]') = 1
begin
    set @xml.modify('
        insert attribute TEST {"value"}
        into (//*[not(@TEST)])[1]
    ')
end
select @xml