How can I Export SQL stored procedure to xml?

Morpheus

Has anyone done this?

I know that in SQL 2000 i could try a dts package, however in SQL 2005 you need to use SSIS which is new tech to me, has anyone done this? if so how did you go about it?

all help is greatly appreciated

Anyone know some really good SQL forums where responses are usually quite quick?

amen

what are you trying to do?
Do you want to call an SP that gives the result set in XML
or have a DTS/SSIS package set up or
a DTS/SSIS package that calls an SP that gives back XML?

If you want an SP to return SQL
in your select statement you could use
FOR XML AUTO

Seth Brundle

does the 'FOR XML' clause within SQL not work?
http://***n.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

Morpheus

I need an SSIS package as it must run automatically every night. It must be in SSIS after further discussion this morning with a fellow developer revealed that SSIS is the 2005 version of DTS (see, told you it was new to me!).

I can use either an SQL statement that simply "outs" xml or (I think) I can create a flat file xml destination?

Either way, my xml must be in a certain format and dumped in a location as it must be picked up and verified by another application.

amen

There are a few ways you can do this
You could try method one below.
This will produce some XML with the columns as attributes of the node. Personally I don't like
adding attributes but thats me !

On the other hand method two will produce some much nicer xml and is what I would normally use.

I know the tables below are a bit crappy but they are only used as an example

Method 1
Say you have two tables
set nocount on
create Table #A(
ProductID INT,
ProductDescription VARCHAR(25)
)
create Table #B
(
OrderID INT,
Quantity INT,
ProductID INT
)
INSERT #A select 1,'A Nice Table'
INSERT #A select 2,'A Nice Chair'

INSERT #B select 1,2,1
INSERT #B select 1,2,2
INSERT #B select 2,3,1

SELECT
1 AS 'Tag',
Null AS 'Parent',
#B.OrderID AS 'ORDERS!1!OrderID',
#B.Quantity AS 'ORDERS!1!Quantity',
#A.ProductDescription AS 'ORDERS!1!ProductDescription'
FROM
#A JOIN #B ON #A.ProductID = #B.ProductID
ORDER BY #B.OrderId,#B.ProductID
FOR XML EXPLICIT

DROP TABLE #A
DROP TABLE #B

This should produce the following output
<ORDERS OrderID="1" Quantity="2" ProductDescription="A Nice Table"/>
<ORDERS OrderID="1" Quantity="2" ProductDescription="A Nice Chair"/>
<ORDERS OrderID="2" Quantity="3" ProductDescription="A Nice Table"/>

Method 2
Say you have two tables
set nocount on

create Table #A(
ProductID INT,
ProductDescription VARCHAR(25)
)
create Table #B
(
OrderID INT,
Quantity INT,
ProductID INT
)
INSERT #A select 1,'A Nice Table'
INSERT #A select 2,'A Nice Chair'

INSERT #B select 1,2,1
INSERT #B select 1,2,2
INSERT #B select 2,3,1

SELECT
1 AS 'Tag',
Null AS 'Parent',
#B.OrderID AS 'ORDERS!1!OrderID',
#B.Quantity AS 'ORDERS!1!Quantity',
#A.ProductDescription AS 'ORDERS!1!ProductDescription'
FROM
#A JOIN #B ON #A.ProductID = #B.ProductID
ORDER BY #B.OrderId,#B.ProductID
FOR XML EXPLICIT

SELECT
Orders.OrderID,
Orders.Quantity,
Products.ProductDescription
FROM
#A Products JOIN #B Orders ON Products.ProductID = Orders.ProductID
ORDER BY Orders.OrderId,Orders.ProductID
FOR XML AUTO

DROP TABLE #A
DROP TABLE #B

Morpheus

Does SSIS give you the facility to raise an error on a users pc if the nightly package fails? i know that in DTS you could at least send an email, but can you set it up so as to message a console, say for example i come in tomorrow morning and last nights feed failed and now as i log on, i get a popup message of some sort telling me that this has happened?

amen

I would just uss SSI to send an email
much easier
I suppose you could make it fire a script on failure and put a file somewhere that login script would read but why bother? Its just another point of failure.
email is much nicer