Jak lze dynamicky měnit strukturu XML řetězce v SQL

Question 1

Potřebuji SQL skript, který bude tahat XML řetězec z DB [varchar(max)], zkontrolujte ji, a aktualizovat ji, pokud to odpovídá konkrétní situaci.

Představte si, že můj xml je v následujícím formátu:

<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že, co chci udělat, je aktualizovat všechny prvky, jejichž jméno je "level6" a, které mají atribut s názvem "here", jehož hodnota začíná s "nyní je čas". Tak, že by se zápas jen dva prvky výše.

Ale to není jediná kritéria výběru. Seznam možností nesmí obsahovat <option here="now" />. Tak, že by nás opustit jen s jedním prvkem aktualizovat.

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

Na tento prvek, pak jsem chtěl přidat chybějící <option here="now" />,, tak, že se stává:

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

Takže, konečný výsledek by měl být:

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

Předpokládejme, že mohu číst data z DB do řetězce, a že vím, jak aktualizovat DB, tak je to opravdu, jak manipulovat s xml řetězec SQL (SQL Server).

Question 2

Můžete použít XML DML (data modification) s .modify funkce změnit 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]');

To funguje následovně:

insert <option here="now" /> to je hodnota, kterou jsme vkládání
as last into jde po jiné podřízené uzly z vybrané
/root/level1/level2/level3/level4/level5/level6 to nás dostane, že level6 uzel
[substring(@here, 1, 15) = "now is the time"] predikáty uzel mít here atribut začíná s touto hodnotou. Musíte upravit délku parametru, aby odpovídala hodnotě porovnáváte. Tam je ne LIKE v XQuery
/options [not(/option[@here = "now"])] jsme se podívat na options uzel, který nemá žádné option dítě, které má here="now" atribut
[1] první takový uzel

Pokud potřebujete upravit více uzlů v rámci jednoho XML dokumentu, budete muset spustit to ve smyčce

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;

Můžete si také udělat to pro celý stůl

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;

Pro velmi velké soubory dat může být rychlejší přestavět celý XML pomocí XQuery, s extra uzlu přidány pomocí Vyrobeno XML.

db<>housle

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

Můžete použít XML DML (data modification) s .modify funkce změnit 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]');

To funguje následovně:

insert <option here="now" /> to je hodnota, kterou jsme vkládání
as last into jde po jiné podřízené uzly z vybrané
/root/level1/level2/level3/level4/level5/level6 to nás dostane, že level6 uzel
[substring(@here, 1, 15) = "now is the time"] predikáty uzel mít here atribut začíná s touto hodnotou. Musíte upravit délku parametru, aby odpovídala hodnotě porovnáváte. Tam je ne LIKE v XQuery
/options [not(/option[@here = "now"])] jsme se podívat na options uzel, který nemá žádné option dítě, které má here="now" atribut
[1] první takový uzel

Pokud potřebujete upravit více uzlů v rámci jednoho XML dokumentu, budete muset spustit to ve smyčce

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;

Můžete si také udělat to pro celý stůl

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;

Pro velmi velké soubory dat může být rychlejší přestavět celý XML pomocí XQuery, s extra uzlu přidány pomocí Vyrobeno XML.

db<>housle

Jak lze dynamicky měnit strukturu XML řetězce v SQL

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii