Ive been trying to find out how to write a query like this for months now and feel its time that I get some help :eek:
Im trying to export columns to text files so that they can be accessed via a website to show statistics.
(My SQL database is used for something else and I do not want the website directly connecting to it.)
So first I would have the table ordered by a specific column and then export the top 50 results for example.
I had it working to export to excell but I lost the query :(
Do I use something like EXPORT COLUMNS or INSERT INTO text file sorta thing
ThanksHi Paul
irrespective of the purpose of the db I would have the website access it directly. Why are you unhappy with this?
In any event BCP is an efficient way to get data out of the db and into text files.|||I dont want to lean on the SQL performance and im sure constant connections like this would.
I have never used BCP before so I guess I will go and have a look into that
thanks|||Still no luck BCP is very confusing :S|||is it? it's just a console app. here's how you can export data for an entire table:
bcp MyDatabase.dbo.MyTable out myfile.txt -c -T -SMYSERVER
and here's how to export the result of a query:
bcp "select foo from MyDatabase.dbo.MyTable where bar=12" out myfile.txt -c -T -SMYSERVER
Showing posts with label export. Show all posts
Showing posts with label export. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
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.
>
>
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.
>
>
Sunday, February 19, 2012
Help regarding sorting report
I have created als.rdlc file for my report it has data from the tableLS ( eg. Month , year, target, actual, etc ), and i have export thatLS.rdlc file in myreport.aspx by using ReportViewer from the toolbox. So when i complie that report.aspx it pull up the report. It pulls up all the data from LS but what i want is sort them by YEAR then MONTH, I Dont know how to do it.? Can any one help me???
Try adding an ORDER BY to the SELECT. If that does not work, change to using a stored procedure.|||I dont know how would you sort it? when i run the report it pulls up the whole database table , so how would i put a search in that?? I am not familer with sorting much.|||Just change
SELECT A, B, C FROM TABLE
to
SELECT A, B, C FROM TABLE ORDER BY ID
Subscribe to:
Posts (Atom)