June 8, 2022 at 4:54 pm
Hello!
This is a follow-up for this topic as, IMHO, the question deserves a separate thread
I'm using a dynamic SQL to solve the issue mentioned in the link.
Most of the non-trivial long query I've already migrated to dynamic, but these particular lines I struggle with
DECLARE @xml xml
SELECT @xml = C
FROM OPENROWSET (BULK 'C:\Program Files\Data\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 got the following code producing compiling errors
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)
'
EXEC sys.sp_executesql @stmt = @SQL;
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'C:'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '@id'.
How to handle strings inside a string I can't figure out...
Would someone please give me a hand here?
June 8, 2022 at 5:09 pm
Looks like you have too many quotes.
Try replacing all your '''' with ''.
Also use PRINT(@SQL) to check your D-Sql
June 8, 2022 at 5:28 pm
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
It woks, thank you everybody!
June 8, 2022 at 7:16 pm
Very special thanks for the technique to check what dynamic produces!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply