¿Cómo puedo cambiar dinámicamente la estructura XML de una cadena en SQL

Question 1

Necesito un script SQL que se va a tirar de una cadena XML de la base de datos [varchar(max)], inspeccionar, y actualizarla si se ajusta a una situación específica.

Imagine que mi xml está en el siguiente 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>

Así, lo que quiero hacer es actualizar todos los elementos cuyo nombre es "level6" y que tienen un atributo llamado "aquí", cuyo valor comienza con "ahora es el momento". Así, que debe coincidir con sólo dos elementos anteriores.

Pero, ese no es el único criterio de selección. La lista de opciones no debe contener <option here="now" />. Así, que debe salir de nosotros con sólo uno de los elementos para la actualización.

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

A ese elemento, a continuación, deseo agregar la falta <option here="now" />, por lo que se convierte en:

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

Así, el resultado final debe ser:

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

Suponga que puedo leer los datos de la DB en una cadena, y que sé cómo actualizar la base de datos, por lo que es realmente la manera de manipular la cadena xml en SQL (SQL Server).

Question 2

Puede utilizar XML DML (modificación de datos) con el .modify función para cambiar el 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]');

Esto funciona de la siguiente manera:

insert <option here="now" /> este es el valor que se va a insertar
as last into se va después de otros nodos secundarios de la seleccionada
/root/level1/level2/level3/level4/level5/level6 esto nos lleva a que level6 nodo
[substring(@here, 1, 15) = "now is the time"] los predicados del nodo a tiene un here atributo de partida con ese valor. Debe modificar el parámetro de longitud para que coincida con el valor que se desea comparar. No hay LIKE en XQuery
/options [not(/option[@here = "now"])] buscamos una options nodo que tiene no option niño, que a su vez tiene una here="now" atributo
[1] el primero de esos nodo

Si necesita modificar varios nodos dentro de un único documento XML, usted necesita para ejecutar este en bucle

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;

También puede hacer esto para toda la tabla

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;

Para conjuntos de datos muy grandes puede ser más rápido para reconstruir el XML completo el uso de XQuery, con el nodo extra añadido usando XML Construido.

db<>violín

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

Puede utilizar XML DML (modificación de datos) con el .modify función para cambiar el 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]');

Esto funciona de la siguiente manera:

insert <option here="now" /> este es el valor que se va a insertar
as last into se va después de otros nodos secundarios de la seleccionada
/root/level1/level2/level3/level4/level5/level6 esto nos lleva a que level6 nodo
[substring(@here, 1, 15) = "now is the time"] los predicados del nodo a tiene un here atributo de partida con ese valor. Debe modificar el parámetro de longitud para que coincida con el valor que se desea comparar. No hay LIKE en XQuery
/options [not(/option[@here = "now"])] buscamos una options nodo que tiene no option niño, que a su vez tiene una here="now" atributo
[1] el primero de esos nodo

Si necesita modificar varios nodos dentro de un único documento XML, usted necesita para ejecutar este en bucle

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;

También puede hacer esto para toda la tabla

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;

Para conjuntos de datos muy grandes puede ser más rápido para reconstruir el XML completo el uso de XQuery, con el nodo extra añadido usando XML Construido.

db<>violín

¿Cómo puedo cambiar dinámicamente la estructura XML de una cadena en SQL

Pregunta

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría