Create XML Files Out Of SQL Server With SSIS And FOR XML Syntax
So you want to spit out some XML from SQL Server into a file, how can you do that? There are a couple of ways, I will show you how you can do it with SSIS. In the SSIS package you need an Execute SQL Task and a Script Task.
Let's get started
First create and populate these two tables in your database
- create table Artist (ArtistID int primary key not null,
- ArtistName ))
- go
- create table Album(AlbumID int primary key not null,
- ArtistID int not null,
- AlbumName ) not null,
- YearReleased smallint not null)
- go
- ,'Pink Floyd')
- ,'Incubus')
- ,'Prince')
- ,,)
- ,,)
- ,,)
- ,,)
- ,,)
- ,,)
- ,,)
Now create this proc
- create proc prMusicCollectionXML
- as
- declare @XmlOutput xml
- set @XmlOutput = (select ArtistName,AlbumName,YearReleased from Album
- join Artist on Album.ArtistID = Artist.ArtistID
- FOR XML AUTO, ROOT('MusicCollection'), ELEMENTS)
- select @XmlOutput
- go
After executing the proc
- exec prMusicCollectionXML
you will see the following output
- <MusicCollection>
- <Artist>
- <ArtistName>Pink Floyd</ArtistName>
- <Album>
- <AlbumName>Wish You Were Here</AlbumName>
- <YearReleased>1975</YearReleased>
- </Album>
- <Album>
- <AlbumName>The Wall</AlbumName>
- <YearReleased>1979</YearReleased>
- </Album>
- </Artist>
- <Artist>
- <ArtistName>Prince</ArtistName>
- <Album>
- <AlbumName>Purple Rain</AlbumName>
- <YearReleased>1984</YearReleased>
- </Album>
- <Album>
- <AlbumName>Lotusflow3r</AlbumName>
- <YearReleased>2009</YearReleased>
- </Album>
- <Album>
- <AlbumName>1999</AlbumName>
- <YearReleased>1982</YearReleased>
- </Album>
- </Artist>
- <Artist>
- <ArtistName>Incubus</ArtistName>
- <Album>
- <AlbumName>Morning View</AlbumName>
- <YearReleased>2001</YearReleased>
- </Album>
- <Album>
- <AlbumName>Light Grenades</AlbumName>
- <YearReleased>2006</YearReleased>
- </Album>
- </Artist>
- </MusicCollection>
So far so good, so how do we dump that data into a file? Create a new SSIS package add an ADO.NET Connection, name it AdventureWorksConnection Drop an Execute SQL Task onto your control flow and modify the properties so it looks like this
On the add a result set by clicking on the add button, change the variable name to User::XMLOutput if it is not already like that
Note!!! In SSIS 2008 this variable should be already created otherwise it will fail
Now execute the package. You will be greeted with the following message: Error: 0xC00291E3 at Execute SQL Task, Execute SQL Task: The result binding name must be set to zero for full result set and XML results. Task failed: Execute SQL Task In order to fix that, change the Result Name property from NewresultName to 0, now run it again and it should execute successfully.
Our next step will be to write this XML to a file. Add a Script Task to the package,double click the Script Task,click on script and type XMLOutput into the property of ReadWriteVariables. It should look like the image below
Click the Design Script button, this will open up a code window, replace all the code you see with this
- ' Microsoft SQL Server Integration Services Script Task
- ' Write scripts using Microsoft Visual Basic
- ' The ScriptMain class is the entry point of the Script Task.
- Imports System
- Imports System.Data
- Imports System.Math
- Imports Microsoft.SqlServer.Dts.Runtime
- Public Class ScriptMain
- Public Sub Main()
- '
- ' Add your code here
- '
- Dim XMLString As String = " "
- XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "")
- XMLString = "<?xml version=""1.0"" ?>" + XMLString
- GenerateXmlFile("C:\\MusicCollection.xml", XMLString)
- End Sub
- Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String)
- Dim objStreamWriter As IO.StreamWriter
- Try
- objStreamWriter = New IO.StreamWriter(filePath)
- objStreamWriter.Write(fileContents)
- objStreamWriter.Close()
- Catch Excep As Exception
- MsgBox(Excep.Message)
- End Try
- Dts.TaskResult = Dts.Results.Success
- End Sub
- End Class
SSIS 2008 requires a code change Here is what the code should look like if you are running SSIS 2008
- ' Microsoft SQL Server Integration Services Script Task
- ' Write scripts using Microsoft Visual Basic 2008.
- ' The ScriptMain is the entry point class of the script.
- Imports System
- Imports System.Data
- Imports System.Math
- Imports Microsoft.SqlServer.Dts.Runtime
- <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
- <System.CLSCompliantAttribute(False)> _
- Partial Public Class ScriptMain
- Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
- Enum ScriptResults
- Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
- Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
- End Enum
- Public Sub Main()
- '
- ' Add your code here
- '
- Dim XMLString As String = " "
- XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "")
- XMLString = "<?xml version=""1.0"" ?>" + XMLString
- GenerateXmlFile("C:\\MusicCollection.xml", XMLString)
- End Sub
- Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String)
- Dim objStreamWriter As IO.StreamWriter
- Try
- objStreamWriter = New IO.StreamWriter(filePath)
- objStreamWriter.Write(fileContents)
- objStreamWriter.Close()
- Catch Excep As Exception
- MsgBox(Excep.Message)
- End Try
- Dts.TaskResult = ScriptResults.Success
- End Sub
- End Class
There are a couple of things you need to know, the XML will be generated inside a <ROOT> tag, I am stripping that out on line 23 of the code, on line 24 I am adding <?xml version="1.0" ?> to the file. Line 26 has the location where the file will be written, right now it is C:\MusicCollection.xml but you can modify that.
So now we are all done with this. It is time to run this package. Run the package and you should see that file has been created.
最新文章
- EXCEL设置选中单元格样式
- <;<;你最喜欢做什么--兴趣问题清单>;>;总结
- Perl重命名当前目录下的文件
- EasyUI组件(窗口组件)
- Python+Selenium进行UI自动化测试项目中,常用的小技巧4:日志打印,longging模块(控制台和文件同时输出)
- sphinx增量索引
- Ubuntu 14.04 部署 CEPH集群
- Chrome开发者工具详解(1):Elements、Console、Sources面板
- 加载php_curl.dll和php_openssl.dll出错原因及解决办法
- Oracle Semaphore Management in UNIX Administration
- Eclipse编辑器样式修改
- 显示hibernate的sql语句
- HTML标签自定义属性
- git代码回滚
- win2012 配置wamp的若干错误
- update_engine-整体结构(三)
- 新手详解JAVA+数据库+JSP完成简单页面
- Java开源内容管理CMS系统J4CMS集成到JTM
- metal的gpu query
- 利用Serv-U搭建FTP服务器