noeud SQL XML Access « n » dans une boucle

voix
0

J'ai un système d'audit asynchrone je l'ai écrit pour SQL Server, qui utilise des déclencheurs pour envoyer un message à une file d'attente Service Broker, qui appelle une procédure stockée pour traiter la file d'attente et enregistrer les détails.

Tout fonctionne parfaitement si un seul changement (une mise à jour ou une suppression) se produit, en utilisant les extraits de code suivants: -

Obtenez message de file d'attente:

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

Je puis stocker le nom du champ que je veux extraire dans la variable @fieldname

Obtenez le nœud que je veux du XML

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

Tout va bien, cela devient la valeur que je veux.

Cependant, il est possible pour plusieurs nœuds inserts soient présents, donc je besoin de passer par tous.

Accès au nœud Inserts spécifique est assez simple, les travaux suivants s'il y a trois (ou plus)

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

Peu difficile à voir avec les boîtes de code étroites, mais si vous faites défiler vers la droite, vous verrez le nombre entre crochets à la fin est le numéro de noeud, [1], [2], [3], etc.

Donc, logiquement je juste besoin d'avoir une variable contenant le nombre de noeuds insertions et boucle à travers elle: -

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 contient le bon nombre, de sorte qu'une partie fonctionne (lorsqu'il est testé de manière indépendante), mais avec le SET @InsertedValue = ligne il ne me laisse pas même exécuter le script ALTER PROCEDURE, il tombe avec: -

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

Sur le plateau @InsertedValue = ligne

J'ai essayé coulée la variable comme un entier deux façons: -

[Xs: integer (sql: variable ( @ nEntries))]

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

Cela donne la même erreur: -

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

[Sql: variable ( @ nEntries) moulé comme xs: integer]

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

Ça donne:

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

[Sql: variable ( @ nEntries) jeté comme xs: integer]

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

Cela me ramène à

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

[Sql: variable ( @ nEntries)] jeté comme xs: integer?

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

Cela est essentiellement à moi: -

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

Selon:-

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

vous pouvez lancer de untypedAtomic en décimal et entier est un sous-type de décimale.

Alors, ne sais pas pourquoi il gémissait à ce sujet.

J'ai essayé sans les crochets: sql: variable ( @ nEntries)

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

Cela me donne

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

J'ai essayé avec des crochets supplémentaires: [[sql: variable ( @ nEntries)] jeté comme xs: integer]

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

Ça donne

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

Je savais que cela ne fonctionnerait pas, comme je l'avais déjà découvert ce la création du système en premier lieu, mais juste pour couvrir toutes les bases que j'ai essayé d'utiliser la concaténation de chaîne

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

Comme prévu, ce qui a donné

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

J'ai essayé également d'utiliser une 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)'))

Encore

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

Et c'est le point que je décidé de demander de l'aide à vous gens bien!

Étant donné que j'utilise sql: variable dans exactement le même appel tout à fait avec succès (le ci-dessous fonctionne très bien, mais seulement obtient les premières données inserts) ...

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

... Quelqu'un at-il des idées que je ne l'ai pas essayé de me laisser utiliser un sql: variable pour le numéro de noeud?

Modifier:

J'ai aussi essayé [sql: variable ( @ nEntries) [1]], juste au cas où il pensait @nEntries pourrait avoir des valeurs multiples

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

Donne notre vieil ami

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

Solution Edit: -

J'ai créé une solution de contournement où créer une table temporaire et insérer une rangée les uns avec les inserts correspondant et supprime enregistrement, et ensuite passer cette table. Cela me permet d'utiliser [1] comme avant, qui fonctionne bien.

Mais je voudrais encore savoir comment utiliser une variable comme ci-dessus, pour une utilisation future!

Créé 19/09/2018 à 13:35
source utilisateur
Dans d'autres langues...                            

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