Help with creating xml shema

can somebody, please help me with my problem. I want to create xml shema to
use it with bulkload in vb.net application. I read about it and I must have
xsd, and xml file. So, if anybody can create for me that files, I would be
very thankful. These are my example tables.
T1
ID name city
1 john NY
2 mark NY
3 eric LA
T2
ID car price color
1 mazda 10000 black
1 honda 12000 blue
2 toyota 9000 green
3 audi 4000 black
1 ford 7800 red
please, please...if someone can help me.
thanks!
What does your XML look like?
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bernard" <bernard@.form.hr> wrote in message
news:clm1td$hbv$1@.ls219.htnet.hr...
> can somebody, please help me with my problem. I want to create xml shema
> to use it with bulkload in vb.net application. I read about it and I must
> have xsd, and xml file. So, if anybody can create for me that files, I
> would be very thankful. These are my example tables.
> T1
> --
> ID name city
> 1 john NY
> 2 mark NY
> 3 eric LA
> --
> T2
> --
> ID car price color
> 1 mazda 10000 black
> 1 honda 12000 blue
> 2 toyota 9000 green
> 3 audi 4000 black
> 1 ford 7800 red
> --
> please, please...if someone can help me.
> thanks!
>
|||Bernard:
It looks like you already have data in those tables, so the easy thing to might to be issue a query on them like this
select * from t1 for xml auto
Then save those results out to file. You'll have to edit out the extra line feeds and add the XML prolog to make that a valid XML file.
Once you have that, and assuming you have the .NET SDK install, you should be able to use XSD.EXE to generate usable schmea.
If you already have the XML files you want to import, you can just feed them to XSD.EXE one at a time to get a matching W3C schema.
> Irwon: What does your XML look like?
If I'm reading this right, he doesn't have any.
Thanks,
Kent Tegels
MVP - SQL Server
The SSX FAQ & Blog:
http://tinyurl.com/6r4gb
Looking for XM, the GUI for SSX? See both:
http://tinyurl.com/4dfee and http://tinyurl.com/53hts
My Blog:
http://www.tegels.org/

Help using sp_xml_preparedocument

Hi all,

We are using a VB component to create and save an XML document on our
local LAN.

We want to load this straight into a DB Table using a query based on
OPENXML. Trouble is, the sp_xml_preparedocument command always throws an
error.

Here is some code:

>>>> CODE >>>>
DECLARE @.idoc int
DECLARE @.doc varchar(1000)

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
<<<< END OF CODE <<<<

where @.doc is the filename of our file on the LAN to be opened, and
@.idoc is the handle to be created.

the error is:
XML parsing error: Invalid at the top level of the document.

Here is some of the XML Document saved on the LAN that should be opened
by sp_xml_preparedocument and prepared.

<?xml version="1.0" encoding="UTF-8" ?>
<xdata created_by="OSA" created_at="Thu Nov 13 11:33:33 EST 2003">
<rows row_count="1000">
<row>
<COL1>714761905</COL1>
<COL2>714761905</COL2>
<COL3>714761905</COL3>
</row>
</xdata
Any advice or tips whould be very helpful...

thanks

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!The xml doc you posted is not well-formed; it is mussing the </rows> end
tag. In any case, the @.doc parameter specifies the actual xml text, not
the path a file containing xml.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"no spam" <anonymous@.devdex.com> wrote in message
news:3fb4f782$0$198$75868355@.news.frii.net...
> Hi all,
> We are using a VB component to create and save an XML document on our
> local LAN.
> We want to load this straight into a DB Table using a query based on
> OPENXML. Trouble is, the sp_xml_preparedocument command always throws
an
> error.
> Here is some code:
> >>>> CODE >>>>
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> <<<< END OF CODE <<<<
> where @.doc is the filename of our file on the LAN to be opened, and
> @.idoc is the handle to be created.
> the error is:
> XML parsing error: Invalid at the top level of the document.
> Here is some of the XML Document saved on the LAN that should be
opened
> by sp_xml_preparedocument and prepared.
> <?xml version="1.0" encoding="UTF-8" ?>
> <xdata created_by="OSA" created_at="Thu Nov 13 11:33:33 EST 2003">
> <rows row_count="1000">
> <row>
> <COL1>714761905</COL1>
> <COL2>714761905</COL2>
> <COL3>714761905</COL3>
> </row>
> </xdata>
> Any advice or tips whould be very helpful...
> thanks
> Phil
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

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

HELP Retrieving remote XML and returning ROWSET

Within a stored procedure, I need to retrieve XML over HTTP from an external
URL, then load some stuff into a recordset and return as such.
Like this, sort-of:
-- *******************************
create procudure queryremote (@.param)
declare @.xmldoc varchar(2000)
set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
@.param)
sp_preparedocument (@.xmldoc etc.)
SELECT * FROM OPENXML(thexmlthing, "/xpath")
Return
-- *********************************
Just to be clear, this is exactly the opposite of what everyone is trying to
do; many articles on getting XML OUT of SQL server and putting XML data INTO
SQL server, over HTTP.
This requirement is to provide backward -compatibility to
SQL-recordset-aware-only applications. Making an HTTP XML query service
LOOK like an SQL recordset.
Email and post please, thanks a lot!
Richard Weerts
rweerts@.ndis.us
In SQL Server 2000 you have two options:
Do the get on the mid-tier and pass the XML as an argument to the stored
proc.
Write your own extended stored proc to GET the data.
In SQL Server 2005, you can write the function using CLR instead of writing
an extended stored proc.
HTH
Michael
"411XML Richard Weerts" <rweerts@.ndis.us> wrote in message
news:OLr5YmXfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Within a stored procedure, I need to retrieve XML over HTTP from an
> external
> URL, then load some stuff into a recordset and return as such.
> Like this, sort-of:
> -- *******************************
> create procudure queryremote (@.param)
> declare @.xmldoc varchar(2000)
> set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
> @.param)
> sp_preparedocument (@.xmldoc etc.)
> SELECT * FROM OPENXML(thexmlthing, "/xpath")
> Return
> -- *********************************
> Just to be clear, this is exactly the opposite of what everyone is trying
> to
> do; many articles on getting XML OUT of SQL server and putting XML data
> INTO
> SQL server, over HTTP.
> This requirement is to provide backward -compatibility to
> SQL-recordset-aware-only applications. Making an HTTP XML query service
> LOOK like an SQL recordset.
> Email and post please, thanks a lot!
> Richard Weerts
> rweerts@.ndis.us
>
>

Help relation Between Xml and SQL2000

HI,
I have a xml file and would like to Make Relation Between
it and table Or View in SQl2000
can some one say how?
Thanks
You should look into using either OpenXML or the annotated schema. Check out
the SQL Server Books Online section on XML or
http://msdn.microsoft.com/sqlxml
Best regards
Michael
"Abdallah Salah" <a_elsabbahi2000@.yahoo.com> wrote in message
news:a8cb01c487d8$c1801960$a401280a@.phx.gbl...
> HI,
> I have a xml file and would like to Make Relation Between
> it and table Or View in SQl2000
> can some one say how?
>
> Thanks
>