Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML with word document style


XML with word document style

Author
Message
elham_azizi_62
elham_azizi_62
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 820
hello all.
is it possible create one xml file with word document style from query result?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16544 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Tavis Reddick
Tavis Reddick
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 191
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 'oBigGrinocumentProperties/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">
<oBigGrinocumentProperties>
<o:Title>Hello World!</o:Title>
</oBigGrinocumentProperties>
<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
elham_azizi_62
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 820
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
Tavis Reddick
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 191
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
elham_azizi_62
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 820
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
elham_azizi_62
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 820
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
elham_azizi_62
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 820
Dear Tavis.
We need to have one dataset with more than 1 datatable in word mail merge .is it possible this?
Tavis Reddick
Tavis Reddick
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 191
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45447 Visits: 39944
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search