Jak mogę dynamicznie zmieniać strukturę XML polecenia w SQL

Question 1

Potrzebuję SQL skrypt, który pobierze XML-wiersz z bazy danych [varchar(max)], sprawdzać i aktualizować, jeśli ona odpowiada konkretnej sytuacji.

Wyobraź sobie, że mój plik xml ma następujący format:

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

Tak więc, chcę zaktualizować wszystkie elementy o nazwie "level6" i atrybutem "tu", którego wartość zaczyna się od "teraz jest czas". W ten sposób, to musi spełniać tylko dwóch elementów powyżej.

Ale to nie jedyne kryterium wyboru. Lista opcji nie musi zawierać <option here="now" />. W ten sposób, u nas musi być tylko jeden element do aktualizacji.

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

Do tego elementu, następnie chcę dodać brakujące <option here="now" />tak , że to staje się:

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

Tak więc końcowy wynik musi być:

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

Załóżmy, że mogę odczytywać dane z BAZY danych w wierszu i że wiem, jak zaktualizować BAZY danych, tak, że faktycznie to jest to, jak manipulować ciągiem xml w SQL (SQL Server).

Question 2

Można użyć XML DML (modyfikacja danych) .modify funkcja zmiany 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]');

Działa to w następujący sposób:

insert <option here="now" /> to wartość, którą wstawiamy
as last into on idzie za innymi węzły podrzędne wybranego
/root/level1/level2/level3/level4/level5/level6 to daje nam to, że level6 węzeł
[substring(@here, 1, 15) = "now is the time"] wskazuje, że witryna musi mieć here atrybut, rozpoczynający się od tej wartości. Należy zmienić ustawienie długości, aby był zgodny z сравниваемому wartości. Tu nie ma LIKE w XQuery
/options [not(/option[@here = "now"])] szukamy options węzeł, który nie option dziecko, które, z kolei, ma here="now" atrybut
[1] pierwszy taki węzeł

Jeśli trzeba zmienić kilka węzłów w jednym dokumencie XML, należy wykonać to w pętli

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;

Można również zrobić to dla całej tabeli

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;

Dla bardzo dużych zbiorów danych może być szybciej odbudować cały XML za pomocą XQuery, dodając dodatkowy węzeł z wykorzystaniem zbudowanego XML.

obd<>skrzypce<>

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

Można użyć XML DML (modyfikacja danych) .modify funkcja zmiany 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]');

Działa to w następujący sposób:

insert <option here="now" /> to wartość, którą wstawiamy
as last into on idzie za innymi węzły podrzędne wybranego
/root/level1/level2/level3/level4/level5/level6 to daje nam to, że level6 węzeł
[substring(@here, 1, 15) = "now is the time"] wskazuje, że witryna musi mieć here atrybut, rozpoczynający się od tej wartości. Należy zmienić ustawienie długości, aby był zgodny z сравниваемому wartości. Tu nie ma LIKE w XQuery
/options [not(/option[@here = "now"])] szukamy options węzeł, który nie option dziecko, które, z kolei, ma here="now" atrybut
[1] pierwszy taki węzeł

Jeśli trzeba zmienić kilka węzłów w jednym dokumencie XML, należy wykonać to w pętli

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;

Można również zrobić to dla całej tabeli

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;

Dla bardzo dużych zbiorów danych może być szybciej odbudować cały XML za pomocą XQuery, dodając dodatkowy węzeł z wykorzystaniem zbudowanego XML.

obd<>skrzypce<>

Jak mogę dynamicznie zmieniać strukturę XML polecenia w SQL

Pytanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii