SQL XML aksessnode 'n' i en sløyfe

stemmer
0

Jeg har en asynkron revisjonssystem jeg skrev for SQL Server, som bruker triggere for å sende en melding til en Service Broker kø, som kaller en lagret prosedyre å behandle køen og logge detaljene.

Det hele fungerer perfekt hvis en enkelt endring (en oppdatering eller en Slett) skjer ved hjelp av følgende kodebiter: -

Få melding fra køen:

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

Jeg deretter lagre navnet på feltet jeg ønsker å trekke inn den variable @fieldname

Få noden jeg ønsker fra XML

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

Alt vel og bra, får denne verdien jeg ønsker.

Men det er mulig for flere Setter noder å være til stede, så jeg trenger å gå gjennom dem alle.

Tilgang til spesifikke Innsett noden er enkel nok, følgende verk hvis det er tre (eller flere)

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

Litt vanskelig å se med de smale kodebokser, men hvis du blar til høyre vil du se hvor mange i hakeparentes nær slutten er nodenummeret, [1], [2], [3] etc.

Så logisk jeg trenger bare å ha en variabel som inneholder antall Setter noder, og loop gjennom det: -

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 inneholder riktig nummer, slik at en del arbeid (når testet uavhengig), men med SET @InsertedValue = linjen i den vil ikke la meg kjøre ALTER PROCEDURE scriptet, faller det over med: -

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

på SET @InsertedValue = linje

I forsøkt støping av variabelen som et heltall et par måter: -

[Xs: integer (SQL: variable ( @ nOppføringer))]

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

Det gir den samme feilen: -

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

[SQL: variable ( @ nOppføringer) støpes som xs: integer]

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

Det gir:

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

[SQL: variable ( @ nOppføringer) støpes som xs:? Heltall]

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

Som får meg tilbake til

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

[SQL: variable ( @ nOppføringer)] støpt som xs: integer?

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

Dette ligger i utgangspunktet til meg: -

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

I følge:-

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

du kan kaste fra untypedAtomic til desimal, og heltall er en undertype av desimaltall.

Så, ikke sikker på hvorfor det er moaning om det.

I forsøkt uten hakeparenteser: SQL: variable ( @ nOppføringer)

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

Det gir meg

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

I forsøkt med ekstra klammeparenteser: [[SQL: variable ( @ nOppføringer)] støpt som xs:? Heltall]

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

det gir

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

Jeg visste at det ikke ville fungere, så jeg hadde allerede oppdaget dette skaper systemet i første omgang, men bare for å dekke alle baser jeg prøvde å bruke streng sammensetning

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

Som forventet, ga dette

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

Jeg har også prøvd å bruke en variabel: -

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

En gang til

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

Og det er poenget jeg bestemte meg for å tigge om hjelp fra dere fine folk!

Gitt at jeg bruker sql: variable ganske vellykket i nøyaktig samme samtalen (nedenfor fungerer fint, men blir bare de første Setter data) ...

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

... er det noen som har noen ideer jeg har ikke prøvd å la meg bruke en sql: variabel for nodenummeret?

Redigere:

Jeg har også prøvd [SQL: variable ( @ nOppføringer) [1]], i tilfelle syntes det @nEntries kan ha flere verdier

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

Gir vår gamle venn

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

Løsning Edit: -

Jeg har laget en løsning hvor jeg skape en midlertidig tabell, og sette inn en rad med hver matchende innsettinger og slett posten, og deretter gå gjennom den tabell. Som lar meg bruke [1] som før, noe som fungerer bra.

Men jeg vil fortsatt gjerne vite hvordan du bruker en variabel som ovenfor, for fremtidig bruk!

Publisert på 19/09/2018 klokken 13:35
kilden bruker
På andre språk...                            

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