XML DML Delete

Question

Post reply

XML DML Delete

Kimberly Blum

SSC Enthusiast

Points: 141
More actions
August 29, 2016 at 3:52 pm

#321019

Hi there,
In the example below, I want to delete a node based on the value of one of its elements. ***I need the node name, element, and value of the element to be variable.*** Below is the code. Does anyone have any idea how to accomplish this? My experience with XPath/XQuery is limited.
Declare
@MyXML XML,
@MyFilter Varchar(1000) = 'person[type="student"]'
Set @MyXML = Cast(
'<root>' +
'<person><type>student</type><idnumber>123</idnumber></person>' +
'<person><type>employee</type><idnumber>456</idnumber></person>' +
'<person><type>student</type><idnumber>789</idnumber></person>' +
'</root>' As XML)
-- The line below works
-- Set @MyXML.modify ('delete //person[type="student"]')
-- This line does not even compile (error: XQuery [modify()]: The XQuery syntax '/function()' is not supported.)
-- Set @MyXML.modify ('delete // sql:variable("MyFilter")')
-- This line does not delete the nodes
-- Set @MyXML.modify ('delete //*[local-name()=sql:variable("MyFilter")]')
Select @MyXML

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

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

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

Quick solution

😎

USE TEEST;

GO

SET NOCOUNT ON;

Declare @MyXML XML = '<root>

<person>

<type>student</type>

<idnumber>123</idnumber>

</person>

<person>

<type>employee</type>

<idnumber>456</idnumber>

</person>

<person>

<type>student</type>

<idnumber>789</idnumber>

</person>

</root>';

-- THE TYPE TO REMOVE

DECLARE @MyFilter Varchar(1000) = 'student';

-- REMOVE THE STUDENT ENTRIES

Set @MyXML.modify ('delete //person[type=sql:variable("@MyFilter")]');

-- DISPLAY THE RESULTS

Select @MyXML;

Output

<root>

<person>

<type>employee</type>

<idnumber>456</idnumber>

</person>

</root>

Kimberly Blum SSC Enthusiast Points: 141 More actions · Answer 2

I need "person" and "type" to be variable as well. It's that combination that I'm having issues with.

Kimberly Blum SSC Enthusiast Points: 141 More actions · Answer 3

I went ahead and just used dynamic sql. I was able to do all the dynamic operations in memory only for our purposes, so performance hit shouldn't be an issue.