[Gwal]

Is it possible to use an XML data source for mail merge? I am exporting data
from SQL Server in XML format and would like to merge it into a .dotx
template. Thanks

[Peter Jamieson]

Is it possible to use an XML data source for mail merge?

No. At least, not directly. Depending on the layout/content of the XML,
and what you need to achieve, you could consider
a. trying to open the XML in Excel. If you can end up with a tabular
format in there, you can save it and use it as the data source for a
merge. (Or you might be able to use some other program to do something
similar)
b. using an XSL transform to convert the XML to a format Word can use.

I am exporting data
from SQL Server in XML format

FWIW, if you can access SQL Server directly from your workstation you
should be able to connect from Word to a SQL /Table/ or /View? directly
and use it as a data source, although in recent versions of SQL AFAICS
you have to be using integrated Windows Authentication, not SQL Server
logins. However, with recent versions of Word it does not appear to be
possible to connect to stored procedures.

Peter Jamieson

http://tips.pjmsn.me.uk

On 13/04/2010 17:37, Gwal wrote:
Is it possible to use an XML data source for mail merge? I am exporting data
from SQL Server in XML format and would like to merge it into a .dotx
template. Thanks

[Gwal]

My end users cannot access SQL Server. We currently generate an Excel file
that is sent to them based on a date range they submit from a web page. They
are doing the merge. The issue they are having is character data that is all
numbers are treated as numbers in Excel during a simple import, and their
mearge fails unless they massage the columns. I just picked up this task
today and hoped XML would merge. I'm trying to get rid of the extra steps
they need to take and provide the merged docs for them.

I may try to writing something that merges the files programatically if that
makes any sense. The word 2007 .docx files are stored as XML. Have you
heard anything about taking this approach? Are there any links you can
provide if so?

Worst case I could write some VBA to do the formatting and massaging of the
data.
Thanks

"Peter Jamieson" wrote:

Is it possible to use an XML data source for mail merge?

No. At least, not directly. Depending on the layout/content of the XML,
and what you need to achieve, you could consider
a. trying to open the XML in Excel. If you can end up with a tabular
format in there, you can save it and use it as the data source for a
merge. (Or you might be able to use some other program to do something
similar)
b. using an XSL transform to convert the XML to a format Word can use.

I am exporting data
from SQL Server in XML format

FWIW, if you can access SQL Server directly from your workstation you
should be able to connect from Word to a SQL /Table/ or /View? directly
and use it as a data source, although in recent versions of SQL AFAICS
you have to be using integrated Windows Authentication, not SQL Server
logins. However, with recent versions of Word it does not appear to be
possible to connect to stored procedures.

Peter Jamieson

http://tips.pjmsn.me.uk

On 13/04/2010 17:37, Gwal wrote:
Is it possible to use an XML data source for mail merge? I am exporting data
from SQL Server in XML format and would like to merge it into a .dotx
template. Thanks
.

[Doug Robbins - Word MVP]

What is the issue with the character data that is all numbers? What is the
merged output from it and what should it be?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"Gwal" wrote in message
...
My end users cannot access SQL Server. We currently generate an Excel
file
that is sent to them based on a date range they submit from a web page.
They
are doing the merge. The issue they are having is character data that is
all
numbers are treated as numbers in Excel during a simple import, and their
mearge fails unless they massage the columns. I just picked up this task
today and hoped XML would merge. I'm trying to get rid of the extra steps
they need to take and provide the merged docs for them.

I may try to writing something that merges the files programatically if
that
makes any sense. The word 2007 .docx files are stored as XML. Have you
heard anything about taking this approach? Are there any links you can
provide if so?

Worst case I could write some VBA to do the formatting and massaging of
the
data.
Thanks

"Peter Jamieson" wrote:

Is it possible to use an XML data source for mail merge?

No. At least, not directly. Depending on the layout/content of the XML,
and what you need to achieve, you could consider
a. trying to open the XML in Excel. If you can end up with a tabular
format in there, you can save it and use it as the data source for a
merge. (Or you might be able to use some other program to do something
similar)
b. using an XSL transform to convert the XML to a format Word can use.

I am exporting data
from SQL Server in XML format

FWIW, if you can access SQL Server directly from your workstation you
should be able to connect from Word to a SQL /Table/ or /View? directly
and use it as a data source, although in recent versions of SQL AFAICS
you have to be using integrated Windows Authentication, not SQL Server
logins. However, with recent versions of Word it does not appear to be
possible to connect to stored procedures.

Peter Jamieson

http://tips.pjmsn.me.uk

On 13/04/2010 17:37, Gwal wrote:
Is it possible to use an XML data source for mail merge? I am
exporting data
from SQL Server in XML format and would like to merge it into a .dotx
template. Thanks
.

[Peter Jamieson]

My end users cannot access SQL Server.

OK.

I'm trying to get rid of the extra steps
they need to take and provide the merged docs for them.

Can I just clarify something here - do you mean that
a. if possible, you would do all the merge work at your end and
provide your end users with an output document (or perhaps some other
product such as a folder of output documents) or
b. for whatever reason, your end users will still have to perform the
merge(s) so you need to provide them with either a data source or a data
source + mail merge main document ?

If it's (a), you can consider "using mail merge to connect directly to
SQL Server", but you would need to be able to retrieve the required data
for each user using a SQL SELECT not more than 511 characters (possibly
255 in some cases) long. Otherwise, the only way to do it AFAIK is to
create a table-valued Transact-SQL query on the server that accepts the
date range parameters, and to get the syntax of your SQL QUERY in Word
right.

If you can't do either of those two types of query, you have a few
options, including:
c. create a mail merge data source that Word /can/ use. Maybe you
could say a bit more about your requirements and options there (e.g.
could you use comma- or tab-delimited data (appropriately wrapped with
text delimiters where necessary)? Do you have multiline data? Could you
create a .mdb? However, I couldn't say for certain that you would not
run into the same problem you have with Excel).
d. "Roll your own" mail merge that uses (say) VBA+ADO to get data from
SQL Server and insert it - e.g., one way is to use { DOCVARIABLE } fields
e. "Roll your own" mail merge that uses .docx format, its facility to
contain a private XML data store, and content controls instead of
fields. If it's a "letter" type merge, the Merge then consists of
pointing to the appropriate record in the store and letting Word fill
the content controls with the values in that record. Typically that is
done with .NET and/or VSTO but I think it is feasible with VBA. However,
it seems to me to be unnecessarily complicated if you can manage (c) or
(d). If you really want to pursue it I can try to find some pointers.
f. some combination of the above and/or other techniques.

If you have to do (b) then (c) would presumably still be workable, (d)
would not, and (e) would be, but only if you can guarantee that the code
you write can be executed on the end user platform.


Peter Jamieson

http://tips.pjmsn.me.uk

On 13/04/2010 19:30, Gwal wrote:
My end users cannot access SQL Server. We currently generate an Excel file
that is sent to them based on a date range they submit from a web page. They
are doing the merge. The issue they are having is character data that is all
numbers are treated as numbers in Excel during a simple import, and their
mearge fails unless they massage the columns. I just picked up this task
today and hoped XML would merge. I'm trying to get rid of the extra steps
they need to take and provide the merged docs for them.

I may try to writing something that merges the files programatically if that
makes any sense. The word 2007 .docx files are stored as XML. Have you
heard anything about taking this approach? Are there any links you can
provide if so?

Worst case I could write some VBA to do the formatting and massaging of the
data.
Thanks

"Peter Jamieson" wrote:

Is it possible to use an XML data source for mail merge?

No. At least, not directly. Depending on the layout/content of the XML,
and what you need to achieve, you could consider
a. trying to open the XML in Excel. If you can end up with a tabular
format in there, you can save it and use it as the data source for a
merge. (Or you might be able to use some other program to do something
similar)
b. using an XSL transform to convert the XML to a format Word can use.

I am exporting data
from SQL Server in XML format

FWIW, if you can access SQL Server directly from your workstation you
should be able to connect from Word to a SQL /Table/ or /View? directly
and use it as a data source, although in recent versions of SQL AFAICS
you have to be using integrated Windows Authentication, not SQL Server
logins. However, with recent versions of Word it does not appear to be
possible to connect to stored procedures.

Peter Jamieson

http://tips.pjmsn.me.uk

On 13/04/2010 17:37, Gwal wrote:
Is it possible to use an XML data source for mail merge? I am exporting data
from SQL Server in XML format and would like to merge it into a .dotx
template. Thanks
.

[Gwal]

Thanks for the replys.
As I dig deeper into this I'm finding some (dumb) constraints. There is
total resistance to change, especially if it involves any changes to customer
process. It's one of those , "thats how we have done it for years"
situations. In fact, even if I could do the merge for them my management
won't accept the change - they won't even offer that solution to the
customer. ???

It now looks like my issue is off topic for this fourm - it's generating an
Excel spreadsheet that is sorted and has types defined on the columns (e.g.,
the current export shows SSN's as floats or scientifics). Actually the code
is creating a CVS file which is made available on a web page. The CVS files
open in Excel by default and the users then have to massage the data and then
do the merge from there. I am supposed to do this in a Java/struts
environment (which I have about 2 mo. of exp.)- I'm a C#/.net guy and any
..net is a big no-no. It's crazy but I'm sure you've been in the same type of
situation somewhere in your career.

I think I'm still going to take an XML approach as you can import an XML
file into excel based on a xsd and put up a real Excel file. Hopefully I can
just write a shell script (under the covers) to do the data extract and the
excel launch, having the Java only call the script.

Again, thanks for the reply - your information will certianly be useful in
other situations.

"Gwal" wrote:

My end users cannot access SQL Server. We currently generate an Excel file
that is sent to them based on a date range they submit from a web page. They
are doing the merge. The issue they are having is character data that is all
numbers are treated as numbers in Excel during a simple import, and their
mearge fails unless they massage the columns. I just picked up this task
today and hoped XML would merge. I'm trying to get rid of the extra steps
they need to take and provide the merged docs for them.

I may try to writing something that merges the files programatically if that
makes any sense. The word 2007 .docx files are stored as XML. Have you
heard anything about taking this approach? Are there any links you can
provide if so?

Worst case I could write some VBA to do the formatting and massaging of the
data.
Thanks

"Peter Jamieson" wrote:

Is it possible to use an XML data source for mail merge?

No. At least, not directly. Depending on the layout/content of the XML,
and what you need to achieve, you could consider
a. trying to open the XML in Excel. If you can end up with a tabular
format in there, you can save it and use it as the data source for a
merge. (Or you might be able to use some other program to do something
similar)
b. using an XSL transform to convert the XML to a format Word can use.

I am exporting data
from SQL Server in XML format

FWIW, if you can access SQL Server directly from your workstation you
should be able to connect from Word to a SQL /Table/ or /View? directly
and use it as a data source, although in recent versions of SQL AFAICS
you have to be using integrated Windows Authentication, not SQL Server
logins. However, with recent versions of Word it does not appear to be
possible to connect to stored procedures.

Peter Jamieson

http://tips.pjmsn.me.uk

On 13/04/2010 17:37, Gwal wrote:
Is it possible to use an XML data source for mail merge? I am exporting data
from SQL Server in XML format and would like to merge it into a .dotx
template. Thanks
.

[Peter Jamieson]

It's crazy but I'm sure you've been in the same type of
situation somewhere in your career.


Many times. FWIW, if they'll let you use Excel VBA you will almost
certainly be able to do everything you need in there.

Good luck!

Peter Jamieson

http://tips.pjmsn.me.uk

On 14/04/2010 18:07, Gwal wrote:
It's crazy but I'm sure you've been in the same type of
situation somewhere in your career.