Help to export in XML with Explicit option

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=E9c Math 8
1 Moi 2 D=E9c Geo 6
1 Moi 2 F=E9v Math 3
1 Moi 2 F=E9v Geo 5
2 Toi 3 D=E9c Math 7
2 Toi 3 D=E9c Geo 2
2 Toi 3 F=E9v Math 4
2 Toi 3 F=E9v Geo 7
..=2E...........
I would like to have :
<export>
<Eleve>
<id>1</id>
<Nom>Moi</Nom>
<Cat>2</Cat>
<Mois>
<Nom_Mois>D=E9c<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=E9v<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=E9c<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=E9v<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.
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'Dc',N'Math', 8)
insert into T values(1,N'Moi',2,N'Dc',N'Geo', 6)
insert into T values(1,N'Moi',2,N'Fv',N'Math', 3)
insert into T values(1,N'Moi',2,N'Fv',N'Geo', 5)
insert into T values(2,N'Toi',3,N'Dc',N'Math', 7)
insert into T values(2,N'Toi',3,N'Dc',N'Geo', 2)
insert into T values(2,N'Toi',3,N'Fv',N'Math', 4)
insert into T values(2,N'Toi',3,N'Fv',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')
<not4u@.chez.com> wrote in message
news:1102671847.545376.255240@.z14g2000cwz.googlegr oups.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 Dc Math 8
1 Moi 2 Dc Geo 6
1 Moi 2 Fv Math 3
1 Moi 2 Fv Geo 5
2 Toi 3 Dc Math 7
2 Toi 3 Dc Geo 2
2 Toi 3 Fv Math 4
2 Toi 3 Fv Geo 7
.............
I would like to have :
<export>
<Eleve>
<id>1</id>
<Nom>Moi</Nom>
<Cat>2</Cat>
<Mois>
<Nom_Mois>Dc<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>Fv<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>Dc<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>Fv<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.
|||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'Dc',N'Math', 8)
insert into T values(1,N'Moi',2,N'Dc',N'Geo', 6)
insert into T values(1,N'Moi',2,N'Fv',N'Math', 3)
insert into T values(1,N'Moi',2,N'Fv',N'Geo', 5)
insert into T values(2,N'Toi',3,N'Dc',N'Math', 7)
insert into T values(2,N'Toi',3,N'Dc',N'Geo', 2)
insert into T values(2,N'Toi',3,N'Fv',N'Math', 4)
insert into T values(2,N'Toi',3,N'Fv',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')
<not4u@.chez.com> wrote in message
news:1102671847.545376.255240@.z14g2000cwz.googlegr oups.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 Dc Math 8
1 Moi 2 Dc Geo 6
1 Moi 2 Fv Math 3
1 Moi 2 Fv Geo 5
2 Toi 3 Dc Math 7
2 Toi 3 Dc Geo 2
2 Toi 3 Fv Math 4
2 Toi 3 Fv Geo 7
.............
I would like to have :
<export>
<Eleve>
<id>1</id>
<Nom>Moi</Nom>
<Cat>2</Cat>
<Mois>
<Nom_Mois>Dc<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>Fv<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>Dc<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>Fv<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.
|||Thank a lot for your help.
Happy new Year in peace
Michael Rys [MSFT] wrote:
> 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'Dc',N'Math', 8)
> insert into T values(1,N'Moi',2,N'Dc',N'Geo', 6)
> insert into T values(1,N'Moi',2,N'Fv',N'Math', 3)
> insert into T values(1,N'Moi',2,N'Fv',N'Geo', 5)
> insert into T values(2,N'Toi',3,N'Dc',N'Math', 7)
> insert into T values(2,N'Toi',3,N'Dc',N'Geo', 2)
> insert into T values(2,N'Toi',3,N'Fv',N'Math', 4)
> insert into T values(2,N'Toi',3,N'Fv',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')
> <not4u@.chez.com> wrote in message
> news:1102671847.545376.255240@.z14g2000cwz.googlegr oups.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 Dc Math 8
> 1 Moi 2 Dc Geo 6
> 1 Moi 2 Fv Math 3
> 1 Moi 2 Fv Geo 5
> 2 Toi 3 Dc Math 7
> 2 Toi 3 Dc Geo 2
> 2 Toi 3 Fv Math 4
> 2 Toi 3 Fv Geo 7
> ............
> I would like to have :
>
> <export>
> <Eleve>
>
> <id>1</id>
> <Nom>Moi</Nom>
> <Cat>2</Cat>
> <Mois>
> <Nom_Mois>Dc<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>Fv<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>Dc<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>Fv<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.
>
>