August 6, 2009 at 4:09 am
I have done some 'Googleing' into this area about "SELECT FOR XML" but dont fully understand it.
What I Need
I have a table with my SQL2005 DB which contains data
I would like to create an SSIS package which outputs the data into an XML file.
Is there any good articles I can read or can anyone explian the process?
Thanks
August 6, 2009 at 9:23 am
This should help: http://blogs.conchango.com/jamiethomson/archive/2006/07/11/4209.aspx
August 6, 2009 at 9:46 am
Is this the only way it can be done?
August 6, 2009 at 9:54 am
Of course not!
I suppose after re-reading your original post, you did say that you're data is comming from a SQL Server DB. You can write a stored procedure that uses the FOR XML option to create the XML and output it back to the SSIS package as varchar(max). Use the SP in a data flow in the OLE DB Source. Send your output to a file and slap the .xml extension on it. I've got a package that does this and it works great.
August 6, 2009 at 10:08 am
Right, I lost you after the first stage...
Like I said Iv never done this before and iv only used SSIS a number of time, hense I was asking for a nice light read.... (i.e. Dummys guide)
Can you explain in a bit more detials please...
My table holds data like:
name, mobile number, home number, first line add, zipcode
Thanks a lot!!!!
August 6, 2009 at 10:35 am
Post your table DDL along with a few rows of sample data (readily consumable as described here: http://www.sqlservercentral.com/articles/Best+Practices/61537/.
August 7, 2009 at 3:51 am
Table:
USE [UK_DBA_Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContactTable](
[Date] [char](11) NULL,
[Job] [varchar](100) NULL,
[Telephone] [varchar](100) NULL,
[Company] [nvarchar](255) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Results:
2009-01-01 Site Manager 0208 7845111 SoftCom
2009-01-01 Stock Room 0208 7845112 SoftCom
2009-01-01 Area Manager 0208 7845113 FilmView
2009-01-01 Stock *** 0208 7845114 OfCom
2009-01-01 Control 0208 7845115 TecNet
This is what I kind of need in the XML file:
The header:
August 7, 2009 at 2:48 pm
Your Xml example did not make it into your post! You may want to use the Preview option next time to ensure your post looks like you want it to. You need to use the code="xml" tag to post XML.
Meanwhile, here's an example of how to use the FOR XML option:
DECLARE @ContactTable TABLE(
Date char(11) NULL,
Job varchar(100) NULL,
Telephone varchar(100) NULL,
Company nvarchar(255) NULL
)
INSERT INTO @ContactTable
SELECT '2009-01-01', 'Site Manager', '0208 7845111', 'SoftCom' UNION ALL
SELECT '2009-01-01', 'Stock Room', '0208 7845112', 'SoftCom' UNION ALL
SELECT '2009-01-01', 'Area Manager', '0208 7845113', 'FilmView' UNION ALL
SELECT '2009-01-01', 'Stock ***', '0208 7845114', 'OfCom' UNION ALL
SELECT '2009-01-01', 'Control', '0208 7845115', 'TecNet'
SELECT Company,
Job,
Telephone,
Date
FROM @ContactTable Contact
FOR XML AUTO, ELEMENTS, ROOT('Contacts')
August 10, 2009 at 1:25 am
Sorry about the formatting of my last post.
I have run the code above and this does create the following:
SoftCom
Site Manager
0208 7845111
2009-01-01
SoftCom
Stock Room
0208 7845112
2009-01-01
FilmView
Area Manager
0208 7845113
2009-01-01
OfCom
Stock ***
0208 7845114
2009-01-01
TecNet
Control
0208 7845115
2009-01-01
Does this code need to be used as part of an SP which the SSIS will call?
Sorry not sure how to use this code....
Thanks
August 10, 2009 at 10:37 am
Does the SELECT.....FOR XML statement create the XML in the format that you were looking for? If so, use the SELECT statement inside a stored procedure. The XML data will show up in the pipeline as DT_IMAGE data type. The best way that I've found to convert this is to have the SP return the XML data as varchar(max). This way, the pipeline sees the data as DT_TEXT and you can easily map that to the output column in the flat file destination.
August 10, 2009 at 10:17 pm
So what format are you looking for and what do you want to do with it?
I just wrote a custom dataflow task that does something like this, and it could probably be modified to your purposes..
The component I wrote takes all selected columns and assembles up a row of XML.. I had planned on posting it up on Codeplex..
What I hadn't done was assemble up a bunch of rows into a single big chunk of data..
CEWII
August 11, 2009 at 12:44 am
Basically I I have the following data in a SQL2005 DB:
Job ContactNo Position
Site Manager 0208 7845111 SoftCom
Stock Room 0208 7845112 SoftCom
Area Manager 0208 7845113 FilmView
Stock 0208 7845114 OfCom
Control 0208 7845115 TecNet
I want to output this data to a XML file
I would like the header to be in effect:
The code published by John Rowan (Posted 8/7/2009 9:48:33 PM above) does give vaild output but does not contacin the XML header.
August 11, 2009 at 9:43 am
Pretty simple then, since you'll be returning the results from the SP via varchar(max) data type, add the XML header info to the top of the varchar string and away you go. A simple source component calling your SP, a flat file destination connection manager, and a flat file destination component.
August 11, 2009 at 10:47 am
I would tend to agree..
CEWII
August 12, 2009 at 12:35 am
Thanks Guys,
But im not sure what you mean by:
"Add the XML header info to the top of the varchar string"
Can I have an example please?
regards,
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply