Export data in XML to import into Word

Question

Export data in XML to import into Word

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

kevaburg SSCoach Points: 18131 More actions · Answer 1

drew.allen - Tuesday, May 2, 2017 10:55 AM
I think the reason that no one has done it, is because the best approach is to use SSRS and export to Word.
Drew

SSRS was an idea that I had but then completely without Word in play. The idea was to create the entire report in SSRS and Email at the required interval as a subscription, I even have an RDL prepared but even that idea depends on a Report Server being available which isn't always the case.

That was the reason why I am trying to use XML in Word. It is even an idea I can use with vNext......as long as I can import the files into Word.

It's driving me mad!!!

Jeff Moden SSC Guru Points: 1004281 More actions · Answer 2

kevaburg - Tuesday, May 2, 2017 11:44 AM
Matt Simmons - Tuesday, May 2, 2017 8:32 AM
kevabug,
I think you mentioned your solution, or at least half of it. "In Excel I can create an ODBC or OLEDB connection and extract information direct from the SQL Server with TSQL an[d] display it in a workbook." Why not then use the .XLS file as the source for you Word Template and you get the best of what you are looking for?
Hi Matt,
thanks for the suggestion but that middle step introduces a new complexity that needs to be understood to be effective. My goal of importing XML into a Word document is really becoming a challenge.
I have even posted the question in a Word forum but nothing has come back. I am beginning to wonder if it is even possible....
Regards,
Kev

With the understanding that I've not done such a thing in more than 20 years (and it wasn't with XML files) and If you have standard "sections" that you'd like to create (think "data dictionary" at the object level), you could create a template for a "section" and, with the help of a little VBA, do a "merge" of the files like you would a form letter and then, with the help of a little more VBA, auto-magically attach each "section" to a master document, which could certainly have a cover page, table of contents, etc. It wouldn't even require you to have Word on the server. The files could live anywhere.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76984 More actions · Answer 3

kevaburg - Tuesday, May 2, 2017 11:49 AM
drew.allen - Tuesday, May 2, 2017 10:55 AM
I think the reason that no one has done it, is because the best approach is to use SSRS and export to Word.
Drew
SSRS was an idea that I had but then completely without Word in play. The idea was to create the entire report in SSRS and Email at the required interval as a subscription, I even have an RDL prepared but even that idea depends on a Report Server being available which isn't always the case.
That was the reason why I am trying to use XML in Word. It is even an idea I can use with vNext......as long as I can import the files into Word.
It's driving me mad!!!

Since SSRS comes with SQL Server, a Report Server can be made available, so if a Report Server is not available, it's by choice. The Report Server doesn't even have to be all that powerful.

Another option is that Word can use SQL Server directly as a data source for mail merges. No reason to go through an XML file. I'm not sure what the requirements are for using SQL as a data source are.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Matt Simmons SSCrazy Points: 2846 More actions · Answer 4

kevaburg - Tuesday, May 2, 2017 11:44 AM
Matt Simmons - Tuesday, May 2, 2017 8:32 AM
kevabug,
I think you mentioned your solution, or at least half of it. "In Excel I can create an ODBC or OLEDB connection and extract information direct from the SQL Server with TSQL an[d] display it in a workbook." Why not then use the .XLS file as the source for you Word Template and you get the best of what you are looking for?
Hi Matt,
thanks for the suggestion but that middle step introduces a new complexity that needs to be understood to be effective. My goal of importing XML into a Word document is really becoming a challenge.
I have even posted the question in a Word forum but nothing has come back. I am beginning to wonder if it is even possible....
Regards,
Kev

Kev, Why not connect directly to SQL via Word as several others have suggested? The Mail Merge Wizard will walk you thru the connection step by step. IMHO, XML is just adding to the complexity of what you are attempting.

Regards,
Matt

David Jackson SSCertifiable Points: 6414 More actions · Answer 5

This is my take on office automation. It is a little off-topic from what the OP asked but does create Office files (An Excel spreadsheet) for any given query.

To run this script open a command prompt in the folder and enter

cscript RunQueries.vbs -sn YourServer -dbn YourDatabase -isf C:\Jobs\sqlScripts.sql -fp C:\ -sf False -er True

Parameters
-sn Server Name to use - Defaults to (local)
-psn Prompt for Server Name - Defaults to False
-dbn Database Name(s) to run in - Defaults to master. Pass in DB names separated by a comma for multiple runs e.g. DBName1,DBName2,DBName3
-isf Input Script File to run - Defaults to sqlScripts.sql
-fp File Path to save to. Defaults to "". If not blank the file name is path + script name + timestamp.xlsx
-sf Save File? Whether to prompt to save the workbook on exit - Defaults to False
-er Enable Refresh? If True, the tables in Excel remain linked to the data source - Defaults to False

The last parameter can be especially useful if the user opening the sheet has direct query access.

HTH
Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Murthy 4104 SSC Rookie Points: 47 More actions · Answer 6

If you are looking for a off-the-shelf solution, try EDocGen . Using EDocGen, you can populate XML into word template to generate bulk documents without any XML manipulation. In the mapper screen, just map the XML elements to Word template's dynamic fields. Also, EDocGen offers Zapier integration for on-demand document generation from SQL Server.

Jeff Moden SSC Guru Points: 1004281 More actions · Answer 7

Matt Simmons - Tuesday, May 2, 2017 2:31 PM
Kev, Why not connect directly to SQL via Word as several others have suggested? The Mail Merge Wizard will walk you thru the connection step by step. IMHO, XML is just adding to the complexity of what you are attempting.

+1000.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aaron.reese SSChampion Points: 13498 More actions · Answer 8

I tried to do this recently with WORD 2010 on-premise with no success.
I don't know about 2016 version or O365 but 2010 has no functionality to read XML files which is a real shame.

My reason for using it was the output had multiple nested tables. These has a well defined structure but the number of rows and whether the table appeared at all was variable depending on the system contents and business rules.

In the end my mailing house took my 1.4 million row XML file and converted it back into some monster hybrid Excel format that they then used to populate their mail merge tools.

Another option would be to feed the XML into an xslt parser and convert it to HTML with CSS for formatting. - but again, another set of tools to be learned

kevaburg SSCoach Points: 18131 More actions · Answer 9

Hi all,

many thanks for all the hints and tips but I have gone for creating the report in SSRS and then importing it as a default report into each installed instance.

I thought the solution would have been a lot more elegant than this but it actually looks quite good and is available on-demand should it be required.

Once again many thanks for your input......

Regards,
Kev