FUNNY Problem "FOR XML EXPLICIT"
-
Hi All,
I am facing a funny problem while using "For Xml Explicit" in my stored procedure. The procedure make joins in 3 tables and then returns me an xml.
The problem:
The procedure executes perfectly fine, but some times it stops giving back the values in XML. It just returns empty tags.
When I recreate the stored procedure again it starts working and gives the data correctly.
I found that we should use WITH RECOMPLIE option in stored procedure to resolve this issue, but it does not seem feasible to me, because in actual env. this procedure will be referenced by many users and will create performance issue.
Help in this issue will be higly appreciated.
Thanks,
Gaurav
-
Looks like the data was cached. Try runing dbcc dropcleanbuffers first. Also you can just run a simple query without "for xml explicit" and see if you still get an empty result. If you can post the query, I can take a look at it for you.
Shawn Shi
-
Hi Shawn,
Thanks for your reply. The thing is that i am using multilpe connections to execute this procedure. And "dbcc dropcleanbuffers" is not what i want, i will need to execute this query whenever the procedure fails to return the response
also, the query runs successfully with less stress using "For XML Explicit". The following is the query which i am using:
(1) Trade status is the value which i suppliy to SP.
(2) XML format is:-->
</trades>
<trade>abc1</trade>
<trade>abc2</trade>
<trade>abc3</trade>
</trades>
(3) The Query :
IF (@includeDefaultTrade = 1)
BEGIN
SELECT 1 AS TAG,
NULL AS PARENT,
NULL AS [Trades!1],
NULL AS [Trade!2]
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
NULL AS [Trades!1],
Trades.name AS [Trade!2]
FROM Trade AS Trades
INNER JOIN ContractTrade AS tContractTrade
ON Trades.id = tContractTrade.Trade_id
INNER JOIN Contract AS tContract
ON tContractTrade.contract_id = tContract.id
WHERE tContract.contract_id = @ginniContractId
AND Trades.Trade_status = @TradeStatus
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
NULL AS [Trades!1],
tContract.default_Trade AS [Trade!2]
FROM Contract AS tContract
WHERE tContract.contract_id = @ginniContractId
FOR XML EXPLICIT
END
ELSE
BEGIN
SELECT 1 AS TAG,
NULL AS PARENT,
NULL AS [Trades!1],
NULL AS [Trade!2]
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
NULL AS [Trades!1],
Trades.name AS [Trade!2]
FROM Trade AS Trades
INNER JOIN ContractTrade AS tContractTrade
ON Trades.id = tContractTrade.Trade_id
INNER JOIN Contract AS tContract
ON tContractTrade.contract_id = tContract.id
WHERE tContract.contract_id = @ginniContractId
AND Trades.Trade_status = @TradeStatus
FOR XML EXPLICIT
END
RETURN
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply