Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML with word document style Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 5:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:25 AM
Points: 143, Visits: 653
hello all.
is it possible create one xml file with word document style from query result?
Post #1409515
Posted Monday, January 21, 2013 5:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
What do you mean with "word document style"?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1409519
Posted Tuesday, January 22, 2013 8:34 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:38 AM
Points: 28, Visits: 130
If you mean, can you generate a word document from a SQL query, then I guess the short answer is "yes".

Try:
WITH XMLNAMESPACES ('http://schemas.microsoft.com/office/word/2003/wordml' AS W,
'urn:schemas-microsoft-com:office:office' AS o)
SELECT 'Hello World!' AS 'o:DocumentProperties/o:Title',
'Hello World!' AS 'W:body/W:p/W:r/W:t'
FOR XML PATH(''), ROOT('W:wordDocument')

which gives you a very simple Word 2003 document in XML:
<W:wordDocument xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:W="http://schemas.microsoft.com/office/word/2003/wordml">
<o:DocumentProperties>
<o:Title>Hello World!</o:Title>
</o:DocumentProperties>
<W:body>
<W:p>
<W:r>
<W:t>Hello World!</W:t>
</W:r>
</W:p>
</W:body>
</W:wordDocument>



which you can save as helloworld.xml and open in Word. If you add an XML declaration as the first line to the above output:
<?xml version="1.0" encoding="UTF-8"?>

you can save as helloworld.doc and Word opens it more naturally.

You can add various Word styles, properties and objects by referring to Microsoft's schema guidance.

However, it might be more useful and flexible to save as an intermediary data format first, then convert/transform it to Word.
Post #1410069
Posted Wednesday, January 23, 2013 2:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:25 AM
Points: 143, Visits: 653
thanks for your reply Tevis.please give me a link for more information about Title,Body,Font,Table,etc in xml from sql server.Meanwhile please give me a sample xml with word document style from "select * from table"'s result for example.thanks
Post #1410423
Posted Wednesday, January 23, 2013 9:41 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:38 AM
Points: 28, Visits: 130
You might want to look at the Office Open XML File Formats used by Word 2007 onwards, but they are complex.

You can read introductory articles such as Walkthrough: Word 2007 XML Format and Working With Tables (WordprocessingML).

I still do not recommend generating WordprocessingML directly from SQL, but you can do it. The following code generates a Word XML document containing a table (with one column coloured) driven by two joined tables:
-- SQL Server 2008 R2 demonstration code to extract data into WordProcessingML for Microsoft Word 2007+
-- Hold standard parts of your Word document package in XML variables
DECLARE @Package1 XML;
SET @Package1 = '
<pkg:part xmlns:pkg="http://schemas.microsoft.com/office/2006/xmlPackage" pkg:name="/_rels/.rels" pkg:contentType="application/vnd.openxmlformats-package.relationships+xml" pkg:padding="512">
<pkg:xmlData>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="word/document.xml"/>
</Relationships>
</pkg:xmlData>
</pkg:part>
';
-- An XML variable to hold the WordProcessingML document before it is inserted into the Package.
DECLARE @Document XML;
-- Declare XML namespaces for Microsoft Word 2007+ WordprocessingML
WITH XMLNAMESPACES (
'http://schemas.openxmlformats.org/markup-compatibility/2006' AS ve,
'urn:schemas-microsoft-com:office:office' AS o,
'http://schemas.openxmlformats.org/officeDocument/2006/relationships' AS r,
'http://schemas.openxmlformats.org/officeDocument/2006/math' AS m,
'urn:schemas-microsoft-com:vml' AS v,
'http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing' AS wp,
'urn:schemas-microsoft-com:office:word' AS w10,
'http://schemas.openxmlformats.org/wordprocessingml/2006/main' AS w,
'http://schemas.openxmlformats.org/wordprocessingml/2006/main' AS w2, -- second reference to namespace allows duplication of elements with same name in query
'http://schemas.microsoft.com/office/word/2006/wordml' AS wne
),
-- Courses belong to a cluster. Represents a data table.
Course AS (
SELECT *
FROM (
VALUES ('HND Art and Design', 1)
,('Woodcraft Workshop', 1)
,('HNC Fitness Health and Exercise ', 2)
,('HND Coaching and Development of Sport', 2)
,('NC Intermediate 2 Administration', 3)
,('HNC Accounting', 3)
,('BA Business Administration', 3)
) AS Course(Title, Cluster)
),
-- Clusters group many courses together, each has a different colour. Represents a data table.
Cluster AS (
SELECT *
FROM (
VALUES (1, 'Creative Industries', '#91278F')
, (2, 'Sport and Fitness', '#A0CBED')
, (3, 'Business and Management', '#00467F')
) AS Cluster(Identifier, Title, Colour)
)
SELECT @Document = (
SELECT (
SELECT Cluster.Title AS 'w:tc/w:p/w:r/w:t',
Cluster.Colour AS 'w:tc/w:tcPr/w:shd/@w:fill',
Course.Title AS 'w2:tc/w2:p/w2:r/w2:t' -- see note above in XMLNAMESPACES
FROM Course
INNER JOIN
Cluster
ON Course.Cluster = Cluster.Identifier
FOR XML PATH('w:tr'), TYPE, ROOT('w:tbl')
) FOR XML PATH('w:body'), ROOT('w:document')
)
;
-- Declare XML namespaces for Microsoft Word 2007+ Packaging
WITH XMLNAMESPACES (
'http://schemas.microsoft.com/office/2006/xmlPackage' AS pkg
)
SELECT @Package1,
'/word/document.xml' AS 'pkg:part/@pkg:name',
'application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml' AS 'pkg:part/@pkg:contentType',
@Document AS 'pkg:part/pkg:xmlData' -- insert the XML document you created above.
FOR XML PATH(''), ROOT('pkg:package')
/*
You can save output as document.xml and open it in Word.
To make Word the default application to open it, you can also manually insert the following as the first two lines of the XML document:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?mso-application progid="Word.Document"?>
*/

It would be easier to generate HTML (which has less complex hierarchies of elements) and open it in Word.

My personal preference is to generate a (preferably standardized) data-oriented XML format from SQL and transform it (using XSLT) into one or more document-oriented formats (HTML, Word, XSL-FO-into-PDF).
Post #1410684
Posted Friday, January 25, 2013 11:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:25 AM
Points: 143, Visits: 653
thank you so much for your reply.
you suggest me that create html from query result and It is easier than you think.please guide me how to do it?
Post #1411981
Posted Saturday, January 26, 2013 2:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:25 AM
Points: 143, Visits: 653
Dear Tavis.
my goal is to create word mail merge with T-sql in a store procedure use of creating xml from query result.
is there Easier way to do this if you know?
Post #1411996
Posted Saturday, January 26, 2013 6:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:25 AM
Points: 143, Visits: 653
Dear Tavis.
We need to have one dataset with more than 1 datatable in word mail merge .is it possible this?
Post #1412021
Posted Saturday, January 26, 2013 9:32 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:38 AM
Points: 28, Visits: 130
elham_azizi_62 (1/26/2013)
Dear Tavis.
We need to have one dataset with more than 1 datatable in word mail merge .is it possible this?

You could start by creating a mock-up (example) Word document with all the elements you need: title, heading, paragraph of text, all the tables each with a row of column headings and two rows from the dataset.

Then you can save this document as XML: Save As > Other Formats > Word XML document.

This will give you the target document to aim for, although it will be complex and have more structure and content than you actually need to generate from your query. You can then think about the best way to create this document, directly (as in the code above), through a simpler format like HTML that Word can open and convert, or through an intermediary format that can be converted into Word by other means, or using some other existing tool or process.

I am assuming that you have looked at other options like Reporting Services export to Word or whatever, and these are not suitable for you.

If you attach a sample Word document showing your tables (column headings and two rows of dummy data each would be fine), we can take a look at it and give more suggestions.
Post #1412042
Posted Saturday, January 26, 2013 5:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 35,371, Visits: 31,915
elham_azizi_62 (1/26/2013)
Dear Tavis.
my goal is to create word mail merge with T-sql in a store procedure use of creating xml from query result.
is there Easier way to do this if you know?


Make life easy on yourself. Export the data to a file and use Word Mail Merge from there. You'll have nearly instant success.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412086
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse