Xquery, insert, Trigger and no place to squeeze stored procedure

Question

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)[1]','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?

Answer 1

Are you familiar with sql:column and sql:variable? They allow you to manipulate XML with external variables or tables? Work through this demo and examine the different 'before' and 'after' results to see if they could help you:

USE tempdb
GO

DECLARE @xml XML
DECLARE @someString VARCHAR(50) = 'test string'

SET @xml = (
SELECT
    object_id AS "@object_id", 
    SCHEMA_NAME(schema_id) AS "@schemaName", 
    name AS "@tableName"
FROM sys.tables t
FOR XML PATH('table'), ROOT('tables'), TYPE
)

SELECT 'before 1' s, DATALENGTH(@xml) dl, @xml x

-- Use sql:variable to add a variable into the XML
SET @xml.modify('insert element someData{sql:variable("@someString")} into (tables/table)[1]')

SELECT 'after 1' s, DATALENGTH(@xml) dl, @xml x


-- Use sql:column to add a column from a table into the XML
DECLARE @t TABLE ( object_id INT PRIMARY KEY, identity_col SYSNAME )

INSERT INTO @t ( object_id, identity_col )
SELECT object_id, name
FROM sys.identity_columns

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT 
    t.c.value('@object_id', 'INT') object_id,
    t.c.query('.') tableData
INTO #tmp
FROM @xml.nodes('tables/table') t(c)



SELECT 'before 2' s, DATALENGTH(tableData) dl, tableData
FROM #tmp

UPDATE t
SET tableData.modify('insert element identCol{sql:column("ids.identity_col")} into (table)[1]')
FROM #tmp t
    CROSS APPLY @t ids

SELECT 'after 2' s, DATALENGTH(tableData) dl, tableData
FROM #tmp