XML Output

[Subodh]

I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a
normal Select statement, depending upon the input parameter specified
(@xmflag). The default of @xmflag = 0 which means non-XML output. Now
Proc1 is calling Proc2 and inserting the output of Proc2 in a temp
table without specifing the @xmflag parameter. The message coming is
"The FOR XML clause is not allowed in a INSERT statement". I don't
understand why this msg is coming when the Select statement FOR XML is
not executing.
Anybody knows the reason?

Thanks in advance.

Subodh

[Erland Sommarskog]

Subodh (sg****@agline.on.ca) writes:

I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a
normal Select statement, depending upon the input parameter specified
(@xmflag). The default of @xmflag = 0 which means non-XML output. Now
Proc1 is calling Proc2 and inserting the output of Proc2 in a temp
table without specifing the @xmflag parameter. The message coming is
"The FOR XML clause is not allowed in a INSERT statement". I don't
understand why this msg is coming when the Select statement FOR XML is
not executing.
Anybody knows the reason?

Probably the cause is that the FOR XML clause is detected when the
procedure is invoked. SQL Server cannot know off-hand whether that
FOR XML will be executed or not, so it will assume that it has.

Move the XML bit of Proc2 to a sub-procedure which you call if @xmlflag is
set. I would expect that to do it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Subodh]

I cannot write the FOR XML RAW part in another sub-SP as the data is
lying in a temp table and I cannot use global temp table. Is there any
setting in SQL Server 2000 which can override this problem. Sometimes
Proc1 executes well but starts giving error msg all of a sudden. Any
thoughts?

Subodh

Probably the cause is that the FOR XML clause is detected when the

procedure is invoked. SQL Server cannot know off-hand whether that
FOR XML will be executed or not, so it will assume that it has.

Move the XML bit of Proc2 to a sub-procedure which you call if @xmlflag is
set. I would expect that to do it. Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>... Subodh (sg****@agline.on.ca) writes:

I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a
normal Select statement, depending upon the input parameter specified
(@xmflag). The default of @xmflag = 0 which means non-XML output. Now
Proc1 is calling Proc2 and inserting the output of Proc2 in a temp
table without specifing the @xmflag parameter. The message coming is
"The FOR XML clause is not allowed in a INSERT statement". I don't
understand why this msg is coming when the Select statement FOR XML is
not executing.
Anybody knows the reason?

[John Bell]

Hi

I would look at re-designing this, as FOR XML can not be used in a derived
table of an INSERT statement, even if you move it into a procedure used in
the execute clause it will still fail.

John

"Subodh" <sg****@agline.on.ca> wrote in message
news:90**************************@posting.google.c om...

I cannot write the FOR XML RAW part in another sub-SP as the data is
lying in a temp table and I cannot use global temp table. Is there any
setting in SQL Server 2000 which can override this problem. Sometimes
Proc1 executes well but starts giving error msg all of a sudden. Any
thoughts?

Subodh

Probably the cause is that the FOR XML clause is detected when the

procedure is invoked. SQL Server cannot know off-hand whether that
FOR XML will be executed or not, so it will assume that it has.

Move the XML bit of Proc2 to a sub-procedure which you call if @xmlflag is set. I would expect that to do it.

Erland Sommarskog <es****@sommarskog.se> wrote in message

news:<Xn*********************@127.0.0.1>...

Subodh (sg****@agline.on.ca) writes:

I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a
normal Select statement, depending upon the input parameter specified
(@xmflag). The default of @xmflag = 0 which means non-XML output. Now
Proc1 is calling Proc2 and inserting the output of Proc2 in a temp
table without specifing the @xmflag parameter. The message coming is
"The FOR XML clause is not allowed in a INSERT statement". I don't
understand why this msg is coming when the Select statement FOR XML is
not executing.
Anybody knows the reason?

[Erland Sommarskog]

Subodh (sg****@agline.on.ca) writes:

I cannot write the FOR XML RAW part in another sub-SP as the data is
lying in a temp table and I cannot use global temp table. Is there any
setting in SQL Server 2000 which can override this problem. Sometimes
Proc1 executes well but starts giving error msg all of a sudden. Any
thoughts?

No, there is no setting you can change.

I guess the occasion when Proc1 executes well, is when there already
is a plan in the cache.

Anyway, I have an article on my web site discusses alternatives to
INSERT-EXEC, you may get some ideas there, see
http://www.sommarskog.se/share_data.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp