Cum pot schimba dinamic structura XML a unui șir de caractere în SQL

Question 1

Am nevoie de un script SQL care vor să tragă un XML string din DB [varchar(max)], inspecta, și o actualizează , dacă se potrivește o anumită situație.

Imaginați-vă că-mi xml este în următorul format:

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

Deci, ceea ce vreau să fac este de a actualiza toate elementele al căror nume este "level6" și care au un atribut numit "aici" a cărui valoare începe cu "acum este momentul". Deci, care ar trebui să se potrivească doar două elemente de mai sus.

Dar, asta nu e numai criteriile de selecție. Lista de opțiuni nu trebuie să conțină <option here="now" />. Deci, care ar trebui să ne lase doar cu un singur element pentru a actualiza.

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

La acel element, apoi m-am doriți să adăugați lipsește <option here="now" />, astfel încât devine:

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

Deci, rezultatul final ar trebui să fie:

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

Să presupunem că eu pot citi datele din DB într-un șir, și care știu cum să actualizați DB, deci nu e chiar cum să manipuleze xml string SQL (SQL Server).

Question 2

Puteți utiliza XML DML (de modificare a datelor), cu .modify funcție pentru a modifica 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]');

Acesta funcționează după cum urmează:

insert <option here="now" /> aceasta este valoarea pe care o introduceți
as last into se merge după alte noduri copil de cel selectat
/root/level1/level2/level3/level4/level5/level6 acest lucru ne duce asta level6 nod
[substring(@here, 1, 15) = "now is the time"] predicate nod să aibă un here atribut începând cu acea valoare. Trebuie să modificați lungimea parametru pentru a se potrivi valoarea comparați. Nu există nici o LIKE în XQuery
/options [not(/option[@here = "now"])] ne uităm pentru un options nodul care nu are nici option copilul care la rândul său are un here="now" atribut
[1] primul astfel de nod

Dacă aveți nevoie să modificați mai multe noduri într-un singur document XML, aveți nevoie pentru a rula acest lucru în buclă

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;

De asemenea, puteți face acest lucru pentru o masă întreagă

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;

Pentru seturi mari de date poate fi mai rapid pentru a reconstrui întregul XML folosind XQuery, cu extra nod adăugat Construite folosind XML.

db<>vioara

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

Puteți utiliza XML DML (de modificare a datelor), cu .modify funcție pentru a modifica 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]');

Acesta funcționează după cum urmează:

insert <option here="now" /> aceasta este valoarea pe care o introduceți
as last into se merge după alte noduri copil de cel selectat
/root/level1/level2/level3/level4/level5/level6 acest lucru ne duce asta level6 nod
[substring(@here, 1, 15) = "now is the time"] predicate nod să aibă un here atribut începând cu acea valoare. Trebuie să modificați lungimea parametru pentru a se potrivi valoarea comparați. Nu există nici o LIKE în XQuery
/options [not(/option[@here = "now"])] ne uităm pentru un options nodul care nu are nici option copilul care la rândul său are un here="now" atribut
[1] primul astfel de nod

Dacă aveți nevoie să modificați mai multe noduri într-un singur document XML, aveți nevoie pentru a rula acest lucru în buclă

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;

De asemenea, puteți face acest lucru pentru o masă întreagă

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;

Pentru seturi mari de date poate fi mai rapid pentru a reconstrui întregul XML folosind XQuery, cu extra nod adăugat Construite folosind XML.

db<>vioara

Cum pot schimba dinamic structura XML a unui șir de caractere în SQL

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie