Add more than one XML element with different attributes

Hello, I know I'm going crazy here because I'm sure I'm missing something simple.. in a nut shell I'm trying to add a series of XML element node with attribute details

The following code produces a single event within the XML ...

declare @debugOutput xml ,    @debugXML xml   , @input_debugString varchar(max)

-- in the event there is already data in the xml blob

    set @debugXML = '<DEBUG>
      <procOutput>
        <LogID>999</LogID>
        <Events>
          <Event EventData="test event 1" EventId="2" />
        </Events>
      </procOutput>
    </DEBUG>'

    set @input_debugString = 'test event 2' -- < in the event new row
    
    select @debugXML = recDebug from load.lgLoadAuditLog where lgLoadAuditLogKey = 2345
    if @debugXML is null
        begin 
        set @debugXML = '<DEBUG></DEBUG>'
    end
    
    declare @logid int = 999
    -- // create a node for the new string 
    if (select @debugXML.exist('/DEBUG/procOutput')) = 0
        begin
        set @debugOutput = '<procOutput></procOutput>'
        set @debugXML.modify('insert sql:variable("@debugOutput") as last into (/DEBUG)[1]')
        set @debugXML.modify('insert element  LogID {sql:variable("@LogId")} as last into (/DEBUG/procOutput)[1]')
        set @debugOutput = '<Events></Events>'
        set @debugXML.modify('insert sql:variable("@debugOutput") as last into (/DEBUG/procOutput)[1]')
    end
    
    -- // lets get the number of event nodes
    declare @id as int = 0
    set @id = (select @debugXML.value('count(/DEBUG/procOutput/Events)', 'int')) + 1
    
        set @debugXML.modify('insert <Event></Event> as last into (/DEBUG[1]/procOutput[1]/Events[1])[1]')
        set @debugXML.modify('insert attribute EventId {sql:variable("@Id")} as last into (/DEBUG/procOutput/Events/Event)[1]')
        set @debugXML.modify('insert attribute EventData {sql:variable("@input_debugString")} as last into (/DEBUG/procOutput/Events/Event)[1]')
    
    select @debugXML
    -- the xml is result but the existing row is over written

It produces the following result:

<DEBUG>
  <procOutput>
    <LogID>999</LogID>
    <Events>
      <Event EventData="test event 2" EventId="2" />
    </Events>
  </procOutput>
</DEBUG>

In the event the XML blob is empty Ill add a structure like the following:

<DEBUG>
  <procOutput>
    <LogID>999</LogID>
    <Events>
      <Event />
    </Events>
  </procOutput>
</DEBUG>

Each time I alter the XML I want to add a continuous set of events like the following:

<DEBUG>
  <procOutput>
    <LogID>999</LogID>
    <Events>
      <Event EventData="test event 1" EventId="1" />
      <Event EventData="test event 2" EventId="2" />
      <Event EventData="test event 3" EventId="3" />
    </Events>
  </procOutput>
</DEBUG>

Any thoughts?
Thanks
-Eric

I think here is the problem: Event[sql:variable("@Id")]

 set @debugXML.modify('insert <Event></Event> as last into (/DEBUG[1]/procOutput[1]/Events[1])[1]')
    set @debugXML.modify('insert attribute EventId {sql:variable("@Id")} as last into (/DEBUG/procOutput/Events/Event[sql:variable("@Id")])[1]')
    set @debugXML.modify('insert attribute EventData {sql:variable("@input_debugString")} as last into (/DEBUG/procOutput/Events/Event[sql:variable("@Id")])[1]')

and also for the count , I think should be a little change on it ( no completed sure ):

set @id = (select @debugXML.value('count(/DEBUG/procOutput/Events/Event)', 'int')) + 1

so this will look like this:

set @id = (select @debugXML.value('count(/DEBUG/procOutput/Events/Event)', 'int')) + 1
set @debugXML.modify('insert <Event></Event> as last into (/DEBUG[1]/procOutput[1]/Events[1])[1]')
set @debugXML.modify('insert attribute EventId {sql:variable("@Id")} as last into (/DEBUG/procOutput/Events/Event[sql:variable("@Id")])[1]')
set @debugXML.modify('insert attribute EventData {sql:variable("@input_debugString")} as last into (/DEBUG/procOutput/Events/Event[sql:variable("@Id")])[1]')

Hi stepson,
First off thank you for your reply.
Your solution worked perfectly , totally missed setting the position !

Thanks again!
-Eric

Oh one note: During my testing I thought it was my machine at first but the time it took to alter the XML while setting the position took nearly 15 seconds. I even went as far as to specify the position of the XML nodes leading to the ie (/DEBUG[1]/procOutput[1]/Events[1]/Event[sql:variable("@Id")])[1]') element. Weird right?

declare @debugOutput xml ,    @debugXML xml   , @input_debugString varchar(max), @debugTaskDt datetime = getdate()

 --in the event there is already data in the xml blob
set @debugXML = '<DEBUG>
  <procOutput>
    <LogID>999</LogID>
    <Events>
      <Event EventData="test event 1" EventId="1" />
    </Events>
  </procOutput>
</DEBUG>'

select @debugXML as [Before Anything( existing record) ], datediff(millisecond,@debugTaskDt,getdate()) as Duration

set @input_debugString = 'test event 2' -- < in the event new row
declare @logid int = 999
--select @debugXML = recDebug from load.lgLoadAuditLog where lgLoadAuditLogKey = 2345
if (select @debugXML.exist('/DEBUG')) is null
    begin 
    set @debugXML = '<DEBUG>
  <procOutput>
    <LogID>'+cast(@logid as varchar)+'</LogID>
    <Events>
    </Events>
  </procOutput>
</DEBUG>'
end

select @debugXML as [Before New Elements are added], datediff(millisecond,@debugTaskDt,getdate()) as Duration


-- // create a node for the new string 
if (select @debugXML.exist('/DEBUG/procOutput')) = 0
    begin
    set @debugOutput = '<procOutput></procOutput>'
    set @debugXML.modify('insert sql:variable("@debugOutput") as last into (/DEBUG)[1]')
    set @debugXML.modify('insert element  LogID {sql:variable("@LogId")} as last into (/DEBUG/procOutput)[1]')
    set @debugOutput = '<Events></Events>'
    set @debugXML.modify('insert sql:variable("@debugOutput") as last into (/DEBUG/procOutput)[1]')
end

select @debugXML as [Before New Elements are added], datediff(millisecond,@debugTaskDt,getdate()) as Duration
-- // lets get the number of event nodes
declare @id as int = 0
set @id = (select @debugXML.value('count(/DEBUG/procOutput/Events/Event)', 'int')) + 1
select @id as [New evenet Id to be added]
set @debugXML.modify('insert <Event></Event> as last into (/DEBUG[1]/procOutput[1]/Events[1])[1]')
    --set @debugXML.modify('insert attribute EventId {sql:variable("@Id")} as last into (/DEBUG/procOutput/Events/Event)[1]')
    --set @debugXML.modify('insert attribute EventData {sql:variable("@input_debugString")} as last into (/DEBUG/procOutput/Events/Event)[1]')
set @debugXML.modify('insert attribute EventId {sql:variable("@Id")} as last into (/DEBUG/procOutput/Events/Event[sql:variable("@Id")])[1]')
set @debugXML.modify('insert attribute EventData {sql:variable("@input_debugString")} as last into (/DEBUG/procOutput/Events/Event[sql:variable("@Id")])[1]')
select @debugXML as [After New Elements are added], datediff(millisecond,@debugTaskDt,getdate()) as Duration

with correct the results of --> However with a heavy performance hit.

<DEBUG>
  <procOutput>
    <LogID>999</LogID>
    <Events>
      <Event EventData="test event 1" EventId="1" />
      <Event EventData="test event 2" EventId="2" />
    </Events>
  </procOutput>
</DEBUG>

Sorry please disregard my last post... clearly it was something on my server I was testing on ... things are running as expected.. sorry for the confusion if any :slight_smile:
-Eric