Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2007-12-17 : 10:34:13
|
| Hi All,I have SQL Server 2005. I am trying to load a XML in to the database so that I can query and play around with the records in the XML. Its a large file which has around 361971 records so obviously I cannot open it in excel and then DTS in the database. Please look at the following small subset/portion of the XML (I have also put this in the Code Snippet). PSImportData xmlns="urn:kssg.com:PS3"> <Summary userMachine="NB1934" userName="majeedZ" description="Auto generated file" creationTime="2007-12-14T10:45:58" id="f95e597b-32df-4bb4-8d68-5208038de1e9" clientFilePath="C:\Projects\RomPetrol\NEW_DATA_RecievedOn12-11-2007\CSV_XML\SalesHistoryRecievedOn12-11-2007_.xml"> <OwnHistoryTask numSalesRecords="361971" /> </Summary> <Sale price="1.50" sales="17" date="2007-10-22" productId="040002" cost="0" outletId="041BST" /> <Sale price="1.30" sales="3" date="2007-10-22" productId="040002" cost="0" outletId="041MC1" /> <Sale price="1.30" sales="19" date="2007-10-22" productId="040002" cost="0" outletId="041SLT" /> <Sale price="1.30" sales="32" date="2007-10-22" productId="040002" cost="0" outletId="041PT2" />......</PSImportData>In the XML I am not intrest in the summary. All I am intrested is in the highlighted red part above, that is to load the [Sale price], [sales], [date], [productId], [cost], [outletId] as column in a table in the database (<Sale price="1.50" sales="17" date="2007-10-22" productId="040002" cost="0" outletId="041BST" />).What is the easiset way in SQL Server 2005 to do that? The same way we can load CSV's why cant we load XMLs in the database? Are there any built-in add-ons for that? If not, what is an easiest way to load the XML?Thanks a million in adavnce.Zeeshan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-17 : 10:44:17
|
| In SSIS, you can move node values from XML onto a table by using a Data Flow Task using XML source and OLEDB dtsn. |
 |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2007-12-17 : 10:50:37
|
| visakh16,Thanks for the reply. But this information is not enough. I am new in this? Can you please explain?Thanks,Zee |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-17 : 10:54:16
|
| You need to select a Data Flow Task in your SSIS and Inside that add a XML source and map it to the large file and it will automatically take attributs/values from XML file. Then place the OLEDB dstn to map to table and map values to correct columns. This will result in getting XML values onto table fields.Remove summary node from file if you dont want it. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-12-17 : 11:15:56
|
| http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
matty
Posting Yak Master
161 Posts |
Posted - 2007-12-18 : 01:21:51
|
| use OPENXMLhttp://www.informit.com/articles/article.aspx?p=26499&rl=1 |
|
|
|
|
|
|
|
|
How to Import a XML in a SQL Server 2005 database?