Thnaks for your help!



I am building dynamic SQL command to send some commands to SQL Service Broker.



I tried this way, but when 25,000 rows hit it, it never completed.

DECLARE @SQL nvarchar(max)

SELECT @SQL = COALESCE(@SQL, N'')

    + some string here (450 bytes) like below

FROM table (with 25,000)

IF @SQL IS NOT NULL AND @SQL <> '' -- notify

BEGIN

    EXEC sp_executesql @SQL

END

The above is just a complete death to SQL Server as it is an exponential problem.



I used FOR XML PATH('') to generate list values, which almost works, besides encoding my internal XML string special characters again. it also finished in 4 seconds.



SET @SQL = CAST(

(

    SELECT ....  AS [data()]

    JOINs here

    FOR XML PATH('')

) as nvarchar(max))

It's almost right... however, it double encodes <, /> and &



Is there any way to un-encode this XML string to be like one below?



SEND ON CONVERSATION '6C3E820E-1AE1-DB11-9125-0013CE4036F0' MESSAGE TYPE [//2020technologies.com/SSB/Messages/inResponseTask] ('&lt;Task acttID="15" actID="133" actIDInstance="1" actoID="3" FromsttID="6" TosttID="5" itmID="100223" itmIDInstance="1" olnID="8420" olnIDInstance="1" PriorActionParams="" FixedParameters="ToStatusValue=&amp;quot;2010&amp;quot;" CreatedOn="2007-10-17T11:45:04.583" CreatedUserName="VIRTUAL\vladimirm" ActionTimeout="0"/&gt;');



The result I want:



SEND ON CONVERSATION '6C3E820E-1AE1-DB11-9125-0013CE4036F0' MESSAGE TYPE [//2020technologies.com/SSB/Messages/inResponseTask] ('<Task acttID="15" actID="133" actIDInstance="1" actoID="3" FromsttID="6" TosttID="5" itmID="100223" itmIDInstance="1" olnID="8420" olnIDInstance="1" PriorActionParams="" FixedParameters="ToStatusValue=&quot;2010&quot;" CreatedOn="2007-10-17T11:38:10.970" CreatedUserName="VIRTUAL\vladimirm" ActionTimeout="0"/>');



thanks for your help

Oops, I guess there is an XML group.
Sorry for wrong group posting.

Nevertheless, if some has an idea, it would be much appreciated.
  Thnaks for your help!



  I am building dynamic SQL command to send some commands to SQL Service Broker.



  I tried this way, but when 25,000 rows hit it, it never completed.

  DECLARE @SQL nvarchar(max)

  SELECT @SQL = COALESCE(@SQL, N'')

      + some string here (450 bytes) like below

  FROM table (with 25,000)

  IF @SQL IS NOT NULL AND @SQL <> '' -- notify

  BEGIN

      EXEC sp_executesql @SQL

  END

  The above is just a complete death to SQL Server as it is an exponential problem.



  I used FOR XML PATH('') to generate list values, which almost works, besides encoding my internal XML string special characters again. it also finished in 4 seconds.



  SET @SQL = CAST(

  (

      SELECT ....  AS [data()]

      JOINs here

      FOR XML PATH('')

  ) as nvarchar(max))

  It's almost right... however, it double encodes <, /> and &



  Is there any way to un-encode this XML string to be like one below?



  SEND ON CONVERSATION '6C3E820E-1AE1-DB11-9125-0013CE4036F0' MESSAGE TYPE [//2020technologies.com/SSB/Messages/inResponseTask] ('&lt;Task acttID="15" actID="133" actIDInstance="1" actoID="3" FromsttID="6" TosttID="5" itmID="100223" itmIDInstance="1" olnID="8420" olnIDInstance="1" PriorActionParams="" FixedParameters="ToStatusValue=&amp;quot;2010&amp;quot;" CreatedOn="2007-10-17T11:45:04.583" CreatedUserName="VIRTUAL\vladimirm" ActionTimeout="0"/&gt;');



  The result I want:



  SEND ON CONVERSATION '6C3E820E-1AE1-DB11-9125-0013CE4036F0' MESSAGE TYPE [//2020technologies.com/SSB/Messages/inResponseTask] ('<Task acttID="15" actID="133" actIDInstance="1" actoID="3" FromsttID="6" TosttID="5" itmID="100223" itmIDInstance="1" olnID="8420" olnIDInstance="1" PriorActionParams="" FixedParameters="ToStatusValue=&quot;2010&quot;" CreatedOn="2007-10-17T11:38:10.970" CreatedUserName="VIRTUAL\vladimirm" ActionTimeout="0"/>');



  thanks for your help

Hello Farmer,

In your FOR XML PATH query, try using the TYPE functioner ala:

use northwind
go
declare @q nvarchar(max)
set @q = '
declare @msg xml
select @msg = (
select * from dbo.products
for xml path(''product''),type)
select @msg'
exec sp_executesql @q


Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Kent,
thank you for your effort. I have tried but it does not seem to make any
difference.

Here is a query for you to try. Just point to your own Numbers table.


declare @sql nvarchar(max)
SET @sql = --REPLACE(REPLACE(
CAST(
(
SELECT
       CASE
            WHEN q.InitiatorConversationHandle IS NOT NULL AND q.Status =
32765 AND (q.Dllname < 'Human' or q.Dllname > 'Human')-- concatenate into
string only when qualifies, when in queue status
            THEN N'SEND ON CONVERSATION '
                + QUOTENAME(q.InitiatorConversationHandle, N'''') --
                + N' MESSAGE TYPE [//whatever] ('''
                + REPLACE(CAST(
                (
                    SELECT
                         q.acttID
                        ,q.actID
                        ,q.actIDInstance
                        ,q.actoID
                        ,q.FromsttID
                        ,q.TosttID
            ,q.itmID
            ,q.itmIDInstance
            ,q.olnID
            ,q.olnIDInstance -- these fields below are character so I am
escaping single tick as the result will be dynamic sql command
                        ,REPLACE(COALESCE('val1', 'val2'),'''','apos;') as
Printer
                        ,REPLACE('param="12345"','''','apos;') as
PriorActionParams
                        ,REPLACE('fparam="654321"','''','apos;') as
FixedParameters
                        ,getdate() as CreatedOn
                        ,REPLACE(system_user,'''','apos;') as
CreatedUserName
                        ,COALESCE(0,0) as "ActionTimeout"
                    FOR XML RAW ('Task')--, ROOT ('Tasks')
                ) as nvarchar(4000)), N'apos;', N'&apos;') -- escape '
character
                + N''');'
            ELSE N'' -- empty string
        END AS [data()]
    FROM
  ( -- this query is just to generate some data, based on Numbers table
   SELECT --TOP (10000) -- 24,682
     6 as FromsttID
    ,5 as TosttID
    ,133 as actID
    ,1 as actIDInstance
    ,32765 as [Status]
    ,'' as PriorActionParams
    ,getdate() as CreatedOn
    ,'Joe' as CreatedUserName
    ,1 as itmID
    ,2 as itmIDInstance
    ,3 as olnID
    ,4 as olnIDInstance
    ,newID() InitiatorConversationHandle
    ,'whatever' Dllname
    ,1 as acttID
    ,1 actoID
   FROM dbo.Numbers
   WHERE Number <= 25000
  )
    q -- must use INSERTED
FOR XML PATH(''),TYPE

) as nvarchar(max))
--, N'&lt;',N'<'), N'/&gt;',N'>')


select @sql
select cast(datalength(@sql)/1024.0/1024.0 as decimal(9,2)) as MB

what I want to get is this and I am at a loss how. My xml skills desire a
lot of improvement. :)


SEND ON CONVERSATION '6C3E820E-1AE1-DB11-9125-0013CE4036F0' MESSAGE TYPE
[//2020technologies.com/SSB/Messages/inResponseTask] ('<Task acttID="15"
actID="133" actIDInstance="1" actoID="3" FromsttID="6" TosttID="5"
itmID="100223" itmIDInstance="1" olnID="8420" olnIDInstance="1"
PriorActionParams="" FixedParameters="ToStatusValue=&quot;2010&quot;"
CreatedOn="2007-10-17T11:38:10.970" CreatedUserName="VIRTUAL\vladimirm"
ActionTimeout="0"/>');

Thank you so much for your expertize.

Show quoteHide quote

Hello Farmer,

see if this makes sense to you:

use northwind
go
declare @msg xml
declare @cmd nvarchar(max)
declare @ch uniqueidentifier
set @msg = ( select top(1) * from dbo.products for xml path(''),type )
set @ch = newID()
set @cmd = 'SEND ON CONVERSATION '''+convert(varchar(36),@ch)+''' MESSAGE
TYPE [//2020technologies.com/SSB/Messages/inResponseTask] ('''+convert(nvarchar(max),@msg)+''')'
select @cmd
go

I get:

SEND ON CONVERSATION '01F37104-41E1-4C85-9100-14D8D221B731' MESSAGE TYPE
[//2020technologies.com/SSB/Messages/inResponseTask] ('<ProductID>1</ProductID><ProductName>Chai</ProductName><SupplierID>1</SupplierID><CategoryID>1</CategoryID><QuantityPerUnit>10
boxes x 20 bags</QuantityPerUnit><UnitPrice>18.0000</UnitPrice><UnitsInStock>39</UnitsInStock><UnitsOnOrder>0</UnitsOnOrder><ReorderLevel>10</ReorderLevel><Discontinued>0</Discontinued>')


Shouldn't have any entititization issues here.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

dynamic xpath in XQUERY exist() function
Extracting xml values using xquery
XML limits in SQL/Server?
why conversion error?
XML data into relational table