I have a Stored procedure, which is, basically, xquery pulling data from xml and inserting it into multiple tables. Once it's inserting into last of the mentioned tables, the trigger fires up and then this data is used to do more data manipulation.
it all goes nicely up to a point where I have to do some serious string cutting & splicing. By serious I mean adding to one string client surnames and first names selected from multiple rows and columns from a table. previously it was done by way of 3-rd party software, which shredded xml and called SQL Server stored procedure with said string manipulation for each row from dataset it derived from xml.
Now that I'm using Xquery this is unavailable - insert from select from xquery will not allow to either re-use the said stored procedure and I can't use the bit of code to do the manipulation in xquery.
Sample code for string manipulation:
select top 100 @string = @string + Surname +' & ' from usr_bie where ref = @ref and code = @code and type = 'Client' order by mc desc Select @string = left(@string,len(@string)-2) + ', ' Select top 100 @string = @string + FirstName + (case when middlename is not null then ' ' + Middlename else '' end ) + ' & ' from usr_bie where ref = @ref and code = @code and type = 'Client' order by mc desc Select @string = left(@string,len(@string)-2)
I need to assign the result of the above to value in xquery:
Insert into a (col1, col2,...) Select t.c.value('(../Ref)','varchar(50)') as col1 .... from @xml.nodes(...)t(c)
I understand that Xquery inserts multiple rows, so there's no easy way to call the old stored procedure for each row, but OTOH I also can't do my
@string manipulation as above.
Is there a solution I'm not familiar with?
I converted the string manipulation code to function (again), and this time it works. But there still is work to be done on the other values from xml... So question is: is there a way to do RBAR with xquery, so I can manipulate data in various ways as I need? As it is right now, Insert is being done on multiple rows, so I can't do do simple condition:
if @variable = somevalue begin..., where @variable is passed as a stored procedure parameter? And Is there any sense in my sticking to the slow way (rbar), instead of - like @Duffy suggested - going to CTE?