主页 / dba / 问题 / 130190
Accepted
AcePL
Asked: 2016-02-24 07:20:06 +0800 CST

Xquery、insert、Trigger 和无处压缩存储过程

  • 772

我有一个存储过程，基本上是 xquery 从 xml 中提取数据并将其插入到多个表中。一旦它插入到最后一个提到的表中，触发器就会启动，然后该数据用于执行更多数据操作。

这一切都很好，直到我必须做一些严肃的字符串切割和拼接。严肃地说，我的意思是将从表的多行和多列中选择的客户姓氏和名字添加到一个字符串中。以前它是通过第 3 方软件完成的，该软件分解 xml 并调用 SQL Server 存储过程，并对从 xml 派生的数据集中的每一行进行字符串操作。

现在我正在使用 Xquery，这是不可用的——insert from select from xquery 将不允许重新使用所述存储过程，而且我不能使用代码位在 xquery 中进行操作。

字符串操作的示例代码：

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)

我需要将上面的结果分配给 xquery 中的值：

Insert into a (col1, col2,...)
Select
t.c.value('(../Ref)[1]','varchar(50)') as col1
....
from @xml.nodes(...)t(c)

我知道 Xquery 插入多行，所以没有简单的方法来为每一行调用旧的存储过程，但是 OTOH 我也不能@string像上面那样进行操作。

有没有我不熟悉的解决方案？

我（再次）将字符串操作代码转换为函数，这次它起作用了。但是在 xml 的其他值上还有很多工作要做……所以问题是：有没有办法用 xquery 做 RBAR，所以我可以根据需要以各种方式操作数据？就像现在一样，插入是在多行上完成的，所以我不能做简单的条件：if @variable = somevalue begin...，其中 @variable 作为存储过程参数传递？我坚持慢速方式 (rbar) 而不是像@Duffy 建议的那样去 CTE 有什么意义吗？

stored-procedures sql-server-2012

1 个回答

  1. Best Answer

    您熟悉sql:column和sql:variable吗？它们允许您使用外部变量或表来操作 XML？完成此演示并检查不同的“之前”和“之后”结果，看看它们是否可以帮助您：

    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
    • 1

相关问题