asked on

How to clear an xml but keep the tags.

Hello,

Using SQL Server 2012.

I have an xml held by a variable called Variables. How can i empty every node? i.e. set it to nil or clear it.

declare @Variables xml = 
'<Variables xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <UserId>1</UserId>
  <UserFirstName xsi:nil="true" ></UserFirstName>
  <UserLastName>Administratör</UserLastName>
  <UserHSAIdentity xsi:nil="true" ></UserHSAIdentity>
  <UserUserId>2Admin</UserUserId>
  <UserEmailAddress1>xx@Skane.se</UserEmailAddress1>
  <UserEmailAddress2 xsi:nil="true" ></UserEmailAddress2>
  <UserAdressRad1>e</UserAdressRad1>
  <UserAdressRad2 xsi:nil="true" ></UserAdressRad2>
  <UserAdressRad3 xsi:nil="true" ></UserAdressRad3>
  <UserPostKod xsi:nil="true" ></UserPostKod>
  <UserPostStad xsi:nil="true" ></UserPostStad>
  <UserTelefon1>12344 129877</UserTelefon1>
  <UserTelefon2 xsi:nil="true" ></UserTelefon2>
  <UserStatus>Inaktiv</UserStatus>
  <UserJobCategory>Övrigt</UserJobCategory>
  <UserChangePasswordDateTime>Aldrig ändrat</UserChangePasswordDateTime>
  <UserLoginTries>0</UserLoginTries>
  <UserLockedDateTime>2018-02-09 14:23</UserLockedDateTime>
  <UserIsGDPRApproved>Nej</UserIsGDPRApproved>
  <UserGDPRApprovalDateTime xsi:nil="true" ></UserGDPRApprovalDateTime>
</Variables>'

Open in new window

ste5an

What's your use-case?

soozh

ASKER

i need an empty xml because i want a list of the nodes which are used in a listbox.

When i get the xml it contains data that is sensitive. This is must strip out. Then i can pass it on the the process that will use it. And i dont know the names of the nodes, only the root node.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

soozh

ASKER

thanks. we are looking into schemas now.