Returning XML from SQL Server

[Mike]

I have a web page that connects form controls to an XML DSO. The problem is
that when the table in SQL Server 2000 is empty (No Rows) then the stored
procedure that returns the XML doesn't return the structure of the table,
thus the DSO fails. Everything works fine as long as there is data in the
table.

How do I return the structure of the table in XML to the ASP page?

Should I consider using ADO recordsets instead?

This is the flow process.

--SQL Server 2000 stored procedure (spGetTable1)
-->COM+ object processes request and validates response
-->ASP page uses the XML passed in from the COM object

I think I need to create an XSL schema but I'm not sure.

If anyone could point me in the right direction with an example, book, web
site, etc. that would be greatly appreciated.

Thanks in advance

[Rob Vieira]

Hi Mike,

The XML doesn't include the metadata from the table because that comes in
the form of elements and attributes. Since there are no rows to return, you
wind up with no elements. Generally speaking, I would expect to just handle
that in code - that is, a document with no elements is essentially the same
as an empty recordset (save that ADO still jumps through the hoops of
creating the metadata to describe the recordset - that's actually some
serious overhead though).

If you really need to know what fields got passed back, then I would indeed
go with ADO for now.

--
Rob Vieira MCSD, MCT, MCDBA
www.ProfessionalSQL.com

"Mike" <merciermd@home.com> wrote in message
news:3a6d1fa2$1@news.devx.com...
>
> I have a web page that connects form controls to an XML DSO. The problem
is
> that when the table in SQL Server 2000 is empty (No Rows) then the stored
> procedure that returns the XML doesn't return the structure of the table,
> thus the DSO fails. Everything works fine as long as there is data in the
> table.
>
> How do I return the structure of the table in XML to the ASP page?
>
> Should I consider using ADO recordsets instead?
>
> This is the flow process.
>
> --SQL Server 2000 stored procedure (spGetTable1)
> -->COM+ object processes request and validates response
> -->ASP page uses the XML passed in from the COM object
>
> I think I need to create an XSL schema but I'm not sure.
>
> If anyone could point me in the right direction with an example, book, web
> site, etc. that would be greatly appreciated.
>
> Thanks in advance
>

[Mike]

Thanks for the response.

To expand on this subject a little further.

What I am attempting to do with the web page is to make it as rich as possible.
I want to send all the requested records back to the client to allow the
user to scroll, edit, delete, etc. without having to make a round trip to
the server on every row change. Everything is working fine, as long as there
are some records to return back to the client.

I can think of two scenarios to accomplish this. I would like to keep this
as pure XML if that is possible/practical.

Scenario #1:
Create an XSL Schema for each stored procedure. I found some code that will
create an empty XML document based on the schema. Then if there are no rows
in the table I can run the schema through the routine to create the XML document.

Scenario #2:
Use ADO

Are there any other options?

Performance wise, which of the two scenarios would be better?

Any thoughts or comments are appreciated.

Thanks
Mike



"Rob Vieira" <RobV@nospam.removethis.ProfessionalSQL.com> wrote:
>Hi Mike,
>
>The XML doesn't include the metadata from the table because that comes in
>the form of elements and attributes. Since there are no rows to return,
you
>wind up with no elements. Generally speaking, I would expect to just handle
>that in code - that is, a document with no elements is essentially the same
>as an empty recordset (save that ADO still jumps through the hoops of
>creating the metadata to describe the recordset - that's actually some
>serious overhead though).
>
>If you really need to know what fields got passed back, then I would indeed
>go with ADO for now.
>
>--
>Rob Vieira MCSD, MCT, MCDBA
>www.ProfessionalSQL.com
>
>"Mike" <merciermd@home.com> wrote in message
>news:3a6d1fa2$1@news.devx.com...
>>
>> I have a web page that connects form controls to an XML DSO. The problem
>is
>> that when the table in SQL Server 2000 is empty (No Rows) then the stored
>> procedure that returns the XML doesn't return the structure of the table,
>> thus the DSO fails. Everything works fine as long as there is data in
the
>> table.
>>
>> How do I return the structure of the table in XML to the ASP page?
>>
>> Should I consider using ADO recordsets instead?
>>
>> This is the flow process.
>>
>> --SQL Server 2000 stored procedure (spGetTable1)
>> -->COM+ object processes request and validates response
>> -->ASP page uses the XML passed in from the COM object
>>
>> I think I need to create an XSL schema but I'm not sure.
>>
>> If anyone could point me in the right direction with an example, book,
web
>> site, etc. that would be greatly appreciated.
>>
>> Thanks in advance
>>
>
>

[Rob Vieira]

You're pretty much on option #1 on the XML side.

Given what you've described to me, the XML solution will probably perform
better. Especially as the XML product matures (the ADO product has already
seen the improvements associated with many releases now, but SQL/XML is just
getting started).


--
Rob Vieira
www.ProfessionalSQL.com


"Mike" <merciermd@home.com> wrote in message
news:3a6e12b2$1@news.devx.com...
>
> Thanks for the response.
>
> To expand on this subject a little further.
>
> What I am attempting to do with the web page is to make it as rich as
possible.
> I want to send all the requested records back to the client to allow the
> user to scroll, edit, delete, etc. without having to make a round trip to
> the server on every row change. Everything is working fine, as long as
there
> are some records to return back to the client.
>
> I can think of two scenarios to accomplish this. I would like to keep this
> as pure XML if that is possible/practical.
>
> Scenario #1:
> Create an XSL Schema for each stored procedure. I found some code that
will
> create an empty XML document based on the schema. Then if there are no
rows
> in the table I can run the schema through the routine to create the XML
document.
>
> Scenario #2:
> Use ADO
>
> Are there any other options?
>
> Performance wise, which of the two scenarios would be better?
>
> Any thoughts or comments are appreciated.
>
> Thanks
> Mike
>
>
>
> "Rob Vieira" <RobV@nospam.removethis.ProfessionalSQL.com> wrote:
> >Hi Mike,
> >
> >The XML doesn't include the metadata from the table because that comes in
> >the form of elements and attributes. Since there are no rows to return,
> you
> >wind up with no elements. Generally speaking, I would expect to just
handle
> >that in code - that is, a document with no elements is essentially the
same
> >as an empty recordset (save that ADO still jumps through the hoops of
> >creating the metadata to describe the recordset - that's actually some
> >serious overhead though).
> >
> >If you really need to know what fields got passed back, then I would
indeed
> >go with ADO for now.
> >
> >--
> >Rob Vieira MCSD, MCT, MCDBA
> >www.ProfessionalSQL.com
> >
> >"Mike" <merciermd@home.com> wrote in message
> >news:3a6d1fa2$1@news.devx.com...
> >>
> >> I have a web page that connects form controls to an XML DSO. The
problem
> >is
> >> that when the table in SQL Server 2000 is empty (No Rows) then the
stored
> >> procedure that returns the XML doesn't return the structure of the
table,
> >> thus the DSO fails. Everything works fine as long as there is data in
> the
> >> table.
> >>
> >> How do I return the structure of the table in XML to the ASP page?
> >>
> >> Should I consider using ADO recordsets instead?
> >>
> >> This is the flow process.
> >>
> >> --SQL Server 2000 stored procedure (spGetTable1)
> >> -->COM+ object processes request and validates response
> >> -->ASP page uses the XML passed in from the COM object
> >>
> >> I think I need to create an XSL schema but I'm not sure.
> >>
> >> If anyone could point me in the right direction with an example, book,
> web
> >> site, etc. that would be greatly appreciated.
> >>
> >> Thanks in advance
> >>
> >
> >
>