Microsoft SQL Server 2008 XML editten in records

[Jannibal23]

Ik loop nu al een tijdje te klooien maar kom er niet uit, vandaar mijn vraag aan jullie.

Ik heb een MS SQL database met daarin een tabel met een duizendtal records. Elk record bestaat uit een kolom met een id en een kolom met XML data.

Een willekeurig record in de kolom met XML data zou er zo uit kunnen zien:

code:

<record id="1">
   <field tag="aa" occ="1" lang="nl-NL" invariant="false">Jan</field>
   <field tag="aa" occ="1" lang="en-US" invariant="false">John</field>
   <field tag="aa" occ="1" lang="de-DE" invariant="false">der Jan</field>
   <field tag="aa" occ="2" lang="nl-NL" invariant="false">Jan2</field>
   <field tag="aa" occ="2" lang="en-US" invariant="false">John2</field>
   <field tag="ab" occ="1">Blabla</field>
   <field tag="ac" occ="1" lang="de-DE" invariant="false">Rechnung</field>
   <field tag="ac" occ="1" lang="nl-NL" invariant="false">rekening</field>
   <field tag="ad" occ="1">Vulling</field>
   <field tag="ae" occ="1" lang="nl-NL" invariant="false">stoeptegel</field>
</record>

Nu wil ik graag met een SQL query de XML aanpassen voor het attribuut @invariant, en wel volgens de volgende regels:
- Per unieke occ (occurence), tag combinatie kan er maar 1x @invariant=true voorkomen
- Indien een <field> gevuld is met @lang=en-US moet @invariant=true worden, eventuele overige velden met dezelfe tag, occ combinatie moeten op @invariant=false blijven staan. (Zie tag=aa in XML code)
- Indien een <field> gevuld is met @lang=nl-NL, en geen @lang=en-US bevat, moet @invariant=true worden, eventuele overige velden met dezelfe tag en occ moeten op @invariant=false blijven staan. (Zie tag=ac in XML code)
- Indien een tag en occ combinatie maar 1 @lang attribuut bevat moet @invariant=true worden. (Zie tag=ae in XML code). Dus onafhankelijk van de waarde van @lang.

Na uitvoering van 1 (of meerdere) SQL query zou de XML er dan zou uit moeten komen zien:

code:

<record id="1">
   <field tag="aa" occ="1" lang="nl-NL" invariant="false">Jan</field>
   <field tag="aa" occ="1" lang="en-US" invariant="true">John</field>
   <field tag="aa" occ="1" lang="de-DE" invariant="false">der Jan</field>
   <field tag="aa" occ="2" lang="nl-NL" invariant="false">Jan2</field>
   <field tag="aa" occ="2" lang="en-US" invariant="true">John2</field>
   <field tag="ab" occ="1">Blabla</field>
   <field tag="ac" occ="1" lang="de-DE" invariant="false">Rechnung</field>
   <field tag="ac" occ="1" lang="nl-NL" invariant="true">rekening</field>
   <field tag="ad" occ="1">Vulling</field>
   <field tag="ae" occ="1" lang="nl-NL" invariant="true">stoeptegel</field>
</record>

Aangezien ik niet zo'n query held ben loop ik hier al een tijdje mee te klooien.

Waar ik tegen aanloop is de toepassing van de regels en dat alles binnen een loop, aangezien je niet meerdere nodes tegelijkertijd kan bewerken met replace-value-of.

Mijn probeersel, zonder regels...

code:

update databasetabel
set xmlkolom.modify
(
'
replace value of
(/record/field/@invariant=false) with "true"
'
)

Jullie hulp zou ik zeer waarderen!

[Meekoh]

Is het nou MS SQL server of MySQL?
Dat is nogal belangrijk vanwege query opbouw die verschilt.

[Jannibal23]

MS SQL. Excuses.

Mijn probeersel is sowieso flauwekul kom ik achter.

Zonder op de regels te letten zou ik het eerste @invariant attribuut van een record als volgt kunnen wijzigen:

code:

update databasetabel
set xmlkolom.modify
('replace value of (/record/field/@invariant[.="false"])[1] with "true"')
where id = 1

Maar ja, nu de overige @invariant attributen binnen dit record en vervolgens de duizend andere records. En dat alles volgens bovenstaande regels...

Edit:
Iets verder nu. Onderstaande query past volgens mij voor alle records de waarde "false" in "true" aan. Nog niet helemaal goed, nu nog regels toepassen...

code:

while exists 
(
select * 
from databasetabel 
where xmlkolom.exist('/record/field/@invariant[.="false"]') = 1
)
update databasetabel
set xmlkolom.modify
('replace value of (/record/field/@invariant[.="false"])[1] with "true"')

[ Voor 142% gewijzigd door Jannibal23 op 18-01-2012 16:38 ]

[Jannibal23]

Iemand een idee?

[Jannibal23]

Voor de geinteresseerde: onderstaande query doet wat ik wil. Topic mag wat mij betreft dicht.

code:

declare @Tmp table
(
  ID int, -- Primary key in databasetable
  RowNumber int,
  Tag varchar(2),
  Occ int,
  Lang varchar(5),
  Invariant bit
  primary key (ID, RowNumber)
);

with C1 as
(
  select T.ID, -- Primary key in databasetable
         R.F.value('@tag', 'varchar(2)') as Tag,
         R.F.value('@occ', 'int') as Occ,
         R.F.value('@lang', 'varchar(5)') as Lang
  from databasetable as T
    cross apply T.xmlcolumn.nodes('/record/field') as R(F)
), 
C2 as
(
  select ID, Tag, Occ, Lang,
         row_number() over(partition by ID order by (select 0)) as RowNumber,
         row_number() over(partition by ID, Tag, Occ 
                           order by case Lang 
                                      when 'en-US' then 1
                                      when 'nl-NL' then 2
                                      else 3
                                    end) as rnInv
  from C1
)
insert into @Tmp (ID, RowNumber, Tag, Occ, Lang, Invariant)
select ID, RowNumber, Tag, Occ, Lang, case rnInv when 1 then 1 else 0 end
from C2;

declare @MaxRowNum int;
declare @I int = 1;

select @MaxRowNum = max(RowNumber)
from @Tmp;

while @I <= @MaxRowNum
begin
  update T
  set xmlcolumn.modify('replace value of (/record/field[@tag = sql:column("Tmp.Tag") and
                                                        @occ = sql:column("Tmp.Occ") and
                                                        @lang = sql:column("Tmp.Lang")]/@invariant)[1] 
                          with sql:column("Tmp.Invariant")')
  from databasetable as T
    inner join @Tmp as Tmp
      on T.ID = Tmp.ID
  where Tmp.RowNumber = @I;

  set @I += 1;
end