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