May 29, 2009 at 4:00 pm
I am trying to import data from saved html files (pages) which originated from SSRS which were sent from a former client months ago. (So the answer is no if you suggest getting the data from it's original source 🙂 )
I know (or think rather) that they can be imported as XML data. The problem is, I have no idea how to go about this. I have no experience with XML data at all. Does anyone have a good link to a detailed step by step way of importing xml from html or does anyone have experience with doing this? I appreciate the help in advance! File is attached as a word doc.
Rob
May 30, 2009 at 3:10 pm
Hi Rob,
the structure of the word file -even if it's saved in XML format- wouldn't let you extract the data into SQL Server in a format you'd probably expect or even a format that might be helpful in any way...
This is caused by the structure of the document:
1) There is no link between the search# (e.g. Search#1: JohnDoe) and the related data.
2) There is no difference between header and data.
3) Header rows are not consistent (e.g. summary header is split into three rows)
4) Storage of search criteria is inconsistent (e.g. Search #4 : (McDade.Herbert) is stored in multiple rows.
5) and so on...
I tried to open your document in Excel (which just worked fine)
In Excel you could add some macros/functions to add the missing information to the tables you'd like to import (e.g. which rows need to be imported into SQL Server - since I don't think that you'd like to import the Result sections showing the totals since you can easily recalculate them once you got the data back into the server).
If you need some help to get the data from your document via Excel back into SS2K* let me know.
In this case I would need to know which sections and specific values you'd like to reimport.
Via Excel it looks like a fairly easy job whereas XML seems to be almost impossible...
Regarding your general question on how to use xml in SS2K5 I recommend you look to up XQuery in BOL together with the XML Workshop series by J. Sebastian (search for "XML Workshop"here at SSC), whereas Jacobs series is much more understandable since he found an excellent way of showing the usage of xml with real world scenarios.
May 31, 2009 at 8:07 am
Is it a requirement to save your data in XML format or you have the option to save your data in any other way in SQL table. Say, as an BLOB data?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply