May 18, 2012 at 2:49 pm
adrian.facio (5/18/2012)
Hey buddy,What sql server version are you using? do you have xml variables?
The sql server version is Microsoft SQL Server 2005 - 9.00.4035.00 Service Pack 2
May 18, 2012 at 3:35 pm
Oh that is good,
Well this query is kinda the same that the past one but it's clearer. I was trying to recreate all the bodytext handling them as a xml variable, in order to make and update of the type OldBodyText = NewBodyText for multiple documents. But i kinda suffer a deviation, anyways this one works good.
DECLARE @Updates AS VARCHAR(MAX)
SET @Updates = ''
;WITH XmlDocuments
AS
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+bodytext)
from documentbody d
where bodyid = 1007
),
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('@code1','varchar(10)'),
Code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/a') as NodeTable(Link)
)
SELECT @Updates = @Updates + 'UPDATE DocumentBody '+
'SET BodyText = REPLACE (BodyText,'''+LinkText+''','''+NewLink+''') '+
'WHERE BodyId = '+CONVERT(VARCHAR(20),BodyId)+';'+char(13)+char(10)
FROM LinkCodes lc
LEFT JOIN LinkTable lt
ON (lc.Code1 IS NULL AND lt.Code2_Sku = lc.Code2) OR
(lc.Code2 IS NULL AND lt.Code1_Sku = lc.Code1)
CROSS APPLY ( SELECT NewLink = CASE WHEN lc.Code1 IS NULL
THEN stuff(LinkText,patindex('%code2%',LinkText),0,'code1="'+lt.Code1_Sku+'" ')
WHEN lc.Code2 IS NULL
THEN stuff(LinkText,patindex('%href%',LinkText),0,'code2="'+lt.Code2_Sku+'" ')
ELSE LinkText
END) nl
PRINT @Updates
May 18, 2012 at 3:37 pm
i forgot the exec (@updates) stamentent in the end.
May 18, 2012 at 3:40 pm
adrian.facio (5/18/2012)
i forgot the exec (@updates) stamentent in the end.
Thanks SSC-Enthusiastic. I'll spend some of the weekend testing it in My Dev environment.
Have a good weekend.
May 18, 2012 at 3:47 pm
Ok you too have a good wekeend, by the way my name is Adrian, SSC-Enthusiasthic is a title of the forum. This is the last update, try this one out .
DECLARE @Updates AS VARCHAR(MAX)
SET @Updates = ''
;WITH XmlDocuments
AS
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+bodytext)
from documentbody d
where bodyid = 1007
),
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('@code1','varchar(10)'),
Code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/a') as NodeTable(Link)
)
SELECT @Updates = @Updates + 'UPDATE DocumentBody '+
'SET BodyText = REPLACE (BodyText,'''+LinkText+''','''+NewLink+''') '+
'WHERE BodyId = '+CONVERT(VARCHAR(20),BodyId)+';'+char(13)+char(10)
FROM LinkCodes lc
LEFT JOIN LinkTable lt
ON (lc.Code1 IS NULL AND lt.Code2_Sku = lc.Code2) OR
(lc.Code2 IS NULL AND lt.Code1_Sku = lc.Code1)
CROSS APPLY ( SELECT NewLink = CASE WHEN lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%code2%',LinkText),0,'code1="'+lt.Code1_Sku+'" ')
WHEN lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%href%',LinkText),0,'code2="'+lt.Code2_Sku+'" ')
ELSE LinkText
END) nl
WHERE NewLink <> LinkText
PRINT @Updates
EXEC (@Updates)
May 18, 2012 at 3:58 pm
adrian.facio (5/18/2012)
Ok you too have a good wekeend, by the way my name is Adrian, SSC-Enthusiasthic is a title of the forum. This is the last update, try this one out .DECLARE @Updates AS VARCHAR(MAX)
SET @Updates = ''
;WITH XmlDocuments
AS
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+bodytext)
from documentbody d
where bodyid = 1007
),
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('@code1','varchar(10)'),
Code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/a') as NodeTable(Link)
)
SELECT @Updates = @Updates + 'UPDATE DocumentBody '+
'SET BodyText = REPLACE (BodyText,'''+LinkText+''','''+NewLink+''') '+
'WHERE BodyId = '+CONVERT(VARCHAR(20),BodyId)+';'+char(13)+char(10)
FROM LinkCodes lc
LEFT JOIN LinkTable lt
ON (lc.Code1 IS NULL AND lt.Code2_Sku = lc.Code2) OR
(lc.Code2 IS NULL AND lt.Code1_Sku = lc.Code1)
CROSS APPLY ( SELECT NewLink = CASE WHEN lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%code2%',LinkText),0,'code1="'+lt.Code1_Sku+'" ')
WHEN lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%href%',LinkText),0,'code2="'+lt.Code2_Sku+'" ')
ELSE LinkText
END) nl
WHERE NewLink <> LinkText
PRINT @Updates
EXEC (@Updates)
oops, sorry about that adrian.facio.
May 18, 2012 at 4:56 pm
No worries, hey this is something closer of what is was trying to achieve, a set based updated of complete body text.
;WITH XmlDocuments
AS
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+bodytext)
from documentbody d
where BodyText IS NOT NULL
),
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('@code1','varchar(10)'),
Code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/a') as NodeTable(Link)
),
NewLinks
AS
(
SELECT BodyId,LinkText,
NewLink = CASE WHEN lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%code2%',LinkText),0,'code1="'+lt.Code1_Sku+'" ')
WHEN lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%href%',LinkText),0,'code2="'+lt.Code2_Sku+'" ')
ELSE LinkText
END,
WasUpdated = CASE WHEN (lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL) OR
(lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL)
THEN 1
ELSE 0
END
FROM LinkCodes lc
LEFT JOIN LinkTable lt
ON (lc.Code1 IS NULL AND lt.Code2_Sku = lc.Code2) OR
(lc.Code2 IS NULL AND lt.Code1_Sku = lc.Code1)
),
NewDocumentBodies
AS
(
SELECT BodyId,
NewBodyText = REPLACE(REPLACE(NewBodyText,'<','<'),'>','>')
FROM (SELECT DISTINCT BodyId FROM NewLinks WHERE WasUpdated = 1 ) db
CROSS APPLY (SELECT NewBodyText = CONVERT( VARCHAR(MAX),
(SELECT NewLink AS 'text()'
FROM NewLinks nl WHERE nl.BodyId = db.BodyId
FOR XML PATH('body'))
)
) nt
)
--SELECT * FROM NewDocumentBodies
UPDATE DocumentBody SET DocumentBody.BodyText = ndb.NewBodyText
FROM DocumentBody odb
INNER JOIN NewDocumentBodies ndb
ON odb.BodyId = ndb.BodyId
May 18, 2012 at 5:00 pm
The thing with xml usage is that the links need to be well formed. Some of the test data was lack of the char ". For example in the following body text the class attribute was not closed :
<body> <a name="productLink" class="productLink code1="1595501" href="" uid="13298529604731501" code2="83K3087" >sku3</a> <a name="productLink" class="productLink" code2="66F9331" href="">sku4</a></body>
Is your real data missing quotes like this example?
May 18, 2012 at 5:02 pm
mmm the code was posted weirdly, let see if this is shown better.
;WITH XmlDocuments
AS
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+bodytext)
from documentbody d
where BodyText IS NOT NULL
),
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('@code1','varchar(10)'),
Code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/a') as NodeTable(Link)
),
NewLinks
AS
(
SELECT BodyId,LinkText,
NewLink = CASE WHEN lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%code2%',LinkText),0,'code1="'+lt.Code1_Sku+'" ')
WHEN lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%href%',LinkText),0,'code2="'+lt.Code2_Sku+'" ')
ELSE LinkText
END,
WasUpdated = CASE WHEN (lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL) OR
(lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL)
THEN 1
ELSE 0
END
FROM LinkCodes lc
LEFT JOIN LinkTable lt
ON (lc.Code1 IS NULL AND lt.Code2_Sku = lc.Code2) OR
(lc.Code2 IS NULL AND lt.Code1_Sku = lc.Code1)
),
NewDocumentBodies
AS
(
SELECT BodyId,
NewBodyText = REPLACE(REPLACE(NewBodyText,'<','<'),'>','>')
FROM (SELECT DISTINCT BodyId FROM NewLinks WHERE WasUpdated = 1 ) db
CROSS APPLY (SELECT NewBodyText = CONVERT( VARCHAR(MAX),
(SELECT NewLink AS 'text()'
FROM NewLinks nl WHERE nl.BodyId = db.BodyId
FOR XML PATH('body'))
)
) nt
)
--SELECT * FROM NewDocumentBodies
UPDATE DocumentBody SET DocumentBody.BodyText = ndb.NewBodyText
FROM DocumentBody odb
INNER JOIN NewDocumentBodies ndb
ON odb.BodyId = ndb.BodyId
May 18, 2012 at 5:04 pm
This one looks slightly better i could not make it show "semicolor ampersan lt " instead of <, but if you copie the code the text is good.
;WITH XmlDocuments
AS
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+bodytext)
from documentbody d
where BodyText IS NOT NULL
),
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('@code1','varchar(10)'),
Code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/a') as NodeTable(Link)
),
NewLinks
AS
(
SELECT BodyId,LinkText,
NewLink = CASE WHEN lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%code2%',LinkText),0,'code1="'+lt.Code1_Sku+'" ')
WHEN lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%href%',LinkText),0,'code2="'+lt.Code2_Sku+'" ')
ELSE LinkText
END,
WasUpdated = CASE WHEN (lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL) OR
(lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL)
THEN 1
ELSE 0
END
FROM LinkCodes lc
LEFT JOIN LinkTable lt
ON (lc.Code1 IS NULL AND lt.Code2_Sku = lc.Code2) OR
(lc.Code2 IS NULL AND lt.Code1_Sku = lc.Code1)
),
NewDocumentBodies
AS
(
SELECT BodyId,
NewBodyText = REPLACE(REPLACE(NewBodyText,'<','<'),'>','>')
FROM (SELECT DISTINCT BodyId FROM NewLinks WHERE WasUpdated = 1 ) db
CROSS APPLY (SELECT NewBodyText = CONVERT( VARCHAR(MAX),
(SELECT NewLink AS 'text()'
FROM NewLinks nl WHERE nl.BodyId = db.BodyId
FOR XML PATH('body'))
)
) nt
)
--SELECT * FROM NewDocumentBodies
UPDATE DocumentBody SET DocumentBody.BodyText = ndb.NewBodyText
FROM DocumentBody odb
INNER JOIN NewDocumentBodies ndb
ON odb.BodyId = ndb.BodyId
May 21, 2012 at 9:21 am
adrian.facio (5/18/2012)
This one looks slightly better i could not make it show "semicolor ampersan lt " instead of <, but if you copie the code the text is good.
;WITH XmlDocuments
AS
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+bodytext)
from documentbody d
where BodyText IS NOT NULL
),
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('@code1','varchar(10)'),
Code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/a') as NodeTable(Link)
),
NewLinks
AS
(
SELECT BodyId,LinkText,
NewLink = CASE WHEN lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%code2%',LinkText),0,'code1="'+lt.Code1_Sku+'" ')
WHEN lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%href%',LinkText),0,'code2="'+lt.Code2_Sku+'" ')
ELSE LinkText
END,
WasUpdated = CASE WHEN (lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL) OR
(lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL)
THEN 1
ELSE 0
END
FROM LinkCodes lc
LEFT JOIN LinkTable lt
ON (lc.Code1 IS NULL AND lt.Code2_Sku = lc.Code2) OR
(lc.Code2 IS NULL AND lt.Code1_Sku = lc.Code1)
),
NewDocumentBodies
AS
(
SELECT BodyId,
NewBodyText = REPLACE(REPLACE(NewBodyText,'<','<'),'>','>')
FROM (SELECT DISTINCT BodyId FROM NewLinks WHERE WasUpdated = 1 ) db
CROSS APPLY (SELECT NewBodyText = CONVERT( VARCHAR(MAX),
(SELECT NewLink AS 'text()'
FROM NewLinks nl WHERE nl.BodyId = db.BodyId
FOR XML PATH('body'))
)
) nt
)
--SELECT * FROM NewDocumentBodies
UPDATE DocumentBody SET DocumentBody.BodyText = ndb.NewBodyText
FROM DocumentBody odb
INNER JOIN NewDocumentBodies ndb
ON odb.BodyId = ndb.BodyId
Thanks adrian. I'll work through these today.
May 21, 2012 at 2:56 pm
rabisco (5/21/2012)
adrian.facio (5/18/2012)
This one looks slightly better i could not make it show "semicolor ampersan lt " instead of <, but if you copie the code the text is good.
;WITH XmlDocuments
AS
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+bodytext)
from documentbody d
where BodyText IS NOT NULL
),
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('@code1','varchar(10)'),
Code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/a') as NodeTable(Link)
),
NewLinks
AS
(
SELECT BodyId,LinkText,
NewLink = CASE WHEN lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%code2%',LinkText),0,'code1="'+lt.Code1_Sku+'" ')
WHEN lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL
THEN stuff(LinkText,patindex('%href%',LinkText),0,'code2="'+lt.Code2_Sku+'" ')
ELSE LinkText
END,
WasUpdated = CASE WHEN (lc.Code1 IS NULL AND lt.Code1_Sku IS NOT NULL) OR
(lc.Code2 IS NULL AND lt.Code2_Sku IS NOT NULL)
THEN 1
ELSE 0
END
FROM LinkCodes lc
LEFT JOIN LinkTable lt
ON (lc.Code1 IS NULL AND lt.Code2_Sku = lc.Code2) OR
(lc.Code2 IS NULL AND lt.Code1_Sku = lc.Code1)
),
NewDocumentBodies
AS
(
SELECT BodyId,
NewBodyText = REPLACE(REPLACE(NewBodyText,'<','<'),'>','>')
FROM (SELECT DISTINCT BodyId FROM NewLinks WHERE WasUpdated = 1 ) db
CROSS APPLY (SELECT NewBodyText = CONVERT( VARCHAR(MAX),
(SELECT NewLink AS 'text()'
FROM NewLinks nl WHERE nl.BodyId = db.BodyId
FOR XML PATH('body'))
)
) nt
)
--SELECT * FROM NewDocumentBodies
UPDATE DocumentBody SET DocumentBody.BodyText = ndb.NewBodyText
FROM DocumentBody odb
INNER JOIN NewDocumentBodies ndb
ON odb.BodyId = ndb.BodyId
Thanks adrian. I'll work through these today.
Hi Adrian,
I'm attempting to test this piece meal. The first issue is that when I run this (Targetting at a specific bodyid
;WITH XmlDocuments
as
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+cast(bodytext as nvarchar(max)))
from documentbody d
--where BodyText IS NOT NULL
where bodyid = '1117'
)
i get the error...
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
The other issue is that when I run this by itself...
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+cast(bodytext as nvarchar(max)))
from documentbody d
where BodyText IS NOT NULL
I get this error.....
Msg 9437, Level 16, State 1, Line 1
XML parsing: line 1, character 261, duplicate attribute
This query returns a result as expected ...
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+cast(bodytext as nvarchar(max)))
from documentbody d
where bodyid = '1117'
Thanks.
May 21, 2012 at 3:24 pm
Hi, what happens is that you need to add a select that uses the CTE. a CTE by itself can't run. Try this please
;WITH XmlDocuments
as
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+cast(bodytext as nvarchar(max)))
from documentbody d
--where BodyText IS NOT NULL
where bodyid = '1117'
)
select * from XmlDocuments
May 21, 2012 at 3:27 pm
adrian.facio (5/21/2012)
Hi, what happens is that you need to add a select that uses the CTE. a CTE by itself can't run. Try this please
;WITH XmlDocuments
as
(
select *,XmlDoc = convert(xml,'<?xml version = "1.0"?>'+cast(bodytext as nvarchar(max)))
from documentbody d
--where BodyText IS NOT NULL
where bodyid = '1117'
)
select * from XmlDocuments
Ah - got it thanks.
May 21, 2012 at 3:28 pm
The second errors is caused by one of the test data elements, it contains the same attribute twice, for example :
<a name ="FirstName" name = "SecondName" Code1 =""123> A code link </a>
Viewing 15 posts - 31 through 45 (of 61 total)
You must be logged in to reply to this topic. Login to reply