XML with word document style

Question

XML with word document style

elham_azizi_62

SSCertifiable

Points: 5095
More actions
January 21, 2013 at 5:48 am

#267136

hello all.
is it possible create one xml file with word document style from query result?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

What do you mean with "word document style"?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Tavis Reddick SSC Eights! Points: 817 More actions · Answer 2

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.

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 3

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

Tavis Reddick SSC Eights! Points: 817 More actions · Answer 4

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

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 5

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?

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 6

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?

elham_azizi_62 SSCertifiable Points: 5095 More actions · Answer 7

Dear Tavis.

We need to have one dataset with more than 1 datatable in word mail merge .is it possible this?

Tavis Reddick SSC Eights! Points: 817 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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.

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)