Click here to Skip to main content
14,699,428 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Xml string as below
<StoreConfigurations Version="2.0" LastModified="2019-07-23 12:40:25">
  <ProcessingOptions>
    <PasswordExpirationDays>30</PasswordExpirationDays>
  </ProcessingOptions>
  <Hosts>
    <Host Suffix="MPS" Prefix="MPS_TCP">
      <StoreNumber>39</StoreNumber>
    </Host>
  </Hosts>
</StoreConfigurations>

How to remove StoreNumber value and display as below.

<StoreConfigurations Version="2.0" LastModified="2019-07-23 12:40:25">
  <ProcessingOptions>
    <PasswordExpirationDays>30</PasswordExpirationDays>
  </ProcessingOptions>
  <Hosts>
    <Host Suffix="MPS" Prefix="MPS_TCP">
      <StoreNumber />
    </Host>
  </Hosts>
</StoreConfigurations>


What I have tried:

I used delete but entire StoreNumber got deleted
Posted
Updated 30-Jan-20 11:15am
Comments
Richard MacCutchan 30-Jan-20 15:59pm
   
You need to provide a more detailed explanation, including the code that did not work.
Member 13794157 30-Jan-20 16:34pm
   
DECLARE @X XML='<storeconfigurations version="2.0" lastmodified="2019-07-23 12:40:25">
<processingoptions>
<passwordexpirationdays>30

<hosts>
<host suffix="MPS" prefix="MPS_TCP">
<storenumber>39
<mid>100


'

SET @x.modify('replace value of (/StoreConfigurations/Hosts/Host[@Suffix="MPS" and @Prefix="MPS_TCP"]/StoreNumber/text())[1]
with " "')

Output should be as below
<storeconfigurations version="2.0" lastmodified="2019-07-23 12:40:25">
<processingoptions>
<passwordexpirationdays>30

<hosts>
<host suffix="MPS" prefix="MPS_TCP">
<storenumber>
<mid>100



1 solution

According to replace value of (XML DML) - SQL Server | Microsoft Docs[^],
replace value of (/StoreConfigurations/Hosts/Host/StoreNumber/text())[1]
with ""

Or, you can try to delete then insert;
delete /StoreConfigurations/Hosts/Host/StoreNumber
insert <StoreNumber /> as last into (/StoreConfigurations/Hosts/Host)[1]
if you really want the self-closing tag.
   
Comments
Member 13794157 30-Jan-20 16:24pm
   
DECLARE @X XML='<storeconfigurations version="2.0" lastmodified="2019-07-23 12:40:25">
<processingoptions>
<passwordexpirationdays>30

<hosts>
<host suffix="MPS" prefix="MPS_TCP">
<storenumber>39


'

select @x.modify('replace value of (/StoreConfigurations/Hosts/Host[@Suffix="MPS" and @Prefix="MPS_TCP"]/StoreNumber/text())[1]
with " "')

select @x

Getting below error
Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.
phil.o 30-Jan-20 16:30pm
   
Can you try with the code I provided for replacement? You don't need to filter by attributes suffix and prefix.
Member 13794157 30-Jan-20 16:37pm
   
I am getting result with below query
SET @x.modify('replace value of (/StoreConfigurations/Hosts/Host[@Suffix="MPS" and @Prefix="MPS_TCP"]/StoreNumber/text())[1]
with " "')
but i want the result as shown below
<storeconfigurations version="2.0" lastmodified="2019-07-23 12:40:25">
<processingoptions>
<passwordexpirationdays>30

<hosts>
<host suffix="MPS" prefix="MPS_TCP">
<storenumber>
<mid>100




After StoreNumber, I have more elements.
phil.o 30-Jan-20 16:46pm
   
Then use insert (XML DML)[^].

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900