Create an XML file from a stored procedure

I'm trying to create a temp xml file to to convert it eventually into an excel file.

using C#

I am calling a stored procedure and from that data retrieved I want to write it in xml format.

I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)

I am new to using xml, so any help would be great..

Can I write an xml file by using a streamWriter or writing all bytes?

Thanks,

kt

You haven't mentioned if you are using SQL Server 2000 or 2005.
If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.

These will return for you the results in the XML format you want and you might not really need to format it manually using C#.

Hope that helps!
|||

I'm using SQL Server 2005...

You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?

sql

Create an XML file from a stored procedure

I'm trying to create a temp xml file to to convert it eventually into an excel file.

using C#

I am calling a stored procedure and from that data retrieved I want to write it in xml format.

I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)

I am new to using xml, so any help would be great..

Can I write an xml file by using a streamWriter or writing all bytes?

Thanks,

kt

You haven't mentioned if you are using SQL Server 2000 or 2005.
If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.

These will return for you the results in the XML format you want and you might not really need to format it manually using C#.

Hope that helps!
|||

I'm using SQL Server 2005...

You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?

CREATE a Temp Table via "EXEC (@SQL)"

I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:

--------------------------
-- Get all SubquestionIDs for this concept
--------------------------
DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR
SELECT QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
FROM RotationMaster AS RM
INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation
INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber
INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumber
WHERE RM.Study = @.Study
GROUP BY QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
HAVING QGDM.SubquestionID <> 0

--------------------------
-- Dynamically create a Temp Table to store the data, simulating a pivot table
--------------------------
SET @.Count = 2
SET @.SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'

OPEN curStudySubquestions
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = @.SQL + ', Col' + CAST(@.Count AS VARCHAR(5)) + ' VARCHAR(10)'
SET @.Count = @.Count + 1
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
END
SET @.SQL = @.SQL + ', ShowOrder SMALLINT)'

CLOSE curStudySubquestions
PRINT 'Create Table SQL:'
PRINT @.SQL
EXEC (@.SQL)
SET @.ErrNum = @.@.ERROR
IF (@.ErrNum <> 0)
BEGIN
PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!'
RETURN
END
PRINT '*** Table Created ***'

-- Test that the table was created
SELECT *, 'TEST' AS AnyField FROM #AllSubquestions

The line PRINT @.SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):

CREATE TABLE #AllSubquestions
(Col1 VARCHAR(100),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10),
Col6 VARCHAR(10),
Col7 VARCHAR(10),
ShowOrder SMALLINT)

However, the SELECT statement to test the creation of the table produces this error:

*** Table Created ***
Server: Msg 208, Level 16, State 1, Procedure
sp_SLIDE_CONCEPT_AllSubquestions, Line 73
Invalid object name '#AllSubquestions'.

It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?The "select *" is outside of the transaction that created the temp table. The way you have written the proc, the temp table disappears at the conclusion of the exec(@.sql).

I would suggest you create a unique static table name (not a temp table) that you feed to the exec sql, then reference the table later as needed. The do the right thing and clean-up after yourself before you exit the sproc.|||The problem is that this temp table will have a variable number of fields when it is run, so a static table won't work. It would be messy to figure out which fields contain data. Oh, and that is only a part of the SP, I clean up properly :)

What I tried was creating "##Allsubquestions", and the table remains in scope for the rest of the SP.

Maybe, though, there is a better way of doing this? Suppose this study has 4 questions, repeating once per Concept. I need the data in this temp table like this:

Col1 Col2 (Question1) Col3 (Question2) Col4 (Question3) Col5 (Question4) ShowOrder
-------------------------------------
Concept 1 25 86 15 47 1
Concept 2 35 89 54 72 2
Concept 3 69 17 48 36 3
Norm A 50 40 40 30 100
Norm B 54 38 42 36 101

This SP will return a recordset to a generic routine that will populate a MS Graph in a PowerPoint presentation, so I need return a dataset that is formatted where I can just load the Graph's Datasheet object from the SP result set. What will change from study to study is the number of questions. This example has four. There may be 8, 10, or any other number.

Create a Temp Table

If I am going to create a temp table and insert data into it, would I
be better of creating the indexes after I do the insert?
Does it matter?If the amount of rows to be inserted is huge, sure. Better to test it.
AMB
"Bob" wrote:

> If I am going to create a temp table and insert data into it, would I
> be better of creating the indexes after I do the insert?
> Does it matter?
>

create a function

hi, I try to do insert a stored procedure's result into a temp table,
then I try to put it inside the function, but it doesn't work.
The reason I want to do this is because we have 200 stored procedures which
need to reuse the same code,
so by putting these logics into a function, the code would be cleaner..
How can I get around this?
CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
@.LId INT)
RETURNS TABLE
AS
CREATE TABLE #tempList
(
UId INT,
CompanyId NVARCHAR(10),
LId INT,
CompanyName NVARCHAR(50)
.....
)
INSERT INTO #tempList
Exec GetListInfo @.UId, @.CompanyId ,@.LId
RETURN (SELECT * FROM #tempList)You can't execute a stored proc within a function -- you'll have to do this
within another stored proc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uxpTfkAIFHA.2936@.TK2MSFTNGP15.phx.gbl...
> hi, I try to do insert a stored procedure's result into a temp table,
> then I try to put it inside the function, but it doesn't work.
> The reason I want to do this is because we have 200 stored procedures
which
> need to reuse the same code,
> so by putting these logics into a function, the code would be cleaner..
> How can I get around this?
>
> CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
> @.LId INT)
> RETURNS TABLE
> AS
> CREATE TABLE #tempList
> (
> UId INT,
> CompanyId NVARCHAR(10),
> LId INT,
> CompanyName NVARCHAR(50)
> .....
> )
> INSERT INTO #tempList
> Exec GetListInfo @.UId, @.CompanyId ,@.LId
>
> RETURN (SELECT * FROM #tempList)
>|||You can't call an stored procedure from within a function. Put the SP
logic into the body of the function instead if you can.
David Portas
SQL Server MVP
--|||you can't put temp table in the function also.
correct me if I'm wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23bBHPtAIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> you can't put temp table in the function also.
> correct me if I'm wrong.
You can use a table variable in a function -- logically the same thing.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||That's correct. However you can use table variables in a function.
Table variables provide a lot of the same functionality as temp tables
but you can't insert the result of an SP into a table variable even if
it was possible to execute the SP.
David Portas
SQL Server MVP
--|||Hi David,
Well, I know that will work, but if I make a change in the logics in
the future,
then I will have to change all 200 procedures. I try to avoid this problem.
If function works, then all I need to do is to change the logic in the
function. Too bad there is no way to get around it.
=(
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||Oh never mind.. ignore the previous question,
I misunderstood it. you're right, put the sp logic into function will work.
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uOGLGtBIFHA.572@.tk2msftngp13.phx.gbl...
> Hi David,
> Well, I know that will work, but if I make a change in the logics
in
> the future,
> then I will have to change all 200 procedures. I try to avoid this
problem.
> If function works, then all I need to do is to change the logic in the
> function. Too bad there is no way to get around it.
> =(
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
>

Counting rows

Hi,

I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.

I figured I could add a column that contains that number. But how will I get it there?

UPDATE [_temp] T1
SET T1.SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T1.SERIAL = T2.SERIAL)

Doesn't work. Any ideas?that should work.

I think we're both missing something really obvious...

I don't start my temp table names with the _ symbol...

I usually use the # sign.

Originally posted by oneleg_theone
Hi,

I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.

I figured I could add a column that contains that number. But how will I get it there?

UPDATE [_temp] T1
SET T1.SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T1.SERIAL = T2.SERIAL)

Doesn't work. Any ideas?|||My version of mssql does not allow table aliasses on updates.|||The tables names are example only, they are named something else. Too bad i named them "temp" in this example...

I already have some sql in a sp that updates tables using aliases so it's not that either... I thought. The first table must be without alias, then it works! But delivers the wrong result... It gives me total rowcount not the number of rows with the same serial..

Working query delivering wrong result:

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE SERIAL = T2.SERIAL)|||Trial and error provided the answer...

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T2.SERIAL = [_temp].SERIAL)

Now gives me the result I want.. :-D|||dang.

like I said, something obvious - can't use alias name on update.

Originally posted by oneleg_theone
Trial and error provided the answer...

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T2.SERIAL = [_temp].SERIAL)

Now gives me the result I want.. :-D