April 29, 2017 at 4:05 am
Hi Folks,
I am building a one-click application to export information from SQL Server 2016 in XML format that will be imported into Microsoft Word 365. The export will be completed with BCP:
bcp"select dbname from dbo.tbl_axs_databasesizes order by dbname asc for xml auto"
queryout"c:\Backups\test.xml"
-T -S. -daxs_utilitydb -w -k
-ec:\Backups\test_errors.txt"
The created file appears to be correct and is attached here as the file "testxml.png".
For some reason the file can't be imported into Word and I can't figure out why.
Has someone here experience with this sort of task?
Thanks in advance.
Regards,
Kev
April 29, 2017 at 5:01 am
Quick question, why is there no root element in the xml?
😎
April 29, 2017 at 8:57 am
Eirikur Eiriksson - Saturday, April 29, 2017 5:01 AMQuick question, why is there no root element in the xml?
😎
The file as I posted is what BCP produced. I can only assume that BCP doesn't include the root element when the data is exported.
I am relatively new to XML and the structure is still somewhat of a black box for me....
April 29, 2017 at 11:19 am
So, I have altered the BCP export to include elements and the root element:
bcp"select dbname from dbo.tbl_databasesizes order by dbname asc for xml auto, elements, root('DBList')"
queryout "c:\Backups\test.xml"
-T -S.
-dutilitydb
-w -k
-ec:\Backups\test_errors.txt"
I can now Create a new XML Mapping object (see attachment) but now don't know what to do with it.... 🙁
April 29, 2017 at 5:02 pm
kevaburg - Saturday, April 29, 2017 11:19 AMSo, I have altered the BCP export to include elements and the root element:
bcp"select dbname from dbo.tbl_databasesizes order by dbname asc for xml auto, elements, root('DBList')"
queryout "c:\Backups\test.xml"
-T -S.
-dutilitydb
-w -k
-ec:\Backups\test_errors.txt"I can now Create a new XML Mapping object (see attachment) but now don't know what to do with it.... 🙁
Maybe "merge fields" in Word? You'd have a "template" where it would do the merge as soon as the document opened in an "auto-update" fashion.
Shifting gears a bit, why "Word"? Are you trying to create some form of server documentation?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2017 at 11:35 pm
My question is similar to Jeff's - why export to MS Word? If you're exporting XML, wouldn't a text file format be appropriate?
April 30, 2017 at 1:48 am
Jeff Moden - Saturday, April 29, 2017 5:02 PMkevaburg - Saturday, April 29, 2017 11:19 AMSo, I have altered the BCP export to include elements and the root element:
bcp"select dbname from dbo.tbl_databasesizes order by dbname asc for xml auto, elements, root('DBList')"
queryout "c:\Backups\test.xml"
-T -S.
-dutilitydb
-w -k
-ec:\Backups\test_errors.txt"I can now Create a new XML Mapping object (see attachment) but now don't know what to do with it.... 🙁
Maybe "merge fields" in Word? You'd have a "template" where it would do the merge as soon as the document opened in an "auto-update" fashion.
Shifting gears a bit, why "Word"? Are you trying to create some form of server documentation?
The aim is indeed to automate documentation. I have created a series of queries that extract information from the instance and associated databases and this should be imported into a prepared template with a single click that can be created in the shortest time possible.
Although quite a lot can be done with the MAP Toolkit or the SSMA or SSDM Reports, I want to be able to create documentation of a high quality that can be tailored and personalised. The process should be:
1. Run a batch file with the BCP statements on the server to be documented to create the XML files
2. Copy the folder with the XML files to a host with Microsoft Word installed
3. Select the template to be used
4. Create a copy of the template and import the XML files into the relevant paragraphs or tables
At the moment it is more of an experiment but I am curious to see how far I can go in automating Office products in this way.
April 30, 2017 at 3:31 pm
kevaburg - Sunday, April 30, 2017 1:48 AMJeff Moden - Saturday, April 29, 2017 5:02 PMkevaburg - Saturday, April 29, 2017 11:19 AMSo, I have altered the BCP export to include elements and the root element:
bcp"select dbname from dbo.tbl_databasesizes order by dbname asc for xml auto, elements, root('DBList')"
queryout "c:\Backups\test.xml"
-T -S.
-dutilitydb
-w -k
-ec:\Backups\test_errors.txt"I can now Create a new XML Mapping object (see attachment) but now don't know what to do with it.... 🙁
Maybe "merge fields" in Word? You'd have a "template" where it would do the merge as soon as the document opened in an "auto-update" fashion.
Shifting gears a bit, why "Word"? Are you trying to create some form of server documentation?
The aim is indeed to automate documentation. I have created a series of queries that extract information from the instance and associated databases and this should be imported into a prepared template with a single click that can be created in the shortest time possible.
Although quite a lot can be done with the MAP Toolkit or the SSMA or SSDM Reports, I want to be able to create documentation of a high quality that can be tailored and personalised. The process should be:
1. Run a batch file with the BCP statements on the server to be documented to create the XML files
2. Copy the folder with the XML files to a host with Microsoft Word installed
3. Select the template to be used
4. Create a copy of the template and import the XML files into the relevant paragraphs or tablesAt the moment it is more of an experiment but I am curious to see how far I can go in automating Office products in this way.
I know how to do simple form letters using MERGE but not this type of repetitive input where a single document would be created across multiple sections. I'm thinking that a Word Forum would be a better source. I did do a search for how to import XML into Word and it's either all manual or a ton of VBA (so far).
Heh... I'm still amazed that MS hasn't made Office products more compatible with each other never mind with SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2017 at 2:01 am
Jeff Moden - Sunday, April 30, 2017 3:31 PMkevaburg - Sunday, April 30, 2017 1:48 AMJeff Moden - Saturday, April 29, 2017 5:02 PMkevaburg - Saturday, April 29, 2017 11:19 AMSo, I have altered the BCP export to include elements and the root element:
bcp"select dbname from dbo.tbl_databasesizes order by dbname asc for xml auto, elements, root('DBList')"
queryout "c:\Backups\test.xml"
-T -S.
-dutilitydb
-w -k
-ec:\Backups\test_errors.txt"I can now Create a new XML Mapping object (see attachment) but now don't know what to do with it.... 🙁
Maybe "merge fields" in Word? You'd have a "template" where it would do the merge as soon as the document opened in an "auto-update" fashion.
Shifting gears a bit, why "Word"? Are you trying to create some form of server documentation?
The aim is indeed to automate documentation. I have created a series of queries that extract information from the instance and associated databases and this should be imported into a prepared template with a single click that can be created in the shortest time possible.
Although quite a lot can be done with the MAP Toolkit or the SSMA or SSDM Reports, I want to be able to create documentation of a high quality that can be tailored and personalised. The process should be:
1. Run a batch file with the BCP statements on the server to be documented to create the XML files
2. Copy the folder with the XML files to a host with Microsoft Word installed
3. Select the template to be used
4. Create a copy of the template and import the XML files into the relevant paragraphs or tablesAt the moment it is more of an experiment but I am curious to see how far I can go in automating Office products in this way.
I know how to do simple form letters using MERGE but not this type of repetitive input where a single document would be created across multiple sections. I'm thinking that a Word Forum would be a better source. I did do a search for how to import XML into Word and it's either all manual or a ton of VBA (so far).
Heh... I'm still amazed that MS hasn't made Office products more compatible with each other never mind with SQL Server.
Hi Jeff,
thank for the input and yes, I think an Office or Word forum would be more appropriate....I was simply curious if colleagues here had done something like this before.
What irritates me a little is your point about compatibility: In Excel I can create an ODBC or OLEDB connection and extract information direct from the SQL Server with TSQL an display it in a workbook. Now wouldn't that be an attractive feature in Word....?
OK then, off to an Office forum! 🙂
May 1, 2017 at 7:25 am
I think the reason you haven't seen anyone do that kind of thing is fairly simple. While Word has a lot of formatting capability, you still have to find a method to map data from SQL Server to a particular element within the Word document. Just using XML isn't going to produce a consistent result without spending at least as much effort on formatting the XML as it might take to use a VBA script to query SQL Server and then custom populate your document, so I suspect that once folks realize how much work it is to map the XML, they give up and go in the VBA direction. Whether we like it or not XML is not something that maps easily to anything else. Frankly, while it may represent a "standard", and my use of that word is pretty darn loose in this case (my opinion is: calling XML a standard is kind of like calling the random typing of a thousand monkeys, "literature"); dealing with it is anything but. Not going to argue that there aren't use cases for it, but every time I encounter it, there's a TON of programming effort sitting behind the scenes to actually handle it, that few people understand, and even fewer are capable of dealing with.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2017 at 10:08 am
One thought, you could use the Open XML word processing schema, zip it and rename to doc whatever, not a trivial exercise though.
😎
May 2, 2017 at 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?
Regards,
Matt
May 2, 2017 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 2, 2017 at 11:41 am
Eirikur Eiriksson - Monday, May 1, 2017 10:08 AMOne thought, you could use the Open XML word processing schema, zip it and rename to doc whatever, not a trivial exercise though.
😎
It is certainly an idea although there is something else I need to learn. Ho-hum....and the job seemed so easy.....
May 2, 2017 at 11:44 am
Matt Simmons - Tuesday, May 2, 2017 8:32 AMkevabug,
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply