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-09-17 : 10:53:17
|
| Hi All,I have a table called sales with the following field.price (decimal type)outletId (nvarchar type)date (datetime type)sales (decimal type)cost (decimal type)productId (nvarchar type)I want to query all the data from this table and with the results I want to create an XML using the following format.<PSImportData xmlns="urn:zzz.com:PS3"> <Summary userName="Z123" id="2e71818f-5690-4c03-a529-8fb8d28f8806" creationTime="2004-01-12T15:37:13" description="Auto generated file" userMachine="AB123" clientFilePath="C:\Models\08.SalesHistory.xml"> <OwnHistoryTask numSalesRecords="15" /> </Summary> <Sale price="0.79" outletId="00069" date="2004-01-02" sales="3" cost="0.43" productId="10010-1" /> <Sale price="4.59" outletId="00069" date="2004-01-02" sales="1" cost="3.19" productId="10011-1" /> <Sale price="4.29" outletId="00069" date="2004-01-02" sales="3" cost="2.58" productId="10033-1" /> <Sale price="2.89" outletId="00069" date="2004-01-02" sales="3" cost="1.89" productId="10047-1" /> <Sale price="1.99" outletId="00069" date="2004-01-02" sales="2" cost="0.96" productId="10054-1" /> <Sale price="1.09" outletId="00069" date="2004-01-02" sales="4" cost="0.60" productId="10104-1" /> <Sale price="1.79" outletId="00069" date="2004-01-02" sales="1" cost="1.12" productId="10120-1" /> <Sale price="5.79" outletId="00069" date="2004-01-02" sales="2" cost="4.02" productId="10156-1" /> <Sale price="4.79" outletId="00069" date="2004-01-02" sales="1" cost="3.62" productId="10171-1" /> <Sale price="5.99" outletId="00069" date="2004-01-02" sales="1" cost="4.47" productId="10206-1" /> <Sale price="2.29" outletId="00069" date="2004-01-02" sales="4" cost="1.66" productId="10221-1" /> <Sale price="4.99" outletId="00069" date="2004-01-02" sales="1" cost="3.23" productId="10259-1" /> <Sale price="5.99" outletId="00069" date="2004-01-02" sales="1" cost="3.99" productId="10262-1" /> <Sale price="5.99" outletId="00069" date="2004-01-02" sales="2" cost="3.99" productId="10263-1" /> <Sale price="3.59" outletId="00069" date="2004-01-02" sales="4" cost="1.95" productId="10265-1" /></PSImportData>I have created a screenshot using the above code at:[url]http://www.flickr.com/photos/7714646@N07/3928333851/sizes/o/[/url]In the screenshot, notice that the red part of the XML always remain same. It is only the black code in between which will change based on the records in the table. I want to generate those black code (one line per row).How to do this and save the result as .XML? I prefer SSIS, but I am open to do that in T-SQL. Please suggest ASAP.Zee |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-09-17 : 11:19:31
|
| Look in Books Online under "FOR XML EXPLICIT", you should be able to use that to get the format you want. |
 |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-09-17 : 15:24:22
|
| I am a beginner. I start looking FOR XML explicit. It is top of my head. Can someone provide me with a start? Please. Is that the only option? |
|
|
|
|
|
|
Creating XML using records in a table?