Come posso modificare dinamicamente la struttura XML di una stringa in SQL

Question 1

Ho bisogno di uno script SQL per estrarre una stringa XML dal DB [varchar(max)], controllare e aggiornare se si inserisce una specifica situazione.

Immaginate che il mio xml è il seguente formato:

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

Così, quello che voglio fare è aggiornare tutti gli elementi il cui nome è "level6" e che hanno un attributo chiamato "qui", il cui valore inizia con "ora è il momento". Quindi, che dovrebbe corrispondere a due soli elementi di cui sopra.

Ma, non è l'unico criterio di selezione. L'elenco delle opzioni non devono contenere <option here="now" />. Quindi, che dovrebbe lasciare con un solo elemento di aggiornamento.

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

A tale elemento, quindi, vorrei aggiungere i mancanti <option here="now" />, in modo che diventi:

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

Così, il risultato finale dovrebbe essere:

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

Si supponga che sono in grado di leggere i dati dal DB in una stringa, e che io so come aggiornare il DB, quindi è davvero come manipolare la stringa xml in SQL (SQL Server).

Question 2

È possibile utilizzare XML DML (data di modifica) con il .modify funzione per modificare l'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]');

Questo funziona come segue:

insert <option here="now" /> questo è il valore che si sta inserendo
as last into va dopo altri nodi figlio di quella selezionata
/root/level1/level2/level3/level4/level5/level6 questo ci ottiene che level6 nodo
[substring(@here, 1, 15) = "now is the time"] predicati il nodo di avere un here attributo a partire da tale valore. È necessario modificare il parametro della lunghezza di corrispondere il valore di confronto. Non c'è LIKE in XQuery
/options [not(/option[@here = "now"])] ci guardiamo per un options il nodo che ha non option bambino che a sua volta ha un here="now" attributo
[1] il primo nodo

Se è necessario modificare più nodi all'interno di un singolo documento XML, è necessario eseguire questa operazione in loop

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;

È anche possibile fare questo per tutto il tavolo

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;

Per molto grandi set di dati potrebbe essere più veloce per ricostruire l'intero XML utilizzando XQuery, con il nodo aggiuntivo aggiunto utilizzando XML Costruiti.

db<>violino

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

È possibile utilizzare XML DML (data di modifica) con il .modify funzione per modificare l'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]');

Questo funziona come segue:

insert <option here="now" /> questo è il valore che si sta inserendo
as last into va dopo altri nodi figlio di quella selezionata
/root/level1/level2/level3/level4/level5/level6 questo ci ottiene che level6 nodo
[substring(@here, 1, 15) = "now is the time"] predicati il nodo di avere un here attributo a partire da tale valore. È necessario modificare il parametro della lunghezza di corrispondere il valore di confronto. Non c'è LIKE in XQuery
/options [not(/option[@here = "now"])] ci guardiamo per un options il nodo che ha non option bambino che a sua volta ha un here="now" attributo
[1] il primo nodo

Se è necessario modificare più nodi all'interno di un singolo documento XML, è necessario eseguire questa operazione in loop

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;

È anche possibile fare questo per tutto il tavolo

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;

Per molto grandi set di dati potrebbe essere più veloce per ricostruire l'intero XML utilizzando XQuery, con il nodo aggiuntivo aggiunto utilizzando XML Costruiti.

db<>violino

Come posso modificare dinamicamente la struttura XML di una stringa in SQL

Domanda

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria