What format is the XML in? Is it in a table? An external file?

If you can handle it as a variable this will do the trick.

-- your lookup table

DECLARE @products TABLE(productID int, product varchar(100));

INSERT @products VALUES (229, 'Product1'),(474, 'Product2');

-- sample XML formatted as a variable

DECLARE @xml XML =

'<tokens>

<token>

<typeid>8</typeid>

</token>

<token>

<typeid>2</typeid>

<op>*</op>

<p1id>229</p1id>

<cur>USD</cur>

<curid>1</curid>

</token>

<token>

<typeid>8</typeid>

<op>-</op>

</token>

<token>

<typeid>2</typeid>

<p1id>474</p1id>

<cur>USD</cur>

<curid>1</curid>

</token>

<token>

<typeid>1</typeid>

<op>*</op>

<p1>3</p1>

</token>

</tokens>';

-- how to update the XML

WITH

parseXML AS

(

SELECT

productID = x2.x.value('(p1id/text())[1]','int')

--NodePosition = ROW_NUMBER() OVER (ORDER BY x2.x.value('(../token)[1]','bit'))

FROM (VALUES (@xml)) x1(x)

CROSS APPLY x1.x.nodes('tokens/token') x2(x)

),

Lkup AS

(

SELECT

rTxt = '<p1id>'+CAST(p.productID AS varchar(3))+'</p1id>',

wTxt = '<p1id>'+product+'</p1id>'

FROM parseXML x

JOIN @products p ON p.productID=x.productID

)

SELECT @xml = REPLACE(CAST(@xml AS varchar(max)),rTxt,wTxt) FROM Lkup

-- results

SELECT @xml;