Get XQuery [edit ()]: top level attribute nodes not supported when changing xml
DECLARE @mycur CURSOR
DECLARE @id int
DECLARE @ParentNodeName varchar(max)
DECLARE @NodeName varchar(max)
DECLARE @NodeText varchar(max)
SET @mycur = CURSOR
FOR
SELECT * FROM @temp
OPEN @mycur
FETCH NEXT FROM @mycur INTO @id,@ParentNodeName,@NodeName,@NodeText
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @id -- sample statements
PRINT @ParentNodeName
PRINT @NodeName
SET @x.modify
('
insert attribute status {sql:variable("@status")}
as first
into (/@ParentNodeName/@NodeName/child::*[position()=sql:variable("@status")])[1]
')
FETCH NEXT FROM @mycur INTO @id,@ParentNodeName,@NodeName,@NodeText
END
DEALLOCATE @mycur
Any idea why I am getting this error as the request works fine if I manually insert the path
+2
source to share
1 answer
The problem is @ParentNodeName and @NodeName; the @ symbol is used to refer to a variable in SQL, but refers to an attribute in XML. The .modify () method tries to insert a string into the root chain with an attribute chain that is poorly formatted by SQL when it looks like you are trying to create a dynamic XML document. You will need to create a dynamic SQL statement that replaces the @ParentNodeName and @NodeName values with those from your temporary table, for example:
SET @SQL = ' SET @x.modify
('''
insert attribute status {sql:variable("@status")}
as first
into (/@ParentNodeName/@NodeName/child::*[position()=sql:variable("@status")])[1]
''')
SET @SQL = REPLACE(@SQL, '@ParentNodeName', @ParentNodeName)
SET @SQL = REPLACE(@SQL, '@NodeName', NodeName)
EXEC (@SQL)
tested.
0
source to share