SQL to XML Resources

Discussion in 'ASP .Net' started by Brendan Reynolds, Jul 25, 2005.

  1. I have an ASP.NET app that needs to extract data from multiple related
    tables in a SQL Server database and POST the data as XML to a third-party
    app, using a schema specified by the third party. I realise that this is
    quite a general and wide-ranging description of the problem. I don't expect
    anyone to provide a step-by-step solution in a newsgroup post, but I'm
    hoping that someone may be able to recommend some resources that might help
    me get a handle on this. On-line resources would be especially welcome, but
    book recommendations are OK too.

    --
    Brendan Reynolds
    Brendan Reynolds, Jul 25, 2005
    #1
    1. Advertising

  2. Brendan Reynolds

    Clint Hill Guest

    You probably want to look at the SQL query to return XML. Look up the
    clause with SQL Server Books Online, and what you want is something like
    the following:

    SELECT (XML Schema from third party applied to columns)
    FROM dbo.YourTable
    FOR XML EXPLICIT, ELEMENTS

    The part between the parentheses will be tricky, I have personally only
    done it once.

    Clint Hill
    H3O Software
    http://www.h3osoftware.com

    Brendan Reynolds wrote:
    > I have an ASP.NET app that needs to extract data from multiple related
    > tables in a SQL Server database and POST the data as XML to a third-party
    > app, using a schema specified by the third party. I realise that this is
    > quite a general and wide-ranging description of the problem. I don't expect
    > anyone to provide a step-by-step solution in a newsgroup post, but I'm
    > hoping that someone may be able to recommend some resources that might help
    > me get a handle on this. On-line resources would be especially welcome, but
    > book recommendations are OK too.
    >
    Clint Hill, Jul 25, 2005
    #2
    1. Advertising

  3. Thanks Clint.

    --
    Brendan Reynolds


    "Clint Hill" <> wrote in message
    news:...
    > You probably want to look at the SQL query to return XML. Look up the
    > clause with SQL Server Books Online, and what you want is something like
    > the following:
    >
    > SELECT (XML Schema from third party applied to columns)
    > FROM dbo.YourTable
    > FOR XML EXPLICIT, ELEMENTS
    >
    > The part between the parentheses will be tricky, I have personally only
    > done it once.
    >
    > Clint Hill
    > H3O Software
    > http://www.h3osoftware.com
    >
    > Brendan Reynolds wrote:
    >> I have an ASP.NET app that needs to extract data from multiple related
    >> tables in a SQL Server database and POST the data as XML to a third-party
    >> app, using a schema specified by the third party. I realise that this is
    >> quite a general and wide-ranging description of the problem. I don't
    >> expect anyone to provide a step-by-step solution in a newsgroup post, but
    >> I'm hoping that someone may be able to recommend some resources that
    >> might help me get a handle on this. On-line resources would be especially
    >> welcome, but book recommendations are OK too.
    >>
    Brendan Reynolds, Jul 25, 2005
    #3
  4. Brendan Reynolds

    vMike Guest

    "Brendan Reynolds" <> wrote in message
    news:%23Fc$...
    > I have an ASP.NET app that needs to extract data from multiple related
    > tables in a SQL Server database and POST the data as XML to a third-party
    > app, using a schema specified by the third party. I realise that this is
    > quite a general and wide-ranging description of the problem. I don't

    expect
    > anyone to provide a step-by-step solution in a newsgroup post, but I'm
    > hoping that someone may be able to recommend some resources that might

    help
    > me get a handle on this. On-line resources would be especially welcome,

    but
    > book recommendations are OK too.
    >
    > --
    > Brendan Reynolds
    >
    >
    >

    You could also fill a dataset from the sql data and then use the
    dataset.writexml method to save to a file or a stream.
    Mike
    vMike, Jul 25, 2005
    #4
  5. Brendan Reynolds

    Clint Hill Guest

    Absolutely. However, it could be more difficult to manage the 3rd party
    schema with a DataSet. Certainly could be done, however I would rather
    SQL just hand over the xml and do a write to the page with it on POST.

    Good idea though because it is certainly quicker to do than the SQL route.

    Clint Hill
    H3O Software
    http://www.h3osoftware.com

    vMike wrote:
    > "Brendan Reynolds" <> wrote in message
    > news:%23Fc$...
    >
    >>I have an ASP.NET app that needs to extract data from multiple related
    >>tables in a SQL Server database and POST the data as XML to a third-party
    >>app, using a schema specified by the third party. I realise that this is
    >>quite a general and wide-ranging description of the problem. I don't

    >
    > expect
    >
    >>anyone to provide a step-by-step solution in a newsgroup post, but I'm
    >>hoping that someone may be able to recommend some resources that might

    >
    > help
    >
    >>me get a handle on this. On-line resources would be especially welcome,

    >
    > but
    >
    >>book recommendations are OK too.
    >>
    >>--
    >>Brendan Reynolds
    >>
    >>
    >>

    >
    > You could also fill a dataset from the sql data and then use the
    > dataset.writexml method to save to a file or a stream.
    > Mike
    >
    >
    Clint Hill, Jul 25, 2005
    #5
  6. Thanks - I probably will do it that way, at least in the short term. I was
    at first reluctant to do that because the app isn't using datasets for any
    other purpose, and it didn't seem very efficient to create and fill a
    dataset just to act as a sort of staging area between the database and the
    XML document. But it does look as though the dataset method may be easier,
    and I need to get something up and running quickly. I might use the dataset
    now and investigate the SQL Server FOR XML option later. I appreciate both
    suggestions - it's always good to have choices! :)

    --
    Brendan Reynolds

    "vMike" <> wrote in message
    news:3%6Fe.16823$...
    >
    > "Brendan Reynolds" <> wrote in message
    > news:%23Fc$...
    >> I have an ASP.NET app that needs to extract data from multiple related
    >> tables in a SQL Server database and POST the data as XML to a third-party
    >> app, using a schema specified by the third party. I realise that this is
    >> quite a general and wide-ranging description of the problem. I don't

    > expect
    >> anyone to provide a step-by-step solution in a newsgroup post, but I'm
    >> hoping that someone may be able to recommend some resources that might

    > help
    >> me get a handle on this. On-line resources would be especially welcome,

    > but
    >> book recommendations are OK too.
    >>
    >> --
    >> Brendan Reynolds
    >>
    >>
    >>

    > You could also fill a dataset from the sql data and then use the
    > dataset.writexml method to save to a file or a stream.
    > Mike
    >
    >
    Brendan Reynolds, Jul 25, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SuperGumby

    asp sql learning resources

    SuperGumby, Nov 8, 2003, in forum: ASP .Net
    Replies:
    3
    Views:
    359
    SuperGumby
    Nov 10, 2003
  2. Harry Zoroc
    Replies:
    1
    Views:
    929
    Gregory Vaughan
    Jul 12, 2004
  3. Russell E. Owen
    Replies:
    0
    Views:
    699
    Russell E. Owen
    Sep 8, 2006
  4. ecoolone
    Replies:
    0
    Views:
    752
    ecoolone
    Jan 3, 2008
  5. david.karr
    Replies:
    9
    Views:
    1,102
    Arne Vajhøj
    Jul 19, 2009
Loading...

Share This Page