How can I replace an XML substring with the results of a query in SQL Server?

I have an XML column in SQL Server that contains a SQL where clause. I would like to replace specific substrings with the results of a query. In the example below, I want to replace p.[EconPeriodID] = 4 with EconPeriod = 'Jan 2011' and replace p.[BookID] = 2 with Book = 'Canada'

<Parameter Name="whereClause" 
           BaseType="nvarchar" 
           Precision="0" 
           Scale="0" 
           MaxLength="64"
          >WHERE (p.[EconomicPeriodID] = 4 and p.[BookID] = 2)</Parameter>

I am not using the CrossApply searching because I am returning multiple messages with different parameters.

Full query below :

DECLARE @sdt_Date DATETIME
SET @sdt_Date = '3/17/2011'

SELECT  name ,
        submit_time ,
        request,
        CONVERT(VARCHAR, request.value(N'
   (for $A in /ServiceProxy/Event/Parameters/Parameter
   where $A/@Name ="valuationDate"
   return $A/text())[1]
', 'datetime'), 111)
  AS 'EOD Date' ,
        request.query(N'
   for $A in /ServiceProxy/Event/Parameters/Parameter
   where $A/@Name ="whereClause"
   return $A/text()
').value('.', 'varchar(max)') AS 'WHERE Clause' 
FROM    Requests
WHERE   submit_time BETWEEN DATEADD(dd, 0,
                                                      DATEDIFF(dd, 0,
                                                              @sdt_Date))
开发者_运维问答                                      AND     DATEADD(dd, 1,
                                                      DATEDIFF(dd, 0,
                                                              @sdt_Date))
ORDER BY submit_time DESC

Update: I am debating between using Cross Apply and changing the saved query to use human readable params. Cross Apply would let me reference the XML shred multiple times in the query, for use with Replace.

So you're returning the WHERE clause as a string? You're not trying to update the value in the XML column, just returning a modified value?

Could you do something like this?

WITH A AS
(  your query minus the ORDER BY clause )
SELECT  Name
     ,  submit_time
     ,  request
     ,  [EOD Date]
     ,  [WHERE Clause] = REPLACE(A.[WHERE Clause], 'p.[EconPeriod] = 4','[EconPeriod] = ''Jan 2011''')
FROM   A
ORDER BY submit_time DESC