Выполните итерацию по узлу XML, затем проверьте, соответствует ли значение json, затем выберите значение

Question

По-видимому, у меня есть поле вызова данных, которые хранят некоторый узел XML + JSON внутри этого узла.

Я могу получить идентификатор доставки с помощью:

JSON_VALUE(ml.Data.value('(/row/value)[2]', 'NVARCHAR(MAX)'),'$.transactions[0].deliveryId') deliveryID 

Однако транзакции не всегда могут быть во втором узле, это может быть в любой строке.

Есть ли какой-то другой способ, которым я могу сделать, чтобы выполнить итерацию узла и найти, есть ли внутри него транзакции json, а затем получить идентификатор доставки из строки?

Спасибо

<row>
  <value id="1ae95d67-599e-4ab6-9ffd-08d4d90ab608" display-name="Cardholder_id" data-type="Int32">17</value>
  <value id="1ae95d67-599e-4ab6-9ffd-08d4d90ab608" display-name="Cardholder_id" data-type="Int32">17</value>
  <value id="eb71fd46-f0b2-401d-9775-08d4d90ab608" display-name="Card_Number">3083 2614 5022 21321</value>
  <value id="4fc261b2-f776-4fd4-8e1d-08d4d90ab608" display-name="Email_Address">[e-mail]</value>
  <value id="c867d4e5-cc0b-4ee6-b911-08d6134132e0" display-name="BP_TRIGGERS_2.0">{"transactions":[{"BP_CommsRef":"V0001","BP_Offer_Expiry":"2018-10-01T00:00:00","deliveryId":"20320925","Job_Number":"A34F443","Send_Date":"2018-09-26T00:00:00"}]}</value>
</row>

Answer 1

Если я правильно понимаю, вы хотите найти JSON в вашем <value> элементы и читать deliverId оттуда:

Declare @XML xml = '
<row>
  <value id="1ae95d67-599e-4ab6-9ffd-08d4d90ab608" display-name="Cardholder_id" data-type="Int32">17</value>
  <value id="1ae95d67-599e-4ab6-9ffd-08d4d90ab608" display-name="Cardholder_id" data-type="Int32">17</value>
  <value id="eb71fd46-f0b2-401d-9775-08d4d90ab608" display-name="Card_Number">3083 2614 5022 21321</value>
  <value id="4fc261b2-f776-4fd4-8e1d-08d4d90ab608" display-name="Email_Address">[e-mail]</value>
  <value id="c867d4e5-cc0b-4ee6-b911-08d6134132e0" display-name="BP_TRIGGERS_2.0">{"transactions":[{"BP_CommsRef":"V0001","BP_Offer_Expiry":"2018-10-01T00:00:00","deliveryId":"20320925","Job_Number":"A34F443","Send_Date":"2018-09-26T00:00:00"}]}</value>
</row>';

- Это проверит на открытие { (вы могли бы искать открытие {"transactions" также)

SELECT JSON_VALUE(
       @XML.query(N'/row/value[substring(text()[1],1,1)="{"]/node()')
       .value(N'text()[1]','nvarchar(max)')
       ,N'$.transactions[0].deliveryId');

- Тот же подход, но глядя на атрибут display-name

SELECT JSON_VALUE(
       @XML.query(N'/row/value[@display-name="BP_TRIGGERS_2.0"]/node()')
       .value(N'text()[1]','nvarchar(max)')
       ,N'$.transactions[0].deliveryId');

Идея вкратце: .query() XML для <value> ниже <row>, который заполняет фильтр. Возьми это node() и читать .value() оттуда.
Это передано в JSON_VALUE для того, чтобы получить необходимые deliveryId,

Вы можете сделать то же самое с атрибутом id и вы можете передать значения с помощью переменной declard (sql:variable()) или из столбца в вашем наборе результатов (sql:column()).