Click here to Skip to main content
15,845,436 members
Home / Discussions / Database
   

Database

 
GeneralRe: SQL Server connection timeout Pin
dlarkin7711-Jul-08 5:26
dlarkin7711-Jul-08 5:26 
GeneralRe: SQL Server connection timeout Pin
Member 9611-Jul-08 6:46
Member 9611-Jul-08 6:46 
GeneralRe: SQL Server connection timeout Pin
Tomz_KV11-Jul-08 6:48
Tomz_KV11-Jul-08 6:48 
QuestionSystem IP in Stored Procedure Pin
Member 400849210-Jul-08 19:51
Member 400849210-Jul-08 19:51 
AnswerRe: System IP in Stored Procedure Pin
N a v a n e e t h10-Jul-08 23:46
N a v a n e e t h10-Jul-08 23:46 
AnswerRe: System IP in Stored Procedure Pin
DerekFL11-Jul-08 5:50
DerekFL11-Jul-08 5:50 
QuestionInserting data into xml column Pin
DerekFL10-Jul-08 10:45
DerekFL10-Jul-08 10:45 
AnswerRe: Inserting data into xml column Pin
leoinfo10-Jul-08 17:31
leoinfo10-Jul-08 17:31 
WOW! A lot of code you put there ...

Try delete all the code and replace it with the block bellow Smile | :)
I don't know what are you using @AuName for, but I'm not kidding, everything you posted can be replaced by this:

/******* START COPY *******/ ;
;WITH XMLNAMESPACES ('uri1' as media, 'uri2' as live) 
UPDATE dbo.DataFeed 
SET items = ( 
		SELECT RTRIM(Title) AS 'media:title' 
		, [Description] AS 'media:description' 
		, Url AS 'live:link' 
		, DateCreated AS 'media:pubdate' 
		, thumbnail AS 'media:thumbnail' 
		FROM dbo.Videos 
		WHERE dbo.Videos.AuthorId = dbo.DataFeed.AuthorId 
		FOR XML PATH('item'), ROOT('root') 
) ;
/******* END COPY *******/ ;





Also, check the below example that I played with, maybe one of the following code styles suits you better:

/* SOURCE TABLE */
CREATE TABLE #T(id int IDENTITY(1,1), name nvarchar(10)); 
INSERT INTO #T (name) SELECT 'Smith';  
INSERT INTO #T (name) SELECT 'Yana'; 
INSERT INTO #T (name) SELECT 'Jane'; 
INSERT INTO #T (name) SELECT 'Mike'; 


CREATE TABLE #D(pk int, dsc xml); 
INSERT INTO #D (pk, dsc) SELECT 1, NULL; 
INSERT INTO #D (pk, dsc) SELECT 2, NULL; 

SELECT 'D:EMPTY', * FROM #D; 

/*STYLE 1 - USING XML VARIABLE*/; 

DECLARE @xmlOUT XML; 

;WITH XMLNAMESPACES ('uriA1' as media, 'uriA2' as live) 
SELECT @xmlOUT = ( 
   SELECT id as 'media:id1' 
         ,name AS 'live:name' 
   FROM #T 
   FOR XML PATH('item'), ROOT('root') 
);  

 
UPDATE #D 
SET dsc =  @xmlOUT 
WHERE pk = 1; 

SELECT 'D:PK1', * FROM #D; 

/*STYLE 2 - USING DIRECT UPDATE*/; 
;WITH XMLNAMESPACES ('uriB1' as media, 'uriB2' as live) 
UPDATE #D  
SET dsc = ( 
    SELECT  id as 'media:id2' 
          , name AS 'live:name' 
    FROM #T  
    FOR XML PATH('item'), ROOT('root') 
    ) 
WHERE pk = 2 ; 
 
SELECT 'D:PK2', * FROM #D; 



CREATE TABLE #X(pk int, dsc xml); 
INSERT INTO #X (pk, dsc) SELECT 1, NULL; 
INSERT INTO #X (pk, dsc) SELECT 2, NULL; 
INSERT INTO #X (pk, dsc) SELECT 3, NULL; 
INSERT INTO #X (pk, dsc) SELECT 4, NULL; 

SELECT 'X:EMPTY',  * FROM #X; 

/*STYLE 3 - USING DIRECT UPDATE*/; 
;WITH XMLNAMESPACES ('uriB1' as media, 'uriB2' as live) 
UPDATE #X  
SET #X.dsc = ( 
    SELECT  #T.id as 'media:id2' 
          , #T.name AS 'live:name' 
    FROM #T  
	WHERE #T.id = #X.pk 
    FOR XML PATH('item'), ROOT('root') 
    );  

SELECT 'X:FINAL',  * FROM #X; 

DROP TABLE #D; 
DROP TABLE #T; 
DROP TABLE #X; 

JokeRe: Inserting data into xml column Pin
leoinfo10-Jul-08 17:39
leoinfo10-Jul-08 17:39 
GeneralRe: Inserting data into xml column Pin
DerekFL11-Jul-08 5:18
DerekFL11-Jul-08 5:18 
QuestionDTS Stopped working after motherboard change Pin
FrankLepkowski10-Jul-08 9:33
FrankLepkowski10-Jul-08 9:33 
AnswerRe: DTS Stopped working after motherboard change Pin
DerekFL10-Jul-08 9:53
DerekFL10-Jul-08 9:53 
GeneralRe: DTS Stopped working after motherboard change Pin
FrankLepkowski10-Jul-08 9:54
FrankLepkowski10-Jul-08 9:54 
GeneralRe: DTS Stopped working after motherboard change Pin
DerekFL10-Jul-08 10:30
DerekFL10-Jul-08 10:30 
GeneralRe: DTS Stopped working after motherboard change Pin
DerekFL10-Jul-08 10:35
DerekFL10-Jul-08 10:35 
QuestionSelecting from three tables? Pin
NewToAspDotNet10-Jul-08 5:55
NewToAspDotNet10-Jul-08 5:55 
AnswerRe: Selecting from three tables? Pin
leoinfo10-Jul-08 6:05
leoinfo10-Jul-08 6:05 
QuestionExport from Visio to Mysql or From MS Sql Server to mysql Pin
Mohammad Al Hoss10-Jul-08 5:28
Mohammad Al Hoss10-Jul-08 5:28 
QuestionParse values in SQL SP Pin
sqldba10-Jul-08 4:21
sqldba10-Jul-08 4:21 
AnswerRe: Parse values in SQL SP [modified] Pin
leoinfo10-Jul-08 8:18
leoinfo10-Jul-08 8:18 
QuestionPattern matching to list in sql Pin
Member 469462710-Jul-08 3:21
Member 469462710-Jul-08 3:21 
QuestionRe: Pattern matching to list in sql Pin
TheFM23410-Jul-08 5:49
TheFM23410-Jul-08 5:49 
QuestionOPENXML() and performance Pin
Kjetil Svendsen9-Jul-08 23:29
Kjetil Svendsen9-Jul-08 23:29 
AnswerRe: OPENXML() and performance Pin
Ashfield10-Jul-08 2:41
Ashfield10-Jul-08 2:41 
GeneralRe: OPENXML() and performance Pin
Kjetil Svendsen10-Jul-08 3:34
Kjetil Svendsen10-Jul-08 3:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.