question

ssharma14 avatar image
ssharma14 asked

Convert XML into Word into Excel

I have a Complex type XML File that I need to load in to SSRS and generate report with additional formatting. Since, SSRS did not support the Complex XML as a source, I came up with another alternative as to convert my XML into Words into Excel and then load in to SSRS. Obviously, I don't have much flexibility with this data - but for the time being. Does anyone know how to convert XML into Word into Excel using Script or any other alternative - SO that I can avoid the manual processing.
xmlconvert
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you be more specific on what you want to achieve? I mean what you are doing with the XML and what is the desired output. Also a close description of "Complex type XML" would be helpful.
1 Like 1 ·
@ Pavel - My requirement is to use an xml as a data source and create dataset. However, the issue is the xml is not supported - it has one parent to many child and sub child. I am attaching the xml for your reference as a notepad. When I use the xml as a source, it picks up random data - since this xml is not structured. So I tried to open the xml with excel and using SSRS I was able to create dataset but I can not format individual cell - so its not flexible and its not possible to convert to excel manually everytime. So - If you know any other alternative to achieve this, will be very helpful ! Thx
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Instead of that, what about using OPENXML in a query? SSRS may not be able to directly open the XML document for you, but you can write a T-SQL query that you can then feed to SSRS. [Here's information on OPENXML.][1] [1]: http://technet.microsoft.com/en-us/library/ms186918.aspx
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@ Grant - I don't have access to database - the requirement is to use the xml as a source and present the data with additional formatting in SSRS. Are you suggesting to write the openxml query in SSIS ?
0 Likes 0 ·
No. There are mechanisms to open a file through T-SQL and then you can use the OPENXML statement, all as part of a query. You can use BULK INSERT to open a file: http://technet.microsoft.com/en-us/library/ms188365.aspx There are other methods too. Although, if you really want to convert stuff prior to running the report, SSIS is a good choice.
0 Likes 0 ·
What are the other methods ? Bulk Insert certainly didnt work for my scenario. -- What do you mean by convert stuff prior to running the report ? You mean converting manually ?
0 Likes 0 ·
If you do a search online, there are simply tons of examples, OPENROWSET, setting up OLE DB linked server, sp_oamethod, all sorts of stuff. Here's an article by Phil Factor on it https://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ Another is OPENDATASOURCE. You'll just have to experiment a little. And by "convert stuff" I meant changing the format from XML to something else using SSIS. That's what SSIS is for.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.