asked on

How do i delete the last node in an xml in T-SQL

hello,

I have a stored procedure that builds an xml.

I have discovered that the application that calls the stored procedure and recieves the xml has a limited size for the returned xml parameter. I can't change the application so i would like to reduce the size of the returned xml.

Is there a way to delete the last node of an xml document? I would then place it in a loop that continues to delete nodes until the length of the xml is below maximum parameter size of the receiving application.

The xml looks like this:

DECLARE @x XML;
SET @x = N'<Errors>
 <Error><VariableName>msr_Q1Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q2Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q3Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q4Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q5Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q6Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q7Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q8Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q9Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
</Errors>'

Open in new window

I need some code that does:

while length of xml < 500 (bytes) do
  delete last node

Open in new window

Is this possible?

Ryan Chong

try this:

DECLARE @x XML;
SET @x = N'<Errors>
 <Error><VariableName>msr_Q1Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q2Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q3Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q4Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q5Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q6Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q7Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q8Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
 <Error><VariableName>msr_Q9Frequency</VariableName><CustomMessage>xxxx</CustomMessage></Error>
</Errors>'

;with cte as
(
	select 
	c.query('*') error,
	row_number() over (order by (select 1)) idx
	from @x.nodes('//Error') t(c)
)
select a.error
from cte a
left join (select max(idx) idx from cte) b	
on a.idx = b.idx
where b.idx is null
for xml path('') , root('Errors')

Open in new window

soozh

ASKER

Thanks for the response.

When i try:

while datalength( @X ) > 300
begin
;with cte as
(
	select 
	c.query('*') error,
	row_number() over (order by (select 1)) idx
	from @x.nodes('//Error') t(c)
)

select @x = (select a.error
from cte a
left join (select max(idx) idx from cte) b	
on a.idx = b.idx
where b.idx is null
for xml path('') , root('Errors')
) 
end

Open in new window

I find that in the second itteration @x is set to null. Any ideas why?

Ryan Chong

what are you trying to do here?

why you want to do a while condition there?

soozh

ASKER

because i want to keep deleting the last node until the xml is less than 300 characters in length.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.