0

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.

Joel MC
  • 107
  • 2
  • 7
  • If you want to update an XML instance in SQL-Server, you must use some no SQL standard function or use the vendor specific XML DML language. SQL-Server doesn't support XQuery Update Facility, neither fully support XQuery. –  Mar 22 '11 at 02:59

1 Answers1

0

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
Ann L.
  • 13,760
  • 5
  • 35
  • 66
  • Ann: The modified value that I want to return is the result of another query, based on the ids and tables in the XML. Eg: select name from econperiod where id = 4 or it could be select name from book where id = 21 – Joel MC Apr 12 '11 at 13:26
  • So, if you think of a REPLACE statement as having the form REPLACE(A, B, C), you won't know B or C until runtime? And they may vary by record? – Ann L. Apr 13 '11 at 15:29