October 26, 2010 at 5:55 am
Hi,
I'm having XML as stored procedure parameter with values like below:
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>
I'm inserting them in a TEMP table and selecting them from temp and copying to my REAL TABLE.
I need to loop through the temp table row count and update table for each value in temp table.
can anyone please help me put about this query..
ALTER PROCEDURE [dbo].[usp_archive_news]
AS
DECLARE @idoc int
DECLARE @doc nvarchar(max)
SET @doc ='
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
--SELECT * INTO #temp
--FROM OPENXML (@idoc, '/NewsDTO',2)
-- WITH (NewsId uniqueidentifier)
SELECT * INTO #temp FROM OPENXML (@idoc, '/NewsDTO/NewsList', 2) WITH (NewsID uniqueidentifier)
Declare @rowCount int
SET @rowCount = (SELECT COUNT(*) FROM #temp)
WHILE (@rowCount >= @rowCount)
BEGIN
Declare @NID uniqueidentifier
SET @NID = (SELECT NewsID from #temp)
Update News set news_isonline='N' where news_id=@NID
SET @rowCount = @rowCount+1
IF @rowCount = @rowCount
PRINT @NID
BREAK;
END
DROP table #temp
October 26, 2010 at 7:48 am
Can you provide more details on what your trying to do?
I get what your code is doing but there are some logical issues with it...
The idea of you wanting to update things row by row is a good sign of heading in the wrong direction...
but with what you provided here is what i got...
An easy way to get around using the XMLPrepare Document is something like this...
(This could remove the need for the #temp table... and just join to the table you need to update with this guy)
DECLARE @XML XML = CONVERT(XML,@doc)
SELECT
x.value('.','uniqueidentifier') AS ElementValue
FROM
@XML.nodes('/*/*') y(x)
I have made some adjustments to your code to get it to execute..
DECLARE @doc NVARCHAR(MAX)
SET @doc ='
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>'
--Create an internal representation of the XML document.
DECLARE @XML AS XML = CONVERT(XML,@doc)
SELECT
ROW_NUMBER() OVER (ORDER BY x.value('.','uniqueidentifier')) AS ROWID, --Will allow you to pull one record at a time
x.value('.','uniqueidentifier') AS NewsID
INTO #temp --This could most likely be avoided
FROM
@XML.nodes('/*/*') y(x)
DECLARE
@RowCount INT = 0 ,
@MaxRowCount INT = 0,
@NID UNIQUEIDENTIFIER
SET @rowCount = (SELECT COUNT(*) FROM #temp)
--Your checking a value against its self? When will RowCount NOT equal RowCount???
WHILE (@rowCount >= @rowCount)
BEGIN
/*
YOUR CODE :
--hhmmm.... which record you want??? this will return more than one record and will error out
SET @NID = (SELECT NewsID from #temp)
*/
SET @NID = (
SELECT NewsID
FROM #temp
WHERE ROWID = @rowCount --Pull record by the ROWID
)
UPDATE
News
SET
news_isonline='N'
WHERE
news_id=@NID
SET @rowCount = @rowCount+1
IF @rowCount = @rowCount
PRINT @NID
BREAK;
END
DROP TABLE #temp
October 26, 2010 at 8:07 am
chuckneerg (10/26/2010)
Can you provide more details on what your trying to do?I get what your code is doing but there are some logical issues with it...
The idea of you wanting to update things row by row is a good sign of heading in the wrong direction...
but with what you provided here is what i got...
An easy way to get around using the XMLPrepare Document is something like this...
(This could remove the need for the #temp table... and just join to the table you need to update with this guy)
DECLARE @XML XML = CONVERT(XML,@doc)
SELECT
x.value('.','uniqueidentifier') AS ElementValue
FROM
@XML.nodes('/*/*') y(x)
I have made some adjustments to your code to get it to execute..
DECLARE @doc NVARCHAR(MAX)
SET @doc ='
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>'
--Create an internal representation of the XML document.
DECLARE @XML AS XML = CONVERT(XML,@doc)
SELECT
ROW_NUMBER() OVER (ORDER BY x.value('.','uniqueidentifier')) AS ROWID, --Will allow you to pull one record at a time
x.value('.','uniqueidentifier') AS NewsID
INTO #temp --This could most likely be avoided
FROM
@XML.nodes('/*/*') y(x)
DECLARE
@RowCount INT = 0 ,
@MaxRowCount INT = 0,
@NID UNIQUEIDENTIFIER
SET @rowCount = (SELECT COUNT(*) FROM #temp)
--Your checking a value against its self? When will RowCount NOT equal RowCount???
WHILE (@rowCount >= @rowCount)
BEGIN
/*
YOUR CODE :
--hhmmm.... which record you want??? this will return more than one record and will error out
SET @NID = (SELECT NewsID from #temp)
*/
SET @NID = (
SELECT NewsID
FROM #temp
WHERE ROWID = @rowCount --Pull record by the ROWID
)
UPDATE
News
SET
news_isonline='N'
WHERE
news_id=@NID
SET @rowCount = @rowCount+1
IF @rowCount = @rowCount
PRINT @NID
BREAK;
END
DROP TABLE #temp
Hi,
Many Thanks for the reply..
In While condition i'm trying to run the UPDATE query till all the records in TEMP table were updated.
That means, if my temp table having 5 ROWS, i need to update 5 rows one by one and after each update, increasing the ROW Number..
After all the ROWS were completed i need to BREAK the Operation and DROP the temp table.. This is actually my requirement..
On my aspx page, i'm having a gridview and user can select one or more rows and clicks on a button, then i'm constructing a serialized object and adding all of them to main DTO object and sending the XML as parameter to the Stored Proc.
I tried with the code you sent, it only updated the first row of my TEMP table, i need to loop through the temp table and update all of the ROWS.
Is there any option to update at a time all the records in TEMP table?
Thanks,
October 26, 2010 at 8:14 am
UPDATE t SET (column in t) = somevalue
FROM #temp t
INNER JOIN News n ON n.news_id = t.news_id
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 26, 2010 at 8:16 am
LOL my bad... a little early in the morning for me 😀
DECLARE @doc NVARCHAR(MAX)
SET @doc ='
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>'
--Create an internal representation of the XML document.
DECLARE @XML AS XML = CONVERT(XML,@doc)
SELECT
ROW_NUMBER() OVER (ORDER BY x.value('.','uniqueidentifier')) AS ROWID, --Will allow you to pull one record at a time
x.value('.','uniqueidentifier') AS NewsID
INTO #temp --This could most likely be avoided
FROM
@XML.nodes('/*/*') y(x)
DECLARE
@RowCount INT = 1 ,
@MaxRowCount INT = 0,
@NID UNIQUEIDENTIFIER
SET @MaxRowCount = (SELECT COUNT(*) FROM #temp)
--Your checking a value against its self? When will RowCount NOT equal RowCount???
WHILE (@rowCount <= @maxrowCount)
BEGIN
/*
YOUR CODE :
--hhmmm.... which record you want??? this will return more than one record and will error out
SET @NID = (SELECT NewsID from #temp)
*/
SET @NID = (
SELECT NewsID
FROM #temp
WHERE ROWID = @rowCount --Pull record by the ROWID
)
UPDATE
News
SET
news_isonline='N'
WHERE
news_id=@NID
SET @rowCount = @rowCount+1
END
DROP TABLE #temp
October 26, 2010 at 9:20 am
Thanks for your time,
Btw i understood the query.. It worked great!!
Thanks alot man..
October 26, 2010 at 9:37 am
There's no need for the funky looping stuff for the update.
DECLARE @doc NVARCHAR(MAX)
SET @doc ='
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>'
--Create an internal representation of the XML document.
DECLARE @XML AS XML = CONVERT(XML,@doc)
SELECT
ROW_NUMBER() OVER (ORDER BY x.value('.','uniqueidentifier')) AS ROWID, --Will allow you to pull one record at a time
x.value('.','uniqueidentifier') AS NewsID
INTO #temp --This could most likely be avoided
FROM @XML.nodes('/*/*') y(x)
UPDATE n SET news_isonline = 'N'
FROM News n
INNER JOIN #temp t ON t.news_id = n.news_id
DROP TABLE #temp
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 26, 2010 at 9:42 am
Okay, let's simplify it just a bit more.
1. Put the data directly into an XML variable, instead of a varchar then converting to XML.
2. Use a CTE instead of a temp table.
And we end up with:
DECLARE @XML XML;
SET @XML ='
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>';
WITH CTE AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY x.value('.','uniqueidentifier')) AS ROWID, --Will allow you to pull one record at a time
x.value('.','uniqueidentifier') AS NewsID
FROM @XML.nodes('/*/*') y(x)
)
UPDATE n
SET news_isonline = 'N'
FROM News n
JOIN CTE t ON t.news_id = n.news_id;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 26, 2010 at 9:50 am
Hi,
Without Looping also the query worked perfectly.. Thanks alot..
My doubt is if i get some 10,000 ID's in my XML, Will this kill performance??
One more thing, In case of Bulk Update/Insert/Delete is Object Serialization the better approach or using ObjectDataSource/SQLDataSource better in GridView?
Thanks.
October 26, 2010 at 9:50 am
Or even this...
DECLARE @XML XML;
SET @XML ='
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>';
WITH CTE AS
(
SELECT x.value('.','uniqueidentifier') AS NewsID
FROM @XML.nodes('/*/*') y(x)
)
UPDATE n
SET news_isonline = 'N'
FROM News n
JOIN CTE t ON t.news_id = n.news_id;
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2010 at 2:24 pm
WayneS (10/26/2010)
Okay, let's simplify it just a bit more.1. Put the data directly into an XML variable, instead of a varchar then converting to XML.
2. Use a CTE instead of a temp table.
And we end up with:
DECLARE @XML XML;SET @XML ='
<NewsDTO>
<NewsList>
<NewsID>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsID>
</NewsList>
<NewsList>
<NewsID>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsID>
</NewsList>
<NewsList>
<NewsID>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsID>
</NewsList>
</NewsDTO>';
WITH CTE AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY x.value('.','uniqueidentifier')) AS ROWID, --Will allow you to pull one record at a time
x.value('.','uniqueidentifier') AS NewsID
FROM @XML.nodes('/*/*') y(x)
)
UPDATE n
SET news_isonline = 'N'
FROM News n
JOIN CTE t ON t.news_id = n.news_id;
Hi,
I'm getting an error while executing this stored procedure in my frontend DataAccess Layer.
The error is No:of parameters dont match with stored procedure..
I'm serializing and constructing XML from the object and simply passing it as a parameter to the DAL as below
public bool ArchiveNewslist(string NewsListXML)
{
object[] parameters = { NewsListXML };
try
{
Int32 n = Convert.ToInt32(db.ExecuteScalar("usp_archive_news", parameters));
if (n > 0)
{
return true;
}
}
catch (System.Data.DataException exception)
{
ExceptionHandler.WriteError(exception.Message, exception);
}
return false;
}
And below is my STORED PROCEDURE
ALTER PROCEDURE [dbo].[usp_archive_news]
AS
DECLARE @doc NVARCHAR(MAX)
--SET @doc ='
--<NewsListDTO>
--<NewsDto>
--<NewsDTO>
--<NewsID>e662eef7-0537-4455-bf87-24d1f8521dfa</NewsID>
--</NewsDTO>
--<NewsDTO>
--<NewsID>0296fa6e-12b2-43a5-bbe3-3d96d88e0f02</NewsID>
--</NewsDTO>
--</NewsDto>
--</NewsListDTO>'
--Create an internal representation of the XML document.
DECLARE @XML AS XML = CONVERT(XML,@doc)
SELECT
ROW_NUMBER() OVER (ORDER BY x.value('.','uniqueidentifier')) AS ROWID, x.value('.','uniqueidentifier') AS NewsID
INTO #temp
FROM @XML.nodes('/*/*/*') y(x)
UPDATE n SET news_isonline = 'N'
FROM News n
INNER JOIN #temp t ON t.NewsID = n.news_id
DROP TABLE #temp
Can you please let me know How to declare parameter in my SP?
Thanks,
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply