Inserting an attribute on multiple nodes in incrementing order

Question

I have a XML column in a SQL Server table. I want to add an attribute to the nodes present in the XML. That attribute will be a unique identifier to the nodes.

I have written some code like:

DECLARE @i int, @key varchar(20);
SET @key = 'key'
SET @i = 1

IF (@i < 10)
    SET @key = @key + @i

UPDATE copy
SET dataxml.modify('insert attribute key {@key}
                    into (/Package[1]/Section/*[local-name() = ("List", "InputNumber", "InputText")][@i])[1]') 
WHERE id = 10;

I am not sure how to loop over this XML and how exactly to add the attribute to the correct node because it has nodes like List, InputNumber, InputText in random order.

The XML looks like:

<Package>
   <Section name='BOX'>
       <InputNumber description="[1] What is the height of the Box."> 
           <value/> 
       </InputNumber>
       <InputNumber description="[2] What is the width of the Box."> 
           <value/> 
       </InputNumber>
       <List description="[3] What is the type of BOX."> 
           <option/>
      </List>
   </Section>
   <Section name='Cover'>
       <InputText description="[4] Color of cover."> 
          <value/> 
       </InputText>
       <List description="[5] Type of cover."> 
          <option/>
      </List>
    </Section>
</Package>

I want the output to be like:

<Package>
   <Section name='BOX'>
       <InputNumber key="key1" description="[1] What is the height of the Box."> 
           <value/> 
       </InputNumber>
       <InputNumber key="key2" description="[2] What is the width of the Box."> 
           <value/> 
       </InputNumber>
       <List key="key3" description="[3] What is the type of BOX."> 
           <option/>
      </List>
   </Section>
   <Section name='Cover'>
       <InputText key="key4" description="[4] Color of cover."> 
          <value/> 
       </InputText>
       <List key="key5" description="[5] Type of cover."> 
          <option/>
      </List>
    </Section>
</Package>

I am getting error while running the mentioned query:

XQuery [copy.DataXML.modify()]: Top-level attribute nodes are not supported

This might get really ugly... I can think of some solutions for an easy structure, where everything is known in advance. But if this is a simplified sample and your real XML is more complex or might need a more generic approach, this will fail... Furthermore, it looks like a "XY-problem". The elemt's position is fixed within the document. Please provide some more background... — Shnugo, Apr 21 '20 at 14:54

Mikael Eriksson · Answer 1 · 2020-04-27T08:37:22.010

You can do it in a while loop as long as there is something to update.

declare @T table(dataxml xml not null);

insert into @T(dataxml) values
('<Package>
   <Section name=''BOX''>
       <InputNumber description="[1] What is the height of the Box."> 
           <value/> 
       </InputNumber>
       <InputNumber description="[2] What is the width of the Box."> 
           <value/> 
       </InputNumber>
       <List description="[3] What is the type of BOX."> 
           <option/>
      </List>
   </Section>
   <Section name=''Cover''>
       <InputText description="[4] Color of cover."> 
          <value/> 
       </InputText>
       <List description="[5] Type of cover."> 
          <option/>
      </List>
    </Section>
</Package>');

declare @key int = 1;

while 1 = 1
begin
  update @T
  set dataxml.modify('insert attribute key {sql:variable("@key")} into 
                      (/Package/Section/*[local-name() = ("List", "InputText", "InputNumber") and not(@key)])[1]')
  where dataxml.exist('/Package/Section/*[local-name() = ("List", "InputText", "InputNumber") and not(@key)]') = 1;

  if @@rowcount = 0
    break;

  set @key += 1;

end;

Result:

<Package>
  <Section name="BOX">
    <InputNumber description="[1] What is the height of the Box." key="1">
      <value />
    </InputNumber>
    <InputNumber description="[2] What is the width of the Box." key="2">
      <value />
    </InputNumber>
    <List description="[3] What is the type of BOX." key="3">
      <option />
    </List>
  </Section>
  <Section name="Cover">
    <InputText description="[4] Color of cover." key="4">
      <value />
    </InputText>
    <List description="[5] Type of cover." key="5">
      <option />
    </List>
  </Section>
</Package>

Inserting an attribute on multiple nodes in incrementing order

1 Answers1