Retrieve and write

Hello,
I'm trying to retrieve data from Sql Server 2000 in XML format and HTTP it
to a gateway. And write the response in XML from gateway back to SQL Server.
Can anyone please advice me which is the best way to do it?
Thanks
ReddYou have three options with SQL Server 2000:
1. Take a look at the SQLXML component (version 3.0 is available for free
download from msd.microsoft.com). This provides you with an ISAPI extension.
2. Write your own web service and use the SQLXML component and the database
functionality (FOR XML, OpenXML and stored procs) using either ASP or
ASP.Net.
3. Use one of the third party tools.
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:FD9F7B95-58E9-4955-B23B-3A8DC005512C@.microsoft.com...
> Hello,
> I'm trying to retrieve data from Sql Server 2000 in XML format and HTTP it
> to a gateway. And write the response in XML from gateway back to SQL
> Server.
> Can anyone please advice me which is the best way to do it?
> Thanks
> Redd|||Thanks Michael,
Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
couple more questions if you don't mind.
1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
another server.
2. When I get the XML response from the server where would the XML file
reside and how can I access it to write data back into my database.
I would be very greatful to you I you can help me out with this.
Thanks
Redd
"Michael Rys [MSFT]" wrote:

> You have three options with SQL Server 2000:
> 1. Take a look at the SQLXML component (version 3.0 is available for free
> download from msd.microsoft.com). This provides you with an ISAPI extensio
n.
> 2. Write your own web service and use the SQLXML component and the databas
e
> functionality (FOR XML, OpenXML and stored procs) using either ASP or
> ASP.Net.
> 3. Use one of the third party tools.
> Best regards
> Michael
> "Redd" <Redd@.discussions.microsoft.com> wrote in message
> news:FD9F7B95-58E9-4955-B23B-3A8DC005512C@.microsoft.com...
>
>|||And also could you please let me know how to add root elements(which are not
in my database) to XML output using stored procedure.
For example:
my XML output file looks like this when I retrieve data from SQLSERVER 2000
using XML AUTO, ELEMENTS
<GHI>
<JKL>
<MNO>
.....
.....
</MNO>
</JKL>
</GHI>
Now I want to add:
<?XML VERSION=.........?>
<ABC>
<DEF>
<GHI>
.......
.......
</GHI>
</DEF>
</ABC>
How can I add <ABC>, <DEF> elements for each <GHI> element
Any idea would help
Thanks
Redd
"Redd" wrote:
> Thanks Michael,
> Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
> couple more questions if you don't mind.
> 1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
> another server.
> 2. When I get the XML response from the server where would the XML file
> reside and how can I access it to write data back into my database.
> I would be very greatful to you I you can help me out with this.
> Thanks
> Redd
> "Michael Rys [MSFT]" wrote:
>|||The three options I give you below are the ways to "HTTP the XML to another
server". You need to make your database server accessible through HTTP by
using one of these 3 approaches (see their documentation for more details on
how to set up the ISAPI for example). Using the SQLXML 3.0 ISAPI and the
SQLXML templates is probably the approach that gives you the quickest way to
doing it.
Since HTTP is a stateless protocol that transport everything (well, in its
most basic form), the XML will be part of the response payload over HTTP
(similar to how you would get the HTML from a webserver). So you would get
the XML into your other application. You then do your changes and you will
have to communicate your changes back. Again, there are a couple of ways to
do it:
1. You can use the SQLXML 3.0 updategrams. This works if your model fits the
model of the updategrams.
2. You keep track of your changes and only send changes back against the
relational data.
3. You send the whole XML document back and use stored procs to perform the
change analysis (using OpenXML).
Again, you can expose this through an HTTP based service (e.g., a SQLXML
template or a stored proc).
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:E468C8D6-730E-4576-98F8-C9456CB904AF@.microsoft.com...
> Thanks Michael,
> Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
> couple more questions if you don't mind.
> 1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
> another server.
> 2. When I get the XML response from the server where would the XML file
> reside and how can I access it to write data back into my database.
> I would be very greatful to you I you can help me out with this.
> Thanks
> Redd
> "Michael Rys [MSFT]" wrote:
>|||Adding the XML declaration (<?xml ... ?> ) is not needed assuming the data
you are getting is in the right output encoding (which you can set on the
provider). Also you can set the root node property on the OLEDB, ADO and
ADO.Net providers that will add the root element around the fragment.
The property should be described in the SQLXML documentation.
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:0C8C2554-C69E-4E29-992F-D02E2BC61FFF@.microsoft.com...
> And also could you please let me know how to add root elements(which are
> not
> in my database) to XML output using stored procedure.
> For example:
> my XML output file looks like this when I retrieve data from SQLSERVER
> 2000
> using XML AUTO, ELEMENTS
> <GHI>
> <JKL>
> <MNO>
> ......
> ......
> </MNO>
> </JKL>
> </GHI>
> Now I want to add:
> <?XML VERSION=.........?>
> <ABC>
> <DEF>
> <GHI>
> ........
> ........
> </GHI>
> </DEF>
> </ABC>
> How can I add <ABC>, <DEF> elements for each <GHI> element
> Any idea would help
> Thanks
> Redd
> "Redd" wrote:
>