Inserting data into xml column

DerekFL

We have a data feed where we need the authors name description and then all the videos the author made. I created a table DataFeed (authorId int, name, desc, items xml). I then filled it with all the authors names and descriptions. I need the items to look like this "Author"Author Name 1"/Author" "Item"Video1"/Item" "Item"Video2"/Item" "Author"Author Name 2"/Author" "Item"Video1"/Item" "Item"Video2"/Item" "Item"Video3"/Item" I have a FOR XML query that leoinfo gave me yesterday where I query all videos for a author but when I tried the following I cant get the output because it needs a variable in the query to output to a OUTPUT var.... DECLARE @xml xml DECLARE @cmd nvarchar(4000) DECLARE @AuId int DECLARE @AuName varchar(50) DECLARE cur_author CURSOR FOR SELECT AuthorId, AuthorName FROM dbo.DataFeed OPEN cur_author FETCH NEXT FROM cur_author INTO @AuId,@AuName WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = [SQL FOR XML Query Videos WHERE AuthorId = @AuId] EXEC sp_executesql @cmd, @xml OUTPUT UPDATE dbo.DataFeed SET items = @xml WHERE WHERE AuthorId = @AuId FETCH NEXT FROM cur_author INTO @AuId,@AuName END .. here is the SQL FOR XML Query 'WITH XMLNAMESPACES (''uri1'' as media, ''uri2'' as live) 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 AuthorId = ' + '''' + @auId + '''' +' FOR XML PATH(''item''), ROOT(''root'')'

leoinfo

WOW! A lot of code you put there ... Try delete all the code and replace it with the block bellow :) 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;

leoinfo

You seem to have a very exciting job ... :D I wish I had a job so demanding ... my new job is kind of boring :( ... but not for long ;)

DerekFL

Wow thats a lot simpler. Thanks a lot. I have a bad habit of reverting to cursors when I cant get something to work. For a Canadian your all right. (Just kidding my sister lives in Toronto)