Как я могу динамически изменять структуру XML строки в SQL

Question 1

Мне нужен SQL-скрипт, который будет извлекать XML-строку из базы данных [varchar(max)], проверять ее и обновлять, если она соответствует конкретной ситуации.

Представьте, что мой xml-файл имеет следующий формат:

<root>
  <level1>
    <level2>
      <level3 />
      <level3 />
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="now is the time for XYZ">
              <options>
                <option this="that" />
                <option me="you" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="this one is not of interest">
              <options>
                <option this="that" />
                <option me="you" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="now is the time for ABC">
              <options>
                <option this="that" />
                <option me="you" />
                <option here="now" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
</root>

Итак, я хочу обновить все элементы с именем "level6" и атрибутом "здесь", значение которого начинается с "сейчас самое время". Таким образом, это должно соответствовать только двум элементам выше.

Но это не единственный критерий отбора. Список опций не должен содержать <option here="now" />. Таким образом, у нас должен остаться только один элемент для обновления.

<level6 here="now is the time for XYZ">
    <options>
        <option this="that" />
        <option me="you" />
    </options>
 </level6>

К этому элементу я затем хочу добавить недостающее <option here="now" />, так что это становится:

<level6 here="now is the time for XYZ">
    <options>
        <option this="that" />
        <option me="you" />
        <option here="now" />
    </options>
 </level6>

Итак, конечный результат должен быть:

 <root>
  <level1>
    <level2>
      <level3 />
      <level3 />
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="now is the time for XYZ">
              <options>
                <option this="that" />
                <option me="you" />
                <option here="now" />      // <- this one new
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="this one is not of interest">
              <options>
                <option this="that" />
                <option me="you" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
  <level1>
    <level2>
      <level3>
        <level4>
          <level5>
            <level6 here="now is the time for ABC">
              <options>
                <option this="that" />
                <option me="you" />
                <option here="now" />
              </options>
            </level6>
          </level5>
        </level4>
      </level3>
    </level2>
  </level1>
</root>

Предположим, что я могу считывать данные из БД в строку и что я знаю, как обновить БД, так что на самом деле это то, как манипулировать строкой xml в SQL (SQL Server).

Question 2

Вы можете использовать XML DML (модификация данных) с .modify функция для изменения XML.

SET @xml.modify('
  insert <option here="now" />
  as last into
  ( /root/level1/level2/level3/level4/level5/level6
     [substring(@here, 1, 15) = "now is the time"]
     /options [not(/option[@here = "now"])]
   )[1]');

Это работает следующим образом:

insert <option here="now" /> это значение, которое мы вставляем
as last into он идет за другими дочерними узлами выбранного
/root/level1/level2/level3/level4/level5/level6 это дает нам то, что level6 узел
[substring(@here, 1, 15) = "now is the time"] указывает, что узел должен иметь here атрибут, начинающийся с этого значения. Вы должны изменить параметр длины, чтобы он соответствовал сравниваемому значению. Здесь нет LIKE в XQuery
/options [not(/option[@here = "now"])] мы ищем options узел, у которого нет option ребенок, который, в свою очередь, имеет here="now" атрибут
[1] первый такой узел

Если вам нужно изменить несколько узлов в одном XML-документе, вам необходимо выполнить это в цикле

DECLARE @i int = 20; --max nodes

WHILE @xml.exist('
  /root/level1/level2/level3/level4/level5/level6
     [substring(@here, 1, 15) = "now is the time"]
     /options [not(option[@here = "now"])]
   ') = 1
BEGIN

    SET @xml.modify('
      insert <option here="now" /> as last into
      ( /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       )[1]');
     
    SET @i -= 1;
    IF @i = 0
        BREAK;
END;

Вы также можете сделать это для всей таблицы

DECLARE @i int = 20; --max nodes

WHILE EXISTS (SELECT 1
    FROM YourTable
    WHERE XmlColumn.exist('
      /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       ') = 1)
BEGIN

    UPDATE t
    SET XmlColumn.modify('
      insert <option here="now" /> as last into
      ( /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       )[1]')
    FROM YourTable t
    WHERE XmlColumn.exist('
      /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       ') = 1;
     
    SET @i -= 1;
    IF @i = 0
        BREAK;
END;

Для очень больших наборов данных может быть быстрее перестроить весь XML с помощью XQuery, добавив дополнительный узел с использованием построенного XML.

бд<>скрипка<>

Charlieface · Answer 1 · 2021-11-23T23:41:04

Вы можете использовать XML DML (модификация данных) с .modify функция для изменения XML.

SET @xml.modify('
  insert <option here="now" />
  as last into
  ( /root/level1/level2/level3/level4/level5/level6
     [substring(@here, 1, 15) = "now is the time"]
     /options [not(/option[@here = "now"])]
   )[1]');

Это работает следующим образом:

insert <option here="now" /> это значение, которое мы вставляем
as last into он идет за другими дочерними узлами выбранного
/root/level1/level2/level3/level4/level5/level6 это дает нам то, что level6 узел
[substring(@here, 1, 15) = "now is the time"] указывает, что узел должен иметь here атрибут, начинающийся с этого значения. Вы должны изменить параметр длины, чтобы он соответствовал сравниваемому значению. Здесь нет LIKE в XQuery
/options [not(/option[@here = "now"])] мы ищем options узел, у которого нет option ребенок, который, в свою очередь, имеет here="now" атрибут
[1] первый такой узел

Если вам нужно изменить несколько узлов в одном XML-документе, вам необходимо выполнить это в цикле

DECLARE @i int = 20; --max nodes

WHILE @xml.exist('
  /root/level1/level2/level3/level4/level5/level6
     [substring(@here, 1, 15) = "now is the time"]
     /options [not(option[@here = "now"])]
   ') = 1
BEGIN

    SET @xml.modify('
      insert <option here="now" /> as last into
      ( /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       )[1]');
     
    SET @i -= 1;
    IF @i = 0
        BREAK;
END;

Вы также можете сделать это для всей таблицы

DECLARE @i int = 20; --max nodes

WHILE EXISTS (SELECT 1
    FROM YourTable
    WHERE XmlColumn.exist('
      /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       ') = 1)
BEGIN

    UPDATE t
    SET XmlColumn.modify('
      insert <option here="now" /> as last into
      ( /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       )[1]')
    FROM YourTable t
    WHERE XmlColumn.exist('
      /root/level1/level2/level3/level4/level5/level6
         [substring(@here, 1, 15) = "now is the time"]
         /options [not(option[@here = "now"])]
       ') = 1;
     
    SET @i -= 1;
    IF @i = 0
        BREAK;
END;

Для очень больших наборов данных может быть быстрее перестроить весь XML с помощью XQuery, добавив дополнительный узел с использованием построенного XML.

бд<>скрипка<>

Как я могу динамически изменять структуру XML строки в SQL

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории