Sql server XML replace

Question

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 30-Jan-20 9:59am

Member 13794157

Updated 30-Jan-20 10:15am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

phil.o · Accepted Answer · 2020-01-30T10:16:00

Solution 1

According to replace value of (XML DML) - SQL Server | Microsoft Docs[^],

SQL

replace value of (/StoreConfigurations/Hosts/Host/StoreNumber/text())[1]
with ""

Or, you can try to delete then insert;

SQL

delete /StoreConfigurations/Hosts/Host/StoreNumber
insert <StoreNumber /> as last into (/StoreConfigurations/Hosts/Host)[1]

if you really want the self-closing tag.

Posted 30-Jan-20 10:16am

phil.o

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)[^].