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
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]')
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.
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
-Eric