sql修改xml

USE C3Article;
GO
----------------------------sourceid=104--------------------------------------------------------
UPDATE articleSource
SET title = N'ITmedia LifeStyle',shortTitle=N'ITmedia LifeStyle',logoAlt=N'ITmedia LifeStyle',editorialMemo=N'ITmedia LifeStyle(トピックス)',logoUrl=N'http://www.itmedia.co.jp/lifestyle/'
WHERE sourceId = 104

DECLARE @partnername104 nvarchar(max);
set @partnername104 = N'ITmedia LifeStyle';

DECLARE @articleIdstring104 varchar (max);
DECLARE @articleId104 int;
DECLARE @n104 int;
set @n104 = 0;
set @articleIdstring104 = '';
DECLARE @articledeleteIdArray104
CURSOR
SET
@articledeleteIdArray104 = CURSOR FOR
SELECT distinct
articleId
FROM
articleArticle
where
sourceid = 104
and status = 1;
OPEN @articledeleteIdArray104
FETCH NEXT
FROM
@articledeleteIdArray104
INTO @articleId104
WHILE @@FETCH_STATUS = 0
BEGIN
declare @xmlDoc104 xml;
select @xmlDoc104= articleXml
from articleArticle
where articleid = @articleId104;
set @xmlDoc104.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:articlesource/cp:partnerdetails/cp:partnername/text())[1] with sql:variable("@partnername104")' );

update articleArticle
set articleXml = @xmlDoc104
where articleid = @articleid104;

FETCH NEXT
FROM
@articledeleteIdArray104
INTO @articleId104
END

CLOSE @articledeleteIdArray104
Deallocate @articledeleteIdArray104

----------------------------sourceid=105--------------------------------------------------------
UPDATE articleSource
SET title = N'ITmedia Mobile',shortTitle=N'ITmedia Mobile',logoAlt=N'ITmedia Mobile',editorialMemo=N'ITmedia Mobile(トピックス)',logoUrl=N'http://www.itmedia.co.jp/mobile/'
WHERE sourceId = 105

DECLARE @partnername105 nvarchar(max);
set @partnername105 = N'ITmedia Mobile';

DECLARE @articleIdstring105 varchar (max);
DECLARE @articleId105 int;
DECLARE @n105 int;
set @n105 = 0;
set @articleIdstring105 = '';
DECLARE @articledeleteIdArray105
CURSOR
SET
@articledeleteIdArray105 = CURSOR FOR
SELECT distinct
articleId
FROM
articleArticle
where
sourceid = 105
and status = 1;
OPEN @articledeleteIdArray105
FETCH NEXT
FROM
@articledeleteIdArray105
INTO @articleId105
WHILE @@FETCH_STATUS = 0
BEGIN
declare @xmlDoc105 xml;
select @xmlDoc105= articleXml
from articleArticle
where articleid = @articleId105;
set @xmlDoc105.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:articlesource/cp:partnerdetails/cp:partnername/text())[1] with sql:variable("@partnername105")' );

update articleArticle
set articleXml = @xmlDoc105
where articleid = @articleid105;

FETCH NEXT
FROM
@articledeleteIdArray105
INTO @articleId105
END

CLOSE @articledeleteIdArray105
Deallocate @articledeleteIdArray105
----------------------------sourceid=106--------------------------------------------------------
UPDATE articleSource
SET title = N'ITmedia PC USER',shortTitle=N'ITmedia PC USER',logoAlt=N'ITmedia PC USER',editorialMemo=N'ITmedia PC USER(トピックス)',logoUrl=N'http://www.itmedia.co.jp/pcuser/'
WHERE sourceId = 106

DECLARE @partnername106 nvarchar(max);
set @partnername106 = N'ITmedia PC USER';

DECLARE @articleIdstring106 varchar (max);
DECLARE @articleId106 int;
DECLARE @n106 int;
set @n106 = 0;
set @articleIdstring106 = '';
DECLARE @articledeleteIdArray106
CURSOR
SET
@articledeleteIdArray106 = CURSOR FOR
SELECT distinct
articleId
FROM
articleArticle
where
sourceid = 106
and status = 1;
OPEN @articledeleteIdArray106
FETCH NEXT
FROM
@articledeleteIdArray106
INTO @articleId106
WHILE @@FETCH_STATUS = 0
BEGIN
declare @xmlDoc106 xml;
select @xmlDoc106= articleXml
from articleArticle
where articleid = @articleId106;
set @xmlDoc106.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:articlesource/cp:partnerdetails/cp:partnername/text())[1] with sql:variable("@partnername106")' );

update articleArticle
set articleXml = @xmlDoc106
where articleid = @articleid106;

FETCH NEXT
FROM
@articledeleteIdArray106
INTO @articleId106
END

CLOSE @articledeleteIdArray106
Deallocate @articledeleteIdArray106
----------------------------sourceid=127--------------------------------------------------------
UPDATE articleSource
SET copyrightText=N'著作権はルネサンス・アカデミー株式会社及びブロードメディア株式会社、ナショナル ジオグラフィック協会に属します。%br%© %yyyy% Renaissance Academy Corporation/Broadmedia Corporation/National Geographic. All rights reserved.'
WHERE sourceId = 127

DECLARE @copyrightText127 nvarchar(max);
set @copyrightText127 = N'著作権はルネサンス・アカデミー株式会社及びブロードメディア株式会社、ナショナル ジオグラフィック協会に属します。%br%© %yyyy% Renaissance Academy Corporation/Broadmedia Corporation/National Geographic. All rights reserved.';

DECLARE @articleIdstring127 varchar (max);
DECLARE @articleId127 int;
DECLARE @n127 int;
set @n127 = 0;
set @articleIdstring127 = '';
DECLARE @articledeleteIdArray127
CURSOR
SET
@articledeleteIdArray127 = CURSOR FOR
SELECT distinct
articleId
FROM
articleArticle
where
sourceid = 127
and status = 1;
OPEN @articledeleteIdArray127
FETCH NEXT
FROM
@articledeleteIdArray127
INTO @articleId127
WHILE @@FETCH_STATUS = 0
BEGIN
declare @xmlDoc127 xml;
select @xmlDoc127= articleXml
from articleArticle
where articleid = @articleId127;
set @xmlDoc127.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:copyright/text())[1] with sql:variable("@copyrightText127")' );

update articleArticle
set articleXml = @xmlDoc127
where articleid = @articleid127;

FETCH NEXT
FROM
@articledeleteIdArray127
INTO @articleId127
END

CLOSE @articledeleteIdArray127
Deallocate @articledeleteIdArray127

----------------------------sourceid=117--------------------------------------------------------
UPDATE articleSource
SET logoImagePath = '/article/images/s/partnerlogo/searchina_logo.gif',logoWidth=120,logoHeight=35,copyrightText=N'著作権はSBIサーチナ株式会社に属します。%br%© %yyyy% SBI Searchina Co., Ltd. All Rights Reserved.'
WHERE sourceId = 117

DECLARE @copyrightText117 nvarchar(max);
set @copyrightText117 = N'著作権はSBIサーチナ株式会社に属します。%br%© %yyyy% SBI Searchina Co., Ltd. All Rights Reserved.';

DECLARE @articleIdstring117 varchar (max);
DECLARE @articleId117 int;
DECLARE @n117 int;
set @n117 = 0;
set @articleIdstring117 = '';
DECLARE @articledeleteIdArray117
CURSOR
SET
@articledeleteIdArray117 = CURSOR FOR
SELECT distinct
articleId
FROM
articleArticle
where
sourceid = 117
and status = 1;
OPEN @articledeleteIdArray117
FETCH NEXT
FROM
@articledeleteIdArray117
INTO @articleId117
WHILE @@FETCH_STATUS = 0
BEGIN
declare @xmlDoc117 xml;
select @xmlDoc117= articleXml
from articleArticle
where articleid = @articleId117;
set @xmlDoc117.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:articlesource/cp:partnerdetails/cp:partnername/text())[1] with sql:variable("@copyrightText117")' );
set @xmlDoc117.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:articlesource/cp:partnerdetails/cp:partnerlogo/cp:linkedimage/cp:image/cp:src/text())[1] with "/article/images/s/partnerlogo/searchina_logo.gif"' );
set @xmlDoc117.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:articlesource/cp:partnerdetails/cp:partnerlogo/cp:linkedimage/cp:image/cp:width/text())[1] with "120"' );
set @xmlDoc117.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:articlesource/cp:partnerdetails/cp:partnerlogo/cp:linkedimage/cp:image/cp:height/text())[1] with "35"' );

update articleArticle
set articleXml = @xmlDoc117
where articleid = @articleid117;

FETCH NEXT
FROM
@articledeleteIdArray117
INTO @articleId117
END

CLOSE @articledeleteIdArray117
Deallocate @articledeleteIdArray117

-------------------------------------------------------- MSN編集部----------------------------------
update articleSource
set copyrightText=N'著作権は日本マイクロソフト株式会社に属します。%br%© %yyyy% Microsoft Corporation. All rights reserved.'
where sourceId in (112,113,115)

DECLARE @copyrightTextTemp nvarchar(max);
set @copyrightTextTemp = N'著作権は日本マイクロソフト株式会社に属します。%br%© %yyyy% Microsoft Corporation. All rights reserved.';

DECLARE @articleIdstringTemp varchar (max);
DECLARE @articleIdTemp int;
DECLARE @nTemp int;
set @nTemp = 0;
set @articleIdstringTemp = '';
DECLARE @articledeleteIdArrayTemp
CURSOR
SET
@articledeleteIdArrayTemp = CURSOR FOR
SELECT distinct
articleId
FROM
articleArticle
where
sourceId in (112,113,115)
and status = 1;
OPEN @articledeleteIdArrayTemp
FETCH NEXT
FROM
@articledeleteIdArrayTemp
INTO @articleIdTemp
WHILE @@FETCH_STATUS = 0
BEGIN
declare @xmlDocTemp xml;
select @xmlDocTemp= articleXml
from articleArticle
where articleid = @articleIdTemp;
set @xmlDocTemp.modify('declare namespace cp="urn:schemas-microsoft-com/contentpublishing/content";replace value of (/cp:gtl/cp:standardarticle/cp:copyright/text())[1] with sql:variable("@copyrightTextTemp")' );

update articleArticle
set articleXml = @xmlDocTemp
where articleid = @articleidTemp;

FETCH NEXT
FROM
@articledeleteIdArrayTemp
INTO @articleIdTemp
END

CLOSE @articledeleteIdArrayTemp
Deallocate @articledeleteIdArrayTemp

----------------------------------Compiler--------------------------------------------------------
DECLARE @articleidstringtable table (articleidstring varchar (max));
DECLARE @articleIdstring varchar (max);
DECLARE @articleIdstring1 varchar (max);
DECLARE @articleId1 int;
DECLARE @errId int;
DECLARE @n1 int;
set @n1 = 0;
set @articleIdstring1 = '';

DECLARE @articledeleteIdArray1
CURSOR
SET
@articledeleteIdArray1 = CURSOR FOR
SELECT distinct
articleId
FROM
articleSearchSimpleArticle
where sourceid in(104,105,106,117,127,112,113,115)

OPEN @articledeleteIdArray1
FETCH NEXT
FROM
@articledeleteIdArray1
INTO @articleId1
WHILE @@FETCH_STATUS = 0
BEGIN
set @articleIdstring1 = @articleIdstring1 + convert(varchar, @articleId1) + ','
if @n1 = 500
begin
set
@articleidstring1 = substring(@articleidstring1, 1, len(@articleidstring1) - 1)
insert
into @articleidstringtable
values (@articleidstring1)
set
@articleidstring1 = ''
set
@n1 = 0
end
set @n1 = @n1 + 1
FETCH NEXT
FROM
@articledeleteIdArray1
INTO @articleId1
END
insert
into @articleidstringtable
values (@articleidstring1)

CLOSE @articledeleteIdArray1
Deallocate @articledeleteIdArray1
---------------exec spCompilerUpdateSearchArticle------------------
DECLARE @articleidstringcursor
CURSOR
SET
@articleidstringcursor = CURSOR FOR
SELECT
articleidstring
FROM
@articleidstringtable
OPEN @articleidstringcursor
FETCH NEXT
FROM
@articleidstringcursor
INTO @articleidstring
WHILE @@FETCH_STATUS = 0
BEGIN
exec spCompilerUpdateSearchArticle @articleidstring
print '500 Compiler over'
FETCH NEXT
FROM
@articleidstringcursor
INTO @articleidstring
END
CLOSE @articleidstringcursor
Deallocate @articleidstringcursor