Export valid XML files

Ladies and Gents,
I cannot seem to find an (easily) workable solution anywhere on the web.
All I need to do, is return the contents of some SQL server tables as XML
data sets.
I was able to form "proper" XML files by adding a top-level root element
(still unclear on why that is not built in). Now the issue that remains is
that SQL server will return the results with a ROWGUID and a full line
of undescores (_________________) before the actual XML.
I am trying to keep the process as simple as can get, avoiding SQLXML
and IIS -- I just want to find a solution where I can create a simple (!)
DTS package that will deliver a properly formatted XML file with a top
level element, one that IE will be able to display properly without
any further editing.
1) is this possible (anything is possible -- can this be done via some form
of "FOR XML" statement)
2) if there is no easy solution to 1, what methods would you recommend to
make the process
most maintanable? My requirements are simple -- I need to produce a
"feed" -- a set of properly formatted
XML files, based on many queries -- probably about 15-20 files
alltogether, so I need this to be
as simple as possible.
TIA,
EugeneCould you be more specific? Are you trying to export the database schema or
the data in your tables?
As simple as possible? A FOR XML query in a SQLXML template.
ML|||If you follow the "Microsoft" way, you set up a "web service" using the HTML
capabilities of SQL Server. The template supplies the formatting you need an
d
returns data in the proper format.
I do not have a link, but you can push a root tag into your FOR XML return.
The examples I have seen use the template, as well.
If the return is not valid, you can also throw the data through an XSLT
transform and get it to look however you desire. This is an extra step,
however.
NOTE: I am fairly fond of the whole SOA/web service concept, but it is not
for everyone. For SQL Server 2000, the link into IIS is not the best
implementation (HTTP endpoints in SQL 2005 are much better), but they provid
e
a means of moving towards SOA. SOA is not a silver bullet, but it makes a lo
t
of sense if you can make the paradigm change to working with messages instea
d
of CRUD.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Eugene" wrote:

> Ladies and Gents,
> I cannot seem to find an (easily) workable solution anywhere on the web.
> All I need to do, is return the contents of some SQL server tables as XML
> data sets.
> I was able to form "proper" XML files by adding a top-level root element
> (still unclear on why that is not built in). Now the issue that remains i
s
> that SQL server will return the results with a ROWGUID and a full line
> of undescores (_________________) before the actual XML.
> I am trying to keep the process as simple as can get, avoiding SQLXML
> and IIS -- I just want to find a solution where I can create a simple (!)
> DTS package that will deliver a properly formatted XML file with a top
> level element, one that IE will be able to display properly without
> any further editing.
> 1) is this possible (anything is possible -- can this be done via some for
m
> of "FOR XML" statement)
> 2) if there is no easy solution to 1, what methods would you recommend to
> make the process
> most maintanable? My requirements are simple -- I need to produce a
> "feed" -- a set of properly formatted
> XML files, based on many queries -- probably about 15-20 files
> alltogether, so I need this to be
> as simple as possible.
> TIA,
> Eugene
>
>|||I am trying to export the data.
Something along the lines of :
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||I am trying to export the data. Here's my query
SELECT
1 AS TAG
,NULL AS PARENT
,NULL AS [INSTITUTIONS!1]
,NULL AS [INSTITUTION!2!iconum]
,NULL AS [INSTITUTION!2!cusip]
,NULL AS [INSTITUTION!2!dlr]
,NULL AS [INSTITUTION!2!position]
,NULL AS [INSTITUTION!2!pct_held]
,NULL AS [INSTITUTION!2!position_change]
UNION
SELECT 2 AS TAG
,1 AS PARENT
,NULL AS [INSTITUTIONS!1]
,iconum AS [INSTITUTION!2!iconum]
,cusip AS [INSTITUTION!2!cusip]
,dlr AS [INSTITUTION!2!dlr]
,position AS [INSTITUTION!2!position]
,pct_held AS [INSTITUTION!2!pct_held]
,position_change AS [INSTITUTION!2!position_change]
FROM ##EUGENE_TEMP
ORDER BY [INSTITUTION!2!iconum]
FOR XML EXPLICIT
I get back something like :
XML_F52E2B61...
_____________________________
<INSTITUTIONS>
<INSTITUTION iconum="10134" cusip="151313103" dlr="2005-03-31T00:00:00"
position="22000" pct_held="0.002" position_change="22000"/>
</INSTITUTIONS>
I need to not have the top
XML_F52E2B61...
_____________________________.
SQLXML is not really an option now -- no ISS on the SQL box... Looks like
I'll just have to script a solution, unless
someone has another idea...
Thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||If you're using BCP to write the results to a text file or using QA's
"Results to file" option, you'll have to remove the unwanted pieces of text
either manually or programatically (not in T-SQL).
Whether you like it or not, you will need a client application to read the
result.
Oh, and another thing - XML results might get split into several rows of
data if not read as an XML by an appropriate client.
If installing IIS creates a possible breach of security, you can still use
the IIS lock-down tool to keep your network secure. The power of SQLXML is
immense, so why not use it?
ML

export to cvs/excel with comments attached

i'm building a web page to help our support staff run and export their sql queries faster and more easily than going through the sql server manager. i found an article at codeproject [1] that i was able to customize to be more flexible, but i'm not sure how to get it to export the results of a query along with the comments that preceed the query.

for example, i have the following sql file:

-- this query does a certain thing, and the comment can be pretty long at times, depending on how complicated the query isselect *from table where columnid > 100

would anyone be able to tell me how to edit Kolluri's export function to allow for a comment section? alternatively if his way of doing an export is out of date, i'd be interested in a newer way. most importantly, though, i need to be able to export the comments along with query results.

my ultimate goal is to have a drop down list of databases on the server, a list of query files (from a certain consolodated folder) for them to run on the chosen database, a "test query" function, and an export function that exports the comments and results.

[1] http://www.codeproject.com/aspnet/ExportClassLibrary.asp

if i'm going about this all wrong, i don't mind changing directions. :)

what i'm really trying to do is allow our support staff to speed up their query running. currently they have a folder of txt and sql files that they need to run on a certain database (which is different for different support staff). right now they open one of these txt/sql files, open sql studio, copy the query from the txt/sql, paste it into sql studio, run the query, export the results, open the results, and paste the query into the exported results file. rinse and repeat for every txt/sql file in the folder, which is time consuming and ridiculous.

this seems very repetitive and i was hoping to help them out by allowing them to go to a web page, select one or more queries from a list, pick which database they need to connect to, and i'd loop through all those queries and run them with the support staff having to only click one button to start that process. i've got almost every part of this plan working except being able to put the query in comments at the top of the exported file, but if i need to change my entire game plan to allow that, i'm willing. having the query at the top of the exported results is very important, and if i can't get this working, my whole web page is a bust.

Export Schema

Hi,

Not sure if this is a simple question...

I would like to export the schema of my database to allow me to print it out in an easily viewable form ( preferably in to visio ). Is this possible, perhaps through a wizard so i can just show the primary / secondary keys of the tables and how they all link ?

Many thanks for your responses.

in Visio there is an option to Update Model from Database. Open a visio window. You will have "Database " Menu . Select Update... Follow the on screen instruction(you will have to make connection and all.... i assume that u r familier with Visio) and one window you will have an option called update Model from database. Select that and go ahead. It will create schem of your database in visio

Madhu