Xquery delete with Xpath from sql:variable

Question

I am having Xpath to delete from XML in an VARCHAR(MAX) variable, but XML.modify('delete '+@MyXpath) give an error ,

The argument 1 of the XML data type method "modify" must be a string literal.

DECLARE @myXML XML,
        @MyXpath VARCHAR(MAX)

-- Processing of Xpaths for components needed to remove 
-- Adding those in @XpathsToRemove     

SELECT TOP(1) @MyXpath = [XPATH]
FROM @XpathsToRemove

SET @myXML.modify('delete '+@MyXpath)

Is there any way to remove those components with Xpath available in @MyXpath variable ?

-- Edit Example XML

DECLARE @myXML XML ='<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Dont forget me this weekend!</body>
</note>'
      ,@MyXpath VARCHAR(MAX) = '//note/from'

      -- There are many Xpaths and dynamically generated with some processing so I don't want to hardcode Xpath there
      SET @myXML.modify('delete '+@MyXpath)

       --This doesn't works too
      -- SET @myXML.modify('delete "sql:variable("MyXPath")"')
      SELECT @myXML

*Is there any way?* Short answer: Yes, *dynamically created SQL* and `EXEC`. If you want help you must provide more details. What exactly do you want to achieve? — Shnugo, Nov 18 '16 at 09:08
@Shnugo If I create SQL and EXEC, then it is unable to interact with local variables i.e `@myXML`. I am not interested to do any changes on DB side, just to update that local `@XML` variable — Sayed Hussain Mehdi, Nov 18 '16 at 09:13
How do you expect me to help? I do not know your variable, neither do I know the `XPath` you want to introduce. In general: What you try will not work! Please provide a working example: (reduced) XML and working statement with a literally written `.modify()`. Then explain which part you want to change dynamically and hope that someone can suggest a solution. — Shnugo, Nov 18 '16 at 09:18
Btw: *it is unable to interact with local variables* Why do you think so? — Shnugo, Nov 18 '16 at 09:18
`DECLARE @myXML XML =' Tove Jani Reminder Dont forget me this weekend! ' ,@MyXpath VARCHAR(MAX) = '//note/from' SET @myXML.modify('delete '+@MyXpath)` — Sayed Hussain Mehdi, Nov 18 '16 at 09:23
@Shnugo Edited question. My XML are too long and Xpaths too in real cases. So I am unable to fit in Nchar too — Sayed Hussain Mehdi, Nov 18 '16 at 09:28
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128428/discussion-between-sayed-hussain-mehdi-and-shnugo). — Sayed Hussain Mehdi, Nov 18 '16 at 09:29

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

Thanks guys, I got solution by EXEC to generate dynamic queries. Following is link that solved my issue,

How to using for loop using XQuery to delete xml nodes [duplicate]

Solution,

DECLARE @str nvarchar(MAX)
SET @str = 'SET @MyXML.modify('+char(39)+'delete '+@MyXPath+'/*'+char(39)+'); '
EXEC sp_executesql  @str, N'@MyXML xml output', @MyXML output

edited May 23 '17 at 12:33

Community

1
1

answered Nov 18 '16 at 10:07

Sayed Hussain Mehdi

151
2
9

Xquery delete with Xpath from sql:variable

1 Answers1