1

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?

  • Is there any reason you can't turn you XQuery portion into a CTE or Temp Table? That would give you something that is much easier to manipulate for the data and your string manipulation with regular SQL. We do something similar in a data sync service we built, the communication layer expects everything in XML but the DB creates and shreds XML before using a CTE or Temp Table to feed it into a regular SQL SP. – Duffy Feb 23 '16 at 15:29
  • @Duffy - I don't think it would help. For my purpose and according to my limited knowledge, CTE would be almost identical to Xquery in functionality. Question is: how to go through the CTE and manipulate values of the cells of each row using several conditions based on scalar variables (which is, I believe, rather difficult if you work on a data set? – AcePL Feb 23 '16 at 23:37
  • I think I'm missing how your XQuery portion relates to your String manipulation. Is the string manipulation being done on the XQuery results? Or is it done on something else and needs to be integrated into the XQuery's insert? – Duffy Feb 23 '16 at 23:47
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.