Post reply

dynamic sql and openrowset xml

  • June 8, 2022 at 5:09 pm

    #4046377

    Looks like you have too many quotes.

    Try replacing all your '''' with ''.

    Also use PRINT(@SQL) to check your D-Sql

    How to post data/code on a forum to get the best help.

  • June 8, 2022 at 5:28 pm

    #4046383

    I'd say you have far too many single-quotes. Each single-quote in your original query only needs to be converted into two, in order to be represented in a variable. You also need to add a single-quote at the end (to match the one in the N' at the start).

    DECLARE @SQL nvarchar(MAX);

    SET @SQL = N'
      DECLARE @xml xml
      SELECT @xml = C
    FROM OPENROWSET (BULK ''C:\ROOT\config.xml'', SINGLE_BLOB) AS Queues(C);

    SELECT
    row_number() over(order by (select 0)) as ID
    ,doc1.col.value(''@id[1]'',''nvarchar(30)'') as QueueName
    ,doc1.col.value(''q-name[1]'',''nvarchar(50)'') as QueueHumanName
    ,doc1.col.value(''(a/sl)[1]'', ''nvarchar(30)'') AS [Serial]
    FROM 
    @xml.nodes(''/config/queues/queue'') doc1(col)'

    I you put a print @qsl in there, you can see that the statement looks about the same as you original statement, which I belive is what you want.

     

     

  • June 8, 2022 at 7:14 pm

    #4046461

    It woks, thank you everybody!

  • June 8, 2022 at 7:16 pm

    #4046462

    Very special thanks for the technique to check what dynamic produces!

     

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply