SQL to XML Resources

B

Brendan Reynolds

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

Clint Hill

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
 
B

Brendan Reynolds

Thanks Clint.

--
Brendan Reynolds


Clint Hill said:
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
 
V

vMike

Brendan Reynolds said:
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.
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
 
C

Clint Hill

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
 
B

Brendan Reynolds

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! :)
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top