SQL узел XML Access «п» в цикле

голоса
0

У меня есть асинхронная система аудита, которую я написал для SQL Server, который использует триггера, чтобы отправить сообщение в очередь Service Broker, который вызывает хранимую процедуру для обработки очереди и журнала детали.

Это все работает отлично, если одно изменение (обновление или Delete) происходит, используя следующие фрагменты кода: -

Получить сообщение из очереди:

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

Я затем сохранить имя поля я хочу, чтобы извлечь в переменную @fieldname

Получить узел Я хочу от XML

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

Бит трудно увидеть с узким кодом коробкой, но если вы прокрутите вправо, вы увидите число в квадратных скобках рядом с концом является номером узла, [1], [2], [3] и т.д.

Так, по логике вещей, я просто нужно иметь переменный, содержащее число узлов топок, и цикл через него: -

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 содержит правильное число, так что часть работы (при испытании самостоятельно), однако с SET @InsertedValue = линии в ней не будет даже позволит мне запустить процедуру сценарий ALTER, он падает с: -

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

на SET @InsertedValue = линии

Я попытался заливкой переменного как целое нескольких способов: -

[Хз: целое число (SQL: переменная ( @ nEntries))]

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

Это дает ту же ошибку: -

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

[SQL: переменная ( @ nEntries) брошено как хз: целое число]

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

Это дает:

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

[SQL: переменная ( @ nEntries) брошено как хв:? Целое число]

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

Это заставляет меня обратно

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

[SQL: переменная ( @ nEntries)] брошено как хз: целое число?

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

Это в основном лежит на мне: -

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

В соответствии с:-

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

Вы можете отлиты из untypedAtomic в десятичную, и целое число является подтипом десятичной.

Так, не знаю, почему он стонет об этом.

Я попытался без квадратных скобок: SQL: переменная ( «@ nEntries»)

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

Это дало мне

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

Я попытался с дополнительными квадратными скобками: [[SQL: переменный ( «@ nEntries»)] брошен как хв: целое число]

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

Это дает

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

Я знал, что это не будет работать, так как я уже открыл для себя этого создания системы, в первую очередь, но только, чтобы покрыть все основания, я попытался с помощью конкатенации

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

Как и следовало ожидать, это дало

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

Я также попытался использовать переменную: -

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

Снова

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

И вот в чем дело, я решил просить помощи у вас прекрасных людей!

Учитывая, что я использую SQL: переменная достаточно успешно в том же вызове (ниже отлично работает, но получает только данные первых вставок) ...

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

... Кто-нибудь есть какие-либо идеи, я не пытался позволить мне использовать SQL: переменный для номера узла?

Редактировать:

Я также попытался [SQL: переменная ( «@ nEntries») [1]], только в случае, если он думал, что @nEntries может иметь несколько значений

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

Придает наш старый друг

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

Обход Edit: -

Я создал обходной путь, где я создать временную таблицу и вставить одну строку с каждой соответствующей вставки и удаления записи, а затем шаг через эту таблицу. Это позволяет мне использовать [1], как и прежде, который хорошо работает.

Но я все же хотел бы знать, как использовать переменную, как указано выше, для использования в будущем!

Задан 19/09/2018 в 13:35
источник пользователем
На других языках...                            

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