SQL Server XML - modify multiple elements

Question

I have a SQL Server XML column with data like this:

<History>
   <Entry>
      .....
   </Entry>
   <Entry>
      .....
   </Entry>
</History>

I need to add a unique identifier to each Entry element, giving this result:

<History>
  <Entry entryID="AAA">
      .....
   </Entry>
   <Entry entryID="BBB">
      .....
   </Entry>
</History>

I have it working for the first occurrence of the Entry element but don't know how to apply it to all occurrences. Also, this entryID is a GUID and I don't know how to generate a new one for each row.

Here is how I update the first element.

declare @eventId varchar(64)
set @eventId =  CONVERT(varchar(64),NEWID())
update Histories
       set XmlHistory.modify('
              insert attribute EntryID {sql:variable("@eventId")}
                     into (History/Entry)[1]
       ')
where HistoryID=285162

I also have query that selects the elements that need this attribute and do not already have it. This gives the primary key and the element I need to update. I can't get a unique element identifier to use for the element array index.

select h.id rowPK, m.c.query('.') theElement
from TheTable h
    cross apply h.XMLColumn.nodes('History/Entry[not(@EntryID)]') m(c)
where XMLColumn.exist('(History/Entry)')= 1

score 0 · Answer 1 · answered Apr 22 '19 at 18:40

Manipulating XML in SQL Server can be very difficult, if you have the option, any other option! you should apply the unique id's before loading this as XML to Sql Server. The best I could do was to shred the XML to a table variable, add the keys and then extract as an XML stream. I hope this helps ...

Note, this does check for duplicate keys so you would need to handle that and you will need to include any additional nodes or elements that you did not reference in the question.

declare @Histories table
(
    HistoryID int,
    XmlHistory xml
)

insert into @Histories values (285162, '
<History> 
  <Entry> 
    Entry 1
  </Entry> 
  <Entry> 
    Entry 2
  </Entry> 
  <Entry> 
    Entry 3
  </Entry> 
  <Entry> 
    Entry 4
  </Entry> 
</History>');

declare @tmp table 
(
  EntryVal varchar(max),
  EntryGuid varchar(64)
)

insert into @tmp(EntryVal, EntryGuid)
SELECT p1.value(N'.[1]','nvarchar(max)') AS EntryValue, CONVERT(varchar(64),NEWID())
from   @Histories H1
outer apply H1.XmlHistory.nodes(N'/History/Entry') AS A(p1)

select EntryGuid as '@ID', EntryVal as "data()"
from   @tmp
for    XML PATH ('ENTRY'), root ('HISTORY');

The output should look like this

<HISTORY>
  <ENTRY ID="1C5C9492-36C8-4E4E-9AE3-DF7E2F1C1948"> 
    Entry 1
  </ENTRY>
  <ENTRY ID="9AC4BB5D-C471-4C89-947B-8C17D2BD446C"> 
    Entry 2
  </ENTRY>
  <ENTRY ID="10A81C91-A58B-4846-A857-A14BFB7F9CB7"> 
    Entry 3
  </ENTRY>
  <ENTRY ID="0E65D134-37A2-489C-8C72-5BE52D08D7B1"> 
    Entry 4
  </ENTRY>
</HISTORY>

SQL Server XML - modify multiple elements

1 Answers1