SQLServer - Add XML Element ID and Key

Question

Dear Experts,

I have an XML Like below

<Root>
<node ID = "ABC" Name = "AAAA" />

</Root>

Now I want to add the XML to add two more fields to the same Node, the output should be required as below

<Root>
<node ID = "ABC" Name = "AAAA" Department = "ABCD" DEPTID = "1234"/>
</Root>

Answer 1

Hi @Azmath Momin ,

Please refer below:

DECLARE @xml xml =N'<Root>  
<node ID = "ABC" Name = "AAAA" />  
</Root> '  
  
SET @xml.modify('               
insert   
(  
attribute Department  {"ABCD" },  
attribute DEPTID   {"1234" }  
)  
as last  
into (/Root/node)[1]') ;     
  
SELECT @xml  ;   
  
--<Root><node Department="ABCD" ID="ABC" DEPTID="1234" Name="AAAA" /></Root>  
  
--change the attributes in order  
Declare @S varchar(max) = ''  
  
Select @S = @S + concat(Item,'="',Value,'" ')  
 From (  
        Select Top 1000   
               Item   = attr.value('local-name(.)','varchar(100)')  
              ,Value  = attr.value('.','varchar(max)')   
         From  @XML.nodes('/Root/node') as A(r)  
         Cross Apply A.r.nodes('./@*') AS B(attr)  
         Order By attr.value('local-name(.)','varchar(100)')   
      ) A  
  
Select convert(xml,'<Root><node '+@S+'/></Root>')  
  
--<Root><node Department="ABCD" DEPTID="1234" ID="ABC" Name="AAAA" /></Root>  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To affect the first <node>, try this example:

declare @xml as xml =   
'<Root>  
    <node ID="ABC" Name="AAAA" />  
</Root>'  
  
select @xml  
  
set @xml.modify('insert ( attribute Department {"ABCD"}, attribute DEPTID {"1234"} ) into (/Root/node)[1]')  
  
select @xml  

It is possible to identify the target node by ID, or to deal with table's columns. Give some details if you need more features.