November 21, 2011 at 1:35 pm
Anyone know how to properly use a syntax for XQuery with delete a path listed in a sql:variable? Need to explore all options before resorting to Dynamic sql
DECLARE @xml XML = '<Root><Location MachineHours="132"></Location><TryThis>Test</TryThis></Root>'
DECLARE @var VARCHAR(128) = '/Root/Location'
SELECT @xml
--SET @xml.modify('delete /Root/Location[1]') /* Works as expected */
SET @xml.modify('delete sql:variable("@var")[1]') /* Fails with error
"XQuery [modify()]: Only non-document nodes may be deleted, found 'xs:string'"
*/
SELECT @xml
November 21, 2011 at 2:08 pm
sql:variable and sql:column can return values, but not paths. To use it that way, you need to build a dynamic SQL statement that puts the literal path in the delete command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply