Database

dlarkin77 wrote:

Can you suggest what I should try next?

Member 4008492 wrote:

IP number of the Local System

/******* 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 *******/ ;

/* 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;