InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:20:16 GMT20http://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspx[quote][b]elham_azizi_62 (1/26/2013)[/b][hr]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?[/quote]Make life easy on yourself. Export the data to a file and use Word Mail Merge from there. You'll have nearly instant success.Sat, 26 Jan 2013 17:43:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspx[quote][b]elham_azizi_62 (1/26/2013)[/b][hr]Dear Tavis.We need to have one dataset with more than 1 datatable in word mail merge .is it possible this?[/quote]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 [url=http://msdn.microsoft.com/en-us/library/dd283105(v=sql.105).aspx]Reporting Services export to Word[/url] 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.Sat, 26 Jan 2013 09:32:33 GMTTavis Reddickhttp://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspxDear Tavis.We need to have one dataset with more than 1 datatable in word mail merge .is it possible this?Sat, 26 Jan 2013 06:44:50 GMTelham_azizi_62http://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspxDear 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?Sat, 26 Jan 2013 02:54:41 GMTelham_azizi_62http://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspxthank 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?Fri, 25 Jan 2013 23:23:35 GMTelham_azizi_62http://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspxYou might want to look at the [url=http://www.ecma-international.org/publications/standards/Ecma-376.htm]Office Open XML File Formats[/url] used by Word 2007 onwards, but they are complex.You can read introductory articles such as [url=http://msdn.microsoft.com/en-us/library/office/bb266220(v=office.12).aspx]Walkthrough: Word 2007 XML Format[/url] and [url=http://msdn.microsoft.com/en-us/library/office/gg278313(v=office.14).aspx]Working With Tables (WordprocessingML)[/url].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:[code="sql"]-- 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 variablesDECLARE @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+ WordprocessingMLWITH 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+ PackagingWITH 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"?>*/[/code]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).Wed, 23 Jan 2013 09:41:02 GMTTavis Reddickhttp://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspxthanks 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.thanksWed, 23 Jan 2013 02:40:55 GMTelham_azizi_62http://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspxIf you mean, can you generate a word document from a SQL query, then I guess the short answer is "yes".Try:[code="sql"]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')[/code]which gives you a very simple Word 2003 document in XML:[code="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>[/code] 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:[code="xml"]<?xml version="1.0" encoding="UTF-8"?>[/code]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.Tue, 22 Jan 2013 08:34:17 GMTTavis Reddickhttp://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspxWhat do you mean with "word document style"?Mon, 21 Jan 2013 05:56:43 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1409515-391-1.aspxhello all.is it possible create one xml file with word document style from query result?Mon, 21 Jan 2013 05:48:47 GMTelham_azizi_62