nó de acesso XML SQL 'n' em um loop

votos
0

Eu tenho um sistema de auditoria assíncrona que escrevi para SQL Server, que usa gatilhos para enviar uma mensagem para uma fila Service Broker, que chama um procedimento armazenado para processar a fila e registrar os detalhes.

Tudo funciona perfeitamente, se uma única alteração (uma actualização ou uma eliminação) acontece, usando os seguintes trechos de código: -

Obter mensagem da fila:

DECLARE @Message XML;
RECEIVE TOP(1) @Message = CONVERT(NVARCHAR(MAX), message_body) FROM AuditLogReceiveQueue;

Eu, então, armazenar o nome do campo que deseja extrair para o @fieldname variável

Obter o nó que eu quero do XML

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[1]', 'NVARCHAR(Max)'))

Tudo bem, este obtém o valor que eu quero.

No entanto, é possível para nós de várias inserções de estar presente, então eu preciso passar por todos eles.

Acessando o nó Insere específico é bastante simples, os seguintes trabalhos se houver três (ou mais)

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[1]', 'NVARCHAR(Max)'))
SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[2]', 'NVARCHAR(Max)'))
SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[3]', 'NVARCHAR(Max)'))

Pouco difícil de ver com as caixas de código estreitas, mas se você rolar para a direita, você verá o número entre colchetes perto do final é o número de nó, [1], [2], [3] etc.

Então, logicamente eu só preciso ter uma variável que contém o número de nós inserções e loop-lo: -

DECLARE @nEntries INT = (SELECT @Message.value('count(/Inserts)', 'int'))

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[sql:variable(@nEntries)]', 'NVARCHAR(Max)'))

@nEntries contém o número correto, para que parte funciona (quando testado de forma independente), porém com a SET @InsertedValue = linha não vai mesmo deixar-me executar o script PROCEDIMENTO ALTER, ele cai com: -

XQuery [value()]: 'value()' requires a singleton (or empty sequence),
    found operand of type 'xdt:untypedAtomic *'

Sobre o conjunto @InsertedValue = linha

I tentou lançar a variável como um inteiro de duas maneiras: -

[XS: inteiro (SQL: variável ( @ nEntradas))]

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[xs:integer(sql:variable(@nEntries))]', 'NVARCHAR(Max)'))

Que dá o mesmo erro: -

XQuery [value()]: 'value()' requires a singleton (or empty sequence),
    found operand of type 'xdt:untypedAtomic *'

[SQL: variável ( @ nEntradas) fundido como xs: número inteiro]

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[sql:variable(@nEntries) cast as xs:integer]', 'NVARCHAR(Max)'))

Isso dá:

XQuery [value()]: In this version of the server, 'cast as <type>' is not available. 
Please use the 'cast as <type> ?' syntax.

[Sql: variable ( @ nEntradas) escalado como xs: integer]

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[sql:variable(@nEntries) cast as xs:integer ?]', 'NVARCHAR(Max)'))

Isso me traz de volta

XQuery [value()]: 'value()' requires a singleton (or empty sequence),
    found operand of type 'xdt:untypedAtomic *'

[Sql: variable ( @ nEntradas)] escalado como xs: integer?

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[sql:variable(@nEntries)] cast as xs:integer ?', 'NVARCHAR(Max)'))

Este encontra-se basicamente para mim: -

XQuery [value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:integer ?'

De acordo com:-

https://docs.microsoft.com/en-us/sql/xquery/type-casting-rules-in-xquery?view=sql-server-2017

você pode lançar a partir untypedAtomic para decimal e integer é um sub-tipo de decimal.

Então, não tenho certeza por que ele está gemendo sobre isso.

Tentei sem os colchetes: sql: variable ( @ nEntradas)

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])sql:variable(@nEntries)', 'NVARCHAR(Max)'))

Isso dá-me

XQuery [value()]: No more tokens expected at the end of the XQuery expression. Found 'sql'.

Eu tentei com colchetes extras: [[sql: variable ( @ nEntradas)] escalado como xs: integer]

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[[sql:variable(@nEntries)] cast as xs:integer ?]', 'NVARCHAR(Max)'))

Isso dá

XQuery [value()]: Syntax error near '['

Eu sabia que não iria funcionar, como eu já tinha descoberto isso criando o sistema em primeiro lugar, mas apenas para cobrir todas as bases Eu tentei usar concatenação

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[' + CONVERT(NVARCHAR(Max),@nEntries) + '] cast as xs:integer ?]', 'NVARCHAR(Max)'))

Como esperado, este deu

The argument 1 of the XML data type method value must be a string literal.

Eu também tentei usar uma variável: -

DECLARE @Query NVARCHAR(Max) = '(/Inserts/*[local-name()=sql:variable(@fieldname)])[' + CONVERT(NVARCHAR(Max),@nEntries) + '] cast as xs:integer ?]'
SET @InsertedValue = (SELECT @Message.value(@Query, 'NVARCHAR(Max)'))

Novamente

The argument 1 of the XML data type method value must be a string literal.

E esse é o ponto que eu decidi pedir a ajuda de vocês, bons folks!

Dado que eu estou usando sql: variable bastante sucesso no exato a mesma chamada (a seguir funciona bem, mas só recebe os dados primeiras inserções) ...

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[1]', 'NVARCHAR(Max)'))

... alguém tem alguma idéia que eu tentei não me deixar usar um sql: variável para o número do nó?

Editar:

Eu também tentei [sql: variable ( @ nEntradas) [1]], apenas no caso de ele pensou @nEntries poderia ter vários valores

SET @InsertedValue = (SELECT @Message.value('(/Inserts/*[local-name()=sql:variable(@fieldname)])[sql:variable(@nEntries)[1]]', 'NVARCHAR(Max)'))

Dá nosso velho amigo

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Editar Solução: -

Eu criei uma solução onde eu criar uma tabela temporária e inserir uma linha com cada inserções correspondentes e registro Apaga, e depois passo por aquela mesa. Que me permite usar [1] como antes, que funciona muito bem.

Mas eu ainda gostaria de saber como usar uma variável como acima, para uso futuro!

Publicado 19/09/2018 em 13:35
fonte usuário
Em outras línguas...                            

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more