Início / dba / Perguntas / 130190
Accepted
AcePL
Asked: 2016-02-24 07:20:06 +0800 CST

Xquery, insert, Trigger e nenhum lugar para espremer o procedimento armazenado

  • 772

Eu tenho um procedimento armazenado, que é, basicamente, xquery puxando dados de xml e inserindo-os em várias tabelas. Depois de inserir na última das tabelas mencionadas, o gatilho é acionado e, em seguida, esses dados são usados ​​para fazer mais manipulação de dados.

tudo vai bem até um ponto em que tenho que fazer cortes e emendas sérios. Por sério, quero dizer adicionar a uma string os sobrenomes e nomes dos clientes selecionados de várias linhas e colunas de uma tabela. anteriormente, isso era feito por meio de software de terceiros, que fragmentava o xml e chamava o procedimento armazenado do SQL Server com a referida manipulação de string para cada linha do conjunto de dados derivado do xml.

Agora que estou usando Xquery, isso não está disponível - inserir de selecionar de xquery não permitirá reutilizar o referido procedimento armazenado e não posso usar o bit de código para fazer a manipulação em xquery.

Exemplo de código para manipulação de string:

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)

Eu preciso atribuir o resultado do acima ao valor em xquery:

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

Entendo que Xquery insere várias linhas, portanto, não há uma maneira fácil de chamar o antigo procedimento armazenado para cada linha, mas OTOH também não consigo fazer minha @stringmanipulação como acima.

Existe uma solução que eu não estou familiarizado?

Eu converti o código de manipulação de string para funcionar (novamente), e desta vez funcionou. Mas ainda há trabalho a ser feito nos outros valores do xml ... Então a pergunta é: existe uma maneira de fazer RBAR com xquery, para que eu possa manipular os dados de várias maneiras conforme necessário? Como está agora, Insert está sendo feito em várias linhas, então não posso fazer condição simples: if @variable = somevalue begin..., onde @variable é passado como um parâmetro de procedimento armazenado? E há algum sentido em seguir o caminho lento (rbar), em vez de - como @Duffy sugeriu - ir para o CTE?

stored-procedures sql-server-2012

1 respostas

  1. Best Answer

    Você está familiarizado com sql:column e sql:variable ? Eles permitem que você manipule XML com variáveis ​​externas ou tabelas? Trabalhe com esta demonstração e examine os diferentes resultados 'antes' e 'depois' para ver se eles podem ajudá-lo:

    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

relate perguntas