Nodo de acceso SQL XML 'n' en un bucle

votos
0

Tengo un sistema de auditoría asíncrono que escribí para SQL Server, que utiliza disparadores para enviar un mensaje a una cola de Service Broker, que llama a un procedimiento almacenado para procesar la cola y registrar los datos.

Todo funciona perfectamente si un solo cambio (una actualización o una eliminación) pasa, utilizando los siguientes fragmentos de código: -

Recibe mensajes de la cola:

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

entonces almacenar el nombre del campo que desea extraer en el @fieldname variables

Obtener el nodo que quiero de XML

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

Todo muy bien, ahora se pone el valor que quiero.

Sin embargo, es posible que las inserciones múltiples nodos que estén presentes, por lo que tiene que pasar por todos ellos.

Acceso al nodo de inserciones específico es bastante simple, las siguientes obras si hay tres (o más)

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)'))

Poco difícil de ver con los cuadros de código estrechas, pero si se desplaza a la derecha verá el número entre paréntesis cuadrados cerca del final es el número de nodo, [1], [2], [3], etc.

Por lo tanto, lógicamente que sólo tiene que tener una variable que contiene el número de nodos Insertos, y el bucle a través de él: -

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 contiene el número correcto, de modo que parte funciona (cuando se prueba de forma independiente), sin embargo con el conjunto @InsertedValue = línea en ella ni siquiera me deja correr la secuencia de comandos ALTER PROCEDURE, se cae con: -

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

en el SET @InsertedValue = línea

He intentado colar la variable como un número entero de un par de maneras: -

[Xs: número entero (sql: variable ( @ nLas entradas))]

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

Eso le da el mismo error: -

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

[Sql: variable ( @ nLas entradas) fundido como xs: integer]

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

Eso da:

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

[Sql: variable ( @ nLas entradas) fundido como xs:? Número entero]

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

Eso me vuelve a

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

[Sql: variable ( @ nLas entradas)] elegida como xs: número entero?

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

Esto básicamente me miente: -

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

De acuerdo a:-

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

se puede transmitir desde untypedAtomic a decimal y entero es un subtipo de decimal.

Por lo tanto, no está seguro de por qué se quejaba de eso.

He intentado sin los corchetes: sql: variable ( @ nLas entradas)

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

Que me dan

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

Probé con corchetes adicionales: [[sql: variable ( @ nLas entradas)] elegida como xs:? Entero]

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

Eso da

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

Yo sabía que no iba a funcionar, como ya habíamos descubierto esta creación del sistema, en primer lugar, pero sólo para cubrir todas las bases He intentado utilizar la concatenación de cadenas

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

Como era de esperar, esto dio

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

También he intentado usar una variable: -

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)'))

Otra vez

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

Y ese es el punto que decidí pedir ayuda de usted buena gente!

Dado que yo estoy usando sql: variable de bastante éxito en la misma llamada exacta (el siguiente trabaja muy bien, pero sólo obtiene los datos primeros insertos) ...

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

... ¿alguien tiene alguna idea que no he tratado de dejarme usar un sql: variable para el número de nodo?

Editar:

También probé [sql: variable ( @) nLas entradas [1]], en caso de que el pensamiento @nEntries podrían tener varios valores

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

Da a nuestro viejo amigo

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

Editar Solución: -

He creado una solución en la que se crea una tabla temporal, e inserte una fila con inserciones a juego y cada registro Borra, y luego paso a través de esa mesa. Eso me permite usar [1] como antes, que funciona muy bien.

Pero todavía me gustaría saber cómo usar una variable como anteriormente, para uso futuro!

Publicado el 19/09/2018 a las 13:35
fuente por usuario
En otros idiomas...                            

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