Database

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; 

SELECT t1.name, t3.countryname
FROM       [table 1] as t1
INNER JOIN [table 2] as t2 ON t2.id = t1.id
INNER JOIN [table 3] as t3 ON t3.countrycode = t2.countrycode

DECLARE @s NVARCHAR(4000);
SET @s = '(SVDB, N, Y; VGBY, Y, B; SCFV, Y, S; SRVD, Y, N; SRVD, Y, N; )';

/* START PROCESSING */;
SET @s = REPLACE(REPLACE(REPLACE( @s ,' ','') ,'(','') ,')','');

CREATE TABLE #S (pk INT IDENTITY(1,1), line NVARCHAR(100)); 
CREATE TABLE #T (col1 NVARCHAR(4), col2 NVARCHAR(1), col3 NVARCHAR(1)); 

DECLARE @p1 INT;
DECLARE @p2 INT;
SET @p1 = 1; 
SET @p2 = CHARINDEX(';' , @s); 

WHILE @p1 < @p2 
BEGIN 
	INSERT INTO #S (line) SELECT SUBSTRING(@s, @p1, @p2-@p1); 
	SET @s = SUBSTRING(@s, @p2+1, LEN(@s)); 
	SET @p1 = 1; 
	SET @p2 = CHARINDEX(';' , @s); 
END 

DECLARE @k INT;SET @k = 1; 
DECLARE @max INT; 
SELECT @max = MAX(pk) FROM #S; 


WHILE @k < @max 
BEGIN 
	SELECT @s = 
          'INSERT INTO #T (col1 , col2 , col3) SELECT  ''' 
          + REPLACE( line , ',', ''',''') + '''' FROM #S WHERE pk = @k; 
	EXEC(@s); 
	SET @k = @k + 1; 
END 

DROP TABLE #S; 

SELECT * FROM #T; 

/* ;
...... 
Do what ever you have to do with data in #T table 
..... 
*/ ;

DROP TABLE #T; 
/* END PROCESSING */