Returning XML from SQL Server


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Returning XML from SQL Server

  1. #1
    Mike Guest

    Returning XML from SQL Server


    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

    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: Returning XML from SQL Server

    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
    >



    Share on Google+

  3. #3
    Mike Guest

    Re: Returning XML from SQL Server


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

    >
    >


    Share on Google+

  4. #4
    Rob Vieira Guest

    Re: Returning XML from SQL Server

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

    > >
    > >

    >



    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center