> Since your table is highly denormalized, we basically have to see it as
> three tables that you join to get the nesting
>
> Eleve -> Mois -> Matiere
>
> You then want to also wrap it with a wrapper element.
>
> Without the wrapper element, you could use FOR XML auto as follows (and add
> the wrapper element using the ADO root node property on the mid-tier):
>
> select Eleve.Id as "id", Eleve.Nom, Eleve.Cat, Mois.Mois as "Nom_Mois",
>
> Matiere.Nom_ex, Matiere.Note
>
> from (select distinct Id, Nom, Cat from T) as Eleve
>
> left outer join
>
> (select distinct Id, Nom, Cat, Mois from T) as Mois
>
> on Eleve.Id=Mois.Id
>
> left outer join
>
> T as Matiere
>
> on Mois.Id=Matiere.Id and Mois.Mois=Matiere.Mois
>
> order by Eleve.Id, Mois.Mois
>
> for xml auto, elements
>
>  This works, because you are fully element-centric and you have no so-called
> sibling type hierarchies.
>
> If you want to get the export wrapper done explicitly in SQL Server 2000,
> you have to use the following EXPLICIT mode query. Also note that if you
> want sibling types or attribute and elements mixed, you will need the
> EXPLICIT mode query:
>
> select Id, Nom, Cat,
>
> (select Mois as "Nom_Mois",
>
> (select Nom_ex, Note
>
> from T as T3
>
> where T2.Id=T3.Id and T2.Mois=T3.Mois
>
> for xml path('Matiere'), type)
>
> from (select distinct Id, Nom, Cat, Mois from T) as T2
>
> where T1.Id=T2.Id
>
> order by Mois
>
> for xml path('Mois'), type)
>
> from (select distinct Id, Nom, Cat from T) as T1
>
> order by Id
>
> for xml path('Eleve'), root('export')
>
> select 1 as tag, 0 as parent,
>
> 1 as "export!1!root!hide",
>
> NULL as "Eleve!2!id!element",
>
> NULL as "Eleve!2!Nom!element",
>
> NULL as "Eleve!2!Cat!element",
>
> NULL as "Mois!3!Nom_Mois!element",
>
> NULL as "Matiere!4!Nom_ex!element",
>
> NULL as "Matiere!4!Note!element"
>
> union all
>
> select distinct 2 as tag, 1 as parent,
>
> 1 as "export!1!root!hide",
>
> Id as "Eleve!2!id!element",
>
> Nom as "Eleve!2!Nom!element",
>
> Cat as "Eleve!2!Cat!element",
>
> NULL as "Mois!3!Nom_Mois!element",
>
> NULL as "Matiere!4!Nom_ex!element",
>
> NULL as "Matiere!4!Note!element"
>
> from T
>
> union all
>
> select distinct 3 as tag, 2 as parent,
>
> 1 as "export!1!root!hide",
>
> Id as "Eleve!2!id!element",
>
> NULL as "Eleve!2!Nom!element",
>
> NULL as "Eleve!2!Cat!element",
>
> Mois as "Mois!3!Nom_Mois!element",
>
> NULL as "Matiere!4!Nom_ex!element",
>
> NULL as "Matiere!4!Note!element"
>
> from T
>
> union all
>
> select distinct 4 as tag, 3 as parent,
>
> 1 as "export!1!root!hide",
>
> Id as "Eleve!2!id!element",
>
> NULL as "Eleve!2!Nom!element",
>
> NULL as "Eleve!2!Cat!element",
>
> Mois as "Mois!3!Nom_Mois!element",
>
> Nom_ex as "Matiere!4!Nom_ex!element",
>
> Note as "Matiere!4!Note!element"
>
> from T
>
> order by "export!1!root!hide", "Eleve!2!id!element",
> "Mois!3!Nom_Mois!element"
>
> for xml explicit
>
> Here is the table generation statement, in case you want to try the above
> queries out:
>
>
>
> create table T(Id int, Nom nvarchar(5), Cat int, Mois nvarchar(5), Nom_ex
> nvarchar(5), Note int)
>
> insert into T values(1,N'Moi',2,N'Déc',N'Math', 8)
>
> insert into T values(1,N'Moi',2,N'Déc',N'Geo', 6)
>
> insert into T values(1,N'Moi',2,N'Fév',N'Math', 3)
>
> insert into T values(1,N'Moi',2,N'Fév',N'Geo', 5)
>
> insert into T values(2,N'Toi',3,N'Déc',N'Math', 7)
>
> insert into T values(2,N'Toi',3,N'Déc',N'Geo', 2)
>
> insert into T values(2,N'Toi',3,N'Fév',N'Math', 4)
>
> insert into T values(2,N'Toi',3,N'Fév',N'Geo', 7)
>
>
>
> I hope this helps.
>
> Best regards
>
> Michael
>
>
>
> PS: In SQL Server 2005, the above expressions still work. You can also use
> the new path mode in the following way:
>
> select Id, Nom, Cat,
>
>    (select Mois as "Nom_Mois",
>
>       (select Nom_ex, Note
>
>        from T as T3
>
>        where T2.Id=T3.Id and T2.Mois=T3.Mois
>
>        for xml path('Matiere'), type)
>
>     from (select distinct Id, Nom, Cat, Mois from T) as T2
>
>     where T1.Id=T2.Id
>
>     order by Mois
>
>     for xml path('Mois'), type)
>
> from (select distinct Id, Nom, Cat from T) as T1
>
> order by Id
>
> for xml path('Eleve'), root('export')
>
> <no***@chez.com> wrote in message
> news:1102671847.545376.255240@z14g2000cwz.googlegroups.com...
> Hello,
>
>
> I have a table that i would like to export in XML
> The table looks like :
>
>
>
> Id | Nom | Cat| Mois | Nom_ex | Note
> 1   Moi    2   Déc     Math     8
> 1   Moi    2   Déc     Geo      6
> 1   Moi    2   Fév     Math     3
> 1   Moi    2   Fév     Geo      5
> 2   Toi    3   Déc     Math     7
> 2   Toi    3   Déc     Geo      2
> 2   Toi    3   Fév     Math     4
> 2   Toi    3   Fév     Geo      7
> ................
>
> I would like to have :
>
>
> <export>
> <Eleve>
>
>
> <id>1</id>
> <Nom>Moi</Nom>
> <Cat>2</Cat>
> <Mois>
>
> <Nom_Mois>Déc<Nom_Mois>
> <Matiere>
>
> <Nom_ex>Math</Nom_ex>
> <Note>8</Note>
> </Matiere>
> <Matiere>
>
> <Nom_ex>Geo</Nom_ex>
> <Note>6</Note>
> </Matiere>
> </Mois>
> <Mois>
>
> <Nom_Mois>Fév<Nom_Mois>
> <Matiere>
>
> <Nom_ex>Math</Nom_ex>
> <Note>3</Note>
> </Matiere>
> <Matiere>
>
> <Nom_ex>Geo</Nom_ex>
> <Note>5</Note>
> </Matiere>
> </Mois>
> </Eleve>
> <Eleve>
>
> <id>2</id>
> <Nom>Toi</Nom>
> <Cat>3</Cat>
> <Mois>
>
> <Nom_Mois>Déc<Nom_Mois>
> <Matiere>
>
> <Nom_ex>Math</Nom_ex>
> <Note>7</Note>
> </Matiere>
> <Matiere>
>
> <Nom_ex>Geo</Nom_ex>
> <Note>2</Note>
> </Matiere>
> </Mois>
> <Mois>
>
> <Nom_Mois>Fév<Nom_Mois>
> <Matiere>
>
> <Nom_ex>Math</Nom_ex>
> <Note>4</Note>
> </Matiere>
> <Matiere>
>
> <Nom_ex>Geo</Nom_ex>
> <Note>7</Note>
> </Matiere>
> </Mois>
> </Eleve>
> </export>
>
> I would like to get this result with a SQL SELECT with options "FOR XML
>
> AUTO or ELEMENTS or EXPLICIT" and not with to parse my data to rebuld
> the XML tree
> Thanks for your help.
>
>
>