Automate XML file build from SQL stored proc
Posted on 2008-06-23
Can anyone help with automating the build of an xml file? Essentially, I can execute a query (see below) and view desired results in SQL Server Management Studio, then obviously copy and paste to a text file. I need to somehow replace these last two steps, by either modifying the query to write to a desired XML file or building a DTX package to accomplish the steps (or other?).
SELECT [item-no] as "Style",
(select offer as "SourceCode" from zztemp3 z where z.[item-no] = c.[item-no] for xml path (''), type) as "SourceCodeList"
FROM (select distinct [item-no] from zztemp3) c
FOR XML PATH ('ITEM'), Root ('ITEMS')
We are using SQL 2005. Thanks in advance --Karen