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 - 2009-02-06 : 12:32:22
|
| Hi All,Note:Before I explain my question, please copy the whole sample code (in bold) and paste it notepad and save it as .XML file. Than open it in internet explorer to understand what I am talking about.<?xml version="1.0" standalone="yes"?><!-- Microsoft's standard datatype namespace document is referenced --><!-- in order to provide complex values in Base64 format. --><PSImportData xmlns="urn:zzz.com:PS3"> <Summary id="ID" description="Description" userName="Autogenerated" userMachine="GenerationMachine" creationTime="1/27/2009" clientFilePath="c:\"> </Summary> <PriceZoneRef id="900"> <ProductRef id="4"> <CurrentPrice price="9.99" effectiveDate="8/18/2008"/> </ProductRef> </PriceZoneRef> <PriceZoneRef id="900"> <ProductRef id="9"> <CurrentPrice price="10.49" effectiveDate="2/25/2008"/> </ProductRef> </PriceZoneRef></PSImportData>Question:I want to load this XML file in a SQL Server 2005 database table. I am OK to load all columns in a SQL server table but I must need the following 4 columns:PriceZoneRef idProductRef idCurrentPrice priceeffectiveDateIn a SSIS under the Control flow tab; I have put "Data Flow Task". Under the "Data Flow" tab I am using "XML source" for my XML. But under the XML source editior it is giving me an option to select my "output name" (see the link below for an screenshot). This is wiered and totaly new to me, becasue I have never see this option in an XML Source editor before. I dont want to put a particular output, since it will then get the incomplete data for me, and not all those 4 columns which I am looking for. There's nothing wrong with the XML. And I didn't do the SQL destnation piece since I am stuck at the source piece. Screenshot:http://www.flickr.com/photos/7714646@N07/3258599994/sizes/o/Please suggest me the best way to load those columns from this XML to a table. Thanks for your quick help.Zee |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-06 : 13:02:28
|
| http://support.microsoft.com/kb/316005 |
 |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-02-06 : 13:32:59
|
| sodeep - Its SQL Server 2005; so why use SQLXMLBulkLoad when we have SSIS in SQL Server 2005.Please suggest something in SSIS.Thanks,Zee |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-06 : 13:52:44
|
| http://blogs.msdn.com/mattm/archive/2007/12/11/using-xml-source.aspx |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 09:24:29
|
| this is a nice article! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-07 : 11:06:19
|
quote:
Originally posted by visakh16
this is a nice article!
Yes it is. |
|
|
|
|
|
|
How to load XML through SSIS with all columns?