May 22, 2012 at 3:11 pm
adrian.facio (5/21/2012)
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>
Hi, Adrian
testing is going well.
I have edited this to read every paragraph in a document...
LinkCodes AS (
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(nvarchar(max),Link.query('.')),
code1 = Link.value('@code1','varchar(10)'),
code2 = Link.value('@code2','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/p') as NodeTable(Link)
But when I run this....
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('%code2=%',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 LinkSku lt
ON (lc.code1 IS NULL AND lt.code1_sku = lc.code2) OR
(lc.code2 IS NULL AND lt.pf_part_sku = lc.code1)
)select * from NewLinks,
But none of the wasUpdated column value was set to 1.
A typical paragraph looks like this
<p>Text before <a __macro_name="productLink" class="macro_productLink _macro_uid_13317485981859923" code1="1147776" href="" macro_uid="_13317485981859923">code1 only</a> text after</p>
May 22, 2012 at 3:48 pm
Hi there,
You can try his for LinkCodes cte. What happens is that the old query is looking for the atribute code1 or code2 in the node, but as the node is no longer '<a>' now is <p>, we need to look for the code in the attributes of the <a> node.
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('(a/@code1)[1]','varchar(10)'),
Code2 = Link.value('(a/@code2)[1]','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/p') as NodeTable(Link)
)
May 22, 2012 at 3:52 pm
Also, you can try this other aproach if there is only one link inside each parragraph.
;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/p/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 '<p>'+NewLink+'<p/>' 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 22, 2012 at 3:57 pm
How many links a paragraph may contain?
May 22, 2012 at 3:58 pm
adrian.facio (5/22/2012)
Hi there,You can try his for LinkCodes cte. What happens is that the old query is looking for the atribute code1 or code2 in the node, but as the node is no longer '<a>' now is <p>, we need to look for the code in the attributes of the <a> node.
LinkCodes
AS
(
select BodyId,
Link = Link.query('.'),
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Code1 = Link.value('(a/@code1)[1]','varchar(10)'),
Code2 = Link.value('(a/@code2)[1]','varchar(10)')
from xmldocuments cross APPLY XmlDoc.nodes('/body/p') as NodeTable(Link)
)
Thanks, thats just what I needed.
I find the the update is writing
<p>Text before < b------- html special characters!
to the column instead of
<p>Text before <a></a></p>
May 22, 2012 at 4:03 pm
can you send the body text you are testing and the code you have updated?
May 22, 2012 at 4:17 pm
adrian.facio (5/22/2012)
can you send the body text you are testing and the code you have updated?
This is one paragraph from the body text I'm testing
<p>Text before <a __macro_name="productLink" class="macro_productLink _macro_uid_13317485981859923" code1="1147776" href="" macro_uid="_13317485981859923">code1 only</a> text after</p>
This is the paragraph above updated......
<p>Text before <a __macro_name="productLink" class="macro_productLink _macro_uid_13317485981859923" code1="1147776" code2="45J0566" href="" macro_uid="_13317485981859923">code1 only</a> "
These are in special characters - I can't seem to post them as literals.
May 22, 2012 at 4:21 pm
Try setting more space in the following line:
LinkText = CONVERT(VARCHAR(200),Link.query('.')),
Change it to 1000, just to try
May 22, 2012 at 4:24 pm
Wait so your problem is that the new link is something like
semicolon ampersand lt p semicolon ampersand gt ??
May 22, 2012 at 4:26 pm
adrian.facio (5/22/2012)
Wait so your problem is that the new link is something likesemicolon ampersand lt p semicolon ampersand gt ??
Correct.
May 22, 2012 at 4:31 pm
That why the replace is for:
NewBodyText = REPLACE(REPLACE(NewBodyText,'<','<'),'>','>')
should be like :
NewBodyText = REPLACE(REPLACE(NewBodyText,'ampersand lt semicolon','<'),' ampersand gt semicon','>')
Using the actual chars that we cant post in the forum
May 22, 2012 at 4:34 pm
adrian.facio (5/22/2012)
That why the replace is for:NewBodyText = REPLACE(REPLACE(NewBodyText,'<','<'),'>','>')
should be like :
NewBodyText = REPLACE(REPLACE(NewBodyText,'ampersand lt semicolon','<'),' ampersand gt semicon','>')
Using the actual chars that we cant post in the forum
Ah ok - thanks, I think I saw that but missed it's meaning in your earlier post.
I'll work on this tonite :-).
May 30, 2012 at 2:48 pm
rabisco (5/22/2012)
adrian.facio (5/22/2012)
That why the replace is for:NewBodyText = REPLACE(REPLACE(NewBodyText,'<','<'),'>','>')
should be like :
NewBodyText = REPLACE(REPLACE(NewBodyText,'ampersand lt semicolon','<'),' ampersand gt semicon','>')
Using the actual chars that we cant post in the forum
Ah ok - thanks, I think I saw that but missed it's meaning in your earlier post.
I'll work on this tonite :-).
Hi adrian,
this code...
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
)
Adds the <body></body> tag to every row from the NewLinks table for which wasupated = 1.
I really want to end up with one <body></body> for each bodyid updated .
Thanks.
May 30, 2012 at 3:36 pm
Hi Rabisco,
Please post your complete query code. The code i sent is intendeed to add <body> at the beggining and the end of one body id, just as you needed it. So i guess there is some parameter changes that needs to be checked out in your version.
May 30, 2012 at 3:50 pm
adrian.facio (5/30/2012)
Hi Rabisco,Please post your complete query code. The code i sent is intendeed to add <body> at the beggining and the end of one body id, just as you needed it. So i guess there is some parameter changes that needs to be checked out in your version.
Ah yes, it does. In my testing, you may have multiple paragraphs in one bodyid which need to be updated. a <body></body> is added to each paragraph. What I'm for is a <body> before the first update and </body> after the last update.
I'll post the code shortly.
Viewing 15 posts - 46 through 60 (of 61 total)
You must be logged in to reply to this topic. Login to reply