Comment puis-je modifier dynamiquement la structure XML d'une chaîne SQL

Question 1

J'ai besoin d'un script SQL qui sera à tirer d'une chaîne XML à partir de la DB [varchar(max)], l'inspecter et de le mettre à jour si elle s'adapte à une situation spécifique.

Imaginez que mon xml est le format suivant:

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

Donc, ce que je veux faire est de mettre à jour tous les éléments dont le nom est "level6", et qui ont un attribut appelé "ici" dont la valeur commence par "c'est maintenant le temps". Alors, qui doit correspondre à seulement deux éléments ci-dessus.

Mais, ce n'est pas le seul critère de sélection. La liste des options ne doit pas contenir <option here="now" />. Donc, ce qui devrait nous laisser avec juste un élément à mettre à jour.

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

Pour cet élément, je veux ajouter le manque <option here="now" />, alors cela devient:

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

Donc, le résultat final devrait être:

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

Supposons que je puisse lire les données de la DB dans une chaîne, et que je sais comment mettre à jour la DB, donc c'est vraiment la façon de manipuler la chaîne xml dans SQL (SQL Server).

Question 2

Vous pouvez utiliser XML DML (modification de données) avec le .modify fonction pour modifier le 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]');

Cela fonctionne comme suit:

insert <option here="now" /> c'est la valeur que nous sommes en insertion
as last into il va après les autres nœuds enfants de la sélection d'un
/root/level1/level2/level3/level4/level5/level6 cela nous permet de nous que level6 nœud
[substring(@here, 1, 15) = "now is the time"] les prédicats le nœud d'avoir une here attribut à partir de cette valeur. Vous devez modifier le paramètre de durée pour correspondre à la valeur de la comparaison. Il n'y a pas de LIKE en XQuery
/options [not(/option[@here = "now"])] nous recherchons un options le nœud qui a pas option l'enfant qui a un here="now" attribut
[1] le premier nœud

Si vous avez besoin de modifier plusieurs nœuds au sein d'un seul document XML, vous devez exécuter ce en boucle

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;

Vous pouvez également le faire pour l'ensemble de la table

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;

Pour de très grands ensembles de données, il peut être plus rapide pour reconstruire l'ensemble du XML à l'aide de XQuery, avec le nœud supplémentaire ajouté Construit à l'aide de XML.

db<>violon

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

Vous pouvez utiliser XML DML (modification de données) avec le .modify fonction pour modifier le 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]');

Cela fonctionne comme suit:

insert <option here="now" /> c'est la valeur que nous sommes en insertion
as last into il va après les autres nœuds enfants de la sélection d'un
/root/level1/level2/level3/level4/level5/level6 cela nous permet de nous que level6 nœud
[substring(@here, 1, 15) = "now is the time"] les prédicats le nœud d'avoir une here attribut à partir de cette valeur. Vous devez modifier le paramètre de durée pour correspondre à la valeur de la comparaison. Il n'y a pas de LIKE en XQuery
/options [not(/option[@here = "now"])] nous recherchons un options le nœud qui a pas option l'enfant qui a un here="now" attribut
[1] le premier nœud

Si vous avez besoin de modifier plusieurs nœuds au sein d'un seul document XML, vous devez exécuter ce en boucle

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;

Vous pouvez également le faire pour l'ensemble de la table

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;

Pour de très grands ensembles de données, il peut être plus rapide pour reconstruire l'ensemble du XML à l'aide de XQuery, avec le nœud supplémentaire ajouté Construit à l'aide de XML.

db<>violon

Comment puis-je modifier dynamiquement la structure XML d'une chaîne SQL

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie