Re: how to export view to display - xml with specific xsd?

Long response, please bear with me...

A couple of things come to mind after reading this thread and looking at
your previous post. The first is that you are using xp_cmdshell to invoke
Word, but aren't giving it a filename. You're passing it the return value
of the stored procedure which is invariably an integer and most likely zero.
Also xp_cmdshell runs on the server, not on the client. So unless your
users are running this directly on the SQL Server they'll probably never see
any results.

It sounds to me like what you are trying to do is save the XML generated by
SQL Server to a file and open the file in Word on the client machine. I
would recommend first of all that you disable xp_cmdshell. It opens up a
whole can of potential security problems. It also sounds like you want the
XML to be generated in a Word-specific XML format. I believe Office 2003
only supports some strange HTML+XML hybrid format (or something like that),
which you'll have to look up documentation for to ensure compliance (Office
2007 supports a pure XML format).

The XSD schema also has nothing to do with the output format, apart from
possibly validating it for you -- it's for input. If you want to transform
your raw XML into another format you need to look into XSLT stylesheets. If
you aren't interested in converting your raw XML to another format there may
be other issues to address. As Marc pointed out, how you are trying to call
the stored procedure and retrieve the results is a big part of the
resolution here.

Possibly the simplest solution might be to call a SQLCLR function to save
the XML output to a file on a server shared directory, and pass the full
path back to the client. You'll need to open up some sort of result reader,
like the .NET SqlDataReader, to get the path. That, or you can pass the
result back in a varchar OUTPUT parameter. Remember, stored procedures only
return a single integer value as a return result; you need to use OUTPUT
parameters or datasets to return other types of values from a stored proc.

Finally invoke the command shell on the client and call Word.exe with the
full path to the file on the share to open it (in whatever format is
required by the Word.exe application). Remember this is a client-side
operation, and unless every user is going to have SQL Server with your full
database installed locally (or if everyone has direct access to the server),
opening up Word.exe on the server isn't going to do them any good.

You're trying to do it all on the server, but half of what you want to do
(by your description) occurs on the client. As Marc pointed out, if you're
trying to do this from within Word directly (client already has Word open)
you might be able to use VBA to call the SP and pass the XML directly back
to Word. That might be a question better asked in an MS Office, Word, or
VBA newsgroup though. If you are trying to do this from a web page or some
such on the client (they click a button on the web page or in the Windows
Forms app, the SP runs, and the results open up in Word locally), you might
be able to use some client-side dynamic scripting or .NET code to do the
job. Half your answer depends on your client application.

"Jane" <Jane@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D056DA1B-3B88-4088-A666-0F632A6A70D5@xxxxxxxxxxxxxxxx
All my code is in T-SQL, Sql Server 2005. We're using Office 2003. I
created a new post on 12/18 'Open XML output in word' that best describes
what I'm trying to do so you can view that.

If you look at the sample code under that post, I'm calling a Sproc that
runs a 'Select.. for xml'; according to the documentation this returns a
filepointer - click on it to open the xml file. but the sproc returns an
integer when I call it in code. I want word to open the file so the user
can
save it.

I appreciate your help - Jane

"Marc Gravell" wrote:

I want to let the user choose where to
save the file. The examples I've seen don't explain how to do this -
can you
help me with this?

Well, what is the architecture of your application? ASP.NET? .NET
WinForms? MFC? WPF? What?

I can answer for some of those... but will need a clue which...




.