1. Página inicial
  2. Pergunta

Como posso alterar dinamicamente a estrutura XML de uma seqüência de caracteres no SQL

0

Pergunta

Eu preciso de um script SQL que irá puxar uma cadeia de caracteres XML do DB [varchar(max)], inspecioná-lo e atualizá-lo se ele se adapta a uma situação específica.

Imagine que o meu xml com o seguinte 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>

Então, o que eu quero fazer é atualizar todos os elementos cujo nome é "level6" e que tem um atributo chamado "aqui", cujo valor começa com "agora é o tempo". Então, o que deve corresponder a apenas dois elementos acima.

Mas, esse não é o único critério de seleção. A lista de opções não deve conter <option here="now" />. Então, o que deve deixar-nos com apenas um elemento para atualização.

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

A esse elemento, então eu quero adicione a falta <option here="now" />, de modo que torna-se:

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

Assim, o resultado final deve 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>

Suponha que eu possa ler os dados do DB em uma seqüência de caracteres, e que eu sei como atualizar o banco de dados, por isso é realmente como manipular a string xml no SQL (SQL Server).

sql-server tsql xml xquery
2021-11-23 17:17:51
1

Melhor resposta

1

Você pode usar XML DML (data de modificação) com o .modify função para alterar o 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]');

Este funciona da seguinte maneira:

  • insert <option here="now" /> este é o valor que estamos inserindo
  • as last into ele vai atrás de outra criança, nós do selecionado um
  • /root/level1/level2/level3/level4/level5/level6 isto leva-nos que level6 nó
  • [substring(@here, 1, 15) = "now is the time"] predicados o nó de ter um here atributo começando com esse valor. Você deve modificar o parâmetro de comprimento para coincidir com o valor que você está comparando. Não há LIKE em XQuery
  • /options [not(/option[@here = "now"])] nós olhamos para uma options nó que tem não option filho, que por sua vez tem um here="now" atributo
  • [1] o primeiro nó

Se você precisar modificar vários nós dentro de um único documento XML, você precisa executar este em 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;

Você também pode fazer isso para uma tabela inteira

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 grandes conjuntos de dados, pode ser mais rápido para reconstruir o XML completo usando XQuery, com o nó extra adicionado Construído usando XML.

db<>violino

2021-11-23 23:41:04

Em outros idiomas

Esta página está em outros idiomas

Русский
..................................................................................................................
Ссылка
Italiano
..................................................................................................................
Link
Polski
..................................................................................................................
Link
Română
..................................................................................................................
Legătură
한국어
..................................................................................................................
링크
हिन्दी
..................................................................................................................
लिंक
Français
..................................................................................................................
Lien
Türk
..................................................................................................................
Atıf
Česk
..................................................................................................................
Odka
ไทย
..................................................................................................................
เชื่อมโยง
中文
..................................................................................................................
链接
Español
..................................................................................................................
Referencia
Slovenský
..................................................................................................................
Odkaz

Popular nesta categoria

Perguntas populares nesta categoria

Concat coluna de uma tabela para cada coluna de outra tabela
Compare as duas linhas (ambos com ID diferente) & verifique se os seus valores de coluna são exatamente os mesmos. Todas as linhas e colunas da mesma tabela
Junte-se a retornar várias colunas e em grupo pelo Entity Framework
Mais de 50 categorias diferentes que compartilham 50% dos atributos. Como criar todas essas tabelas de forma eficiente em banco de dados sql? [fechado]
Agendar o pacote do SSIS para executar uma agenda
Pesquisar várias palavras em uma seqüência de caracteres no SQL Server
Dinâmica ao alterar tipos de dados (dinamicamente)
SE EXISTE e Instrução MERGE
Injetar RDS segredos para Python no Lambda
Como calcular o total em execução no sql para trás