XQuery Scripts (SQL Server)

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Scripts

Upload Image

Close it

Select File

Various XQuery examples that helps you to solve XML related problems within SQL Server

Moderators


	Like

Invite Friends

Home · Scripts · Most Liked · Most Commented · Fans (41)

A SQL Server XQuery example demonstrating how to delete multiple elements

Mar 14 2012 12:00AM by Jacob Sebastian

This example shows how to delete multiple elements from an XML document using XML DML in TSQL

This script was created to answer a question posted in the beyondrelational.com forum. The script I post below borrows the first version of the solution submitted by Mitesh and enhances it to answer a subsequent question.

Input

<EmployeeInfo xmlns="urnchemas-microsoft-comqlqlRowSet2">
  <Employees>
	<Employee>
	  <FirstName>Mitesh</FirstName>
	  <MiddleName>R</MiddleName>
	  <LastName>Modi</LastName>
	</Employee>
  </Employees>
</EmployeeInfo>

Task 1 - Delete the FirstName element

(I am borrowing the solution from Mitesh)

DECLARE @x XML
SELECT @x = 
'<EmployeeInfo xmlns="urnchemas-microsoft-comqlqlRowSet2">
<Employees>
	<Employee>
		<FirstName>Mitesh</FirstName>
		<MiddleName>R</MiddleName>
		<LastName>Modi</LastName>
	</Employee>
</Employees>
</EmployeeInfo>'

SET @x.modify('
	declare default element namespace "urnchemas-microsoft-comqlqlRowSet2";
	delete (EmployeeInfo/Employees/Employee/MiddleName)[1]'
 )

SELECT @x 
/*
<EmployeeInfo xmlns="urnchemas-microsoft-comqlqlRowSet2">
  <Employees>
	<Employee>
	  <FirstName>Mitesh</FirstName>
	  <LastName>Modi</LastName>
	</Employee>
  </Employees>
</EmployeeInfo>
*/

Task 2 - Delete both the FirstName and MiddleName elements

Note the usage of the local-name() function along with an or clause to delete the elements where the name of the element matches with the specified values.

DECLARE @x XML
SELECT @x = 
'<EmployeeInfo xmlns="urnchemas-microsoft-comqlqlRowSet2">
<Employees>
	<Employee>
		<FirstName>Mitesh</FirstName>
		<MiddleName>R</MiddleName>
		<LastName>Modi</LastName>
	</Employee>
</Employees>
</EmployeeInfo>'

SET @x.modify('
	declare default element namespace "urnchemas-microsoft-comqlqlRowSet2";
	delete (EmployeeInfo/Employees/Employee/*[local-name()="FirstName" 
		or local-name()="MiddleName"])
	' )

SELECT @x

/*
<EmployeeInfo xmlns="urnchemas-microsoft-comqlqlRowSet2">
  <Employees>
	<Employee>
	  <LastName>Modi</LastName>
	</Employee>
  </Employees>
</EmployeeInfo>
*/

Jacob Sebastian

1 · 100% · 32002

Liked

Worked

Failed

Lifesaver

Production

Unwise


Post this to my facebook wall
Like this
Tweet this
Tried it and worked great
It did not work for me
Thanks, this was a life saver
I am using it on production
This may be producing the expected results, but not a recommended approach


	Submit

3 Comments

Hi Jacob,

I've simplified the example code a little.

DECLARE @x XML
SELECT @x = 
'<A xmlns="edde">
  <B>
    <C>
    	<D>Mitesh</D>
    	<D>R</D>
    	<E>E1</E>
    	<E>E2</E>
    	<E>E3</E>
    	<F>F</F>
    </C>
  </B>
</A>'

SELECT @x

Now I want to know how to delete the first D element and the last E element. The result I want is:

<A xmlns="edde">
  <B>
    <C>
    	<D>R</D>
    	<E>E1</E>
    	<E>E2</E>
    	<F>F</F>
    </C>
  </B>
</A>

How to do that? Thanks.

commented on Jun 19 2012 6:50AM

Edde Yang

636 · 0% · 53

If you can live with a two-step-delete, then the following will give the desired results.

DECLARE @x XML
SELECT @x = 
'<A xmlns="edde">
  <B>
	<C>
    	<D>Mitesh</D>
    	<D>R</D>
    	<E>E1</E>
    	<E>E2</E>
    	<E>E3</E>
    	<F>F</F>
	</C>
  </B>
</A>'

SET @x.modify('
	declare default element namespace "edde";
	delete A/B/C/*[local-name()="D"][1]
')

SET @x.modify('
	declare default element namespace "edde";
	delete A/B/C/*[local-name()="E"][position()=last()]
')

SELECT @x
/*
Output:
<A xmlns="edde">
  <B>
	<C>
	  <D>R</D>
	  <E>E1</E>
	  <E>E2</E>
	  <F>F</F>
	</C>
  </B>
</A>
*/

commented on Jun 19 2012 7:40AM

Jacob Sebastian

1 · 100% · 32002

Thanks Jacob.

commented on Jun 19 2012 10:03AM

Edde Yang

636 · 0% · 53

Your Comment

Sign Up or Login to post a comment.

"A SQL Server XQuery example demonstrating how to delete multiple elements" rated 5 out of 5 by 9 readers +Jacob Sebastian