Query to remove a tag in XML in SQL Server

Question

I have a xml with name space as follows. How do I remove the tag <ns1:replyTo> from the XML using XML query. Can we remove the tag with something like below query. Am not sure how to pass through the tags with SOAP-ENV

update tablename 
set XMLColumnname.modify(('delete /ServiceIn/file/sender/replyTo[1]'))

XML:

<?xml version="1.0"?>
    <SOAP-ENV:Envelope
        xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
        xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/1999/XMLSchema"
        xmlns:si="http://someservice.abc.com/stds/xyz/a1"
        xmlns:oas="http://docs.oasis-open.org/abc/2004/01/oasis-200401-abc-abcsecurity-xyz- 1.0.xsd">
        <SOAP-ENV:Header>
            <oas:Security>
                <oas:Credential>
                    <oas:name>%s</oas:name>
                    <oas:Password>%s</oas:Password>
                </oas:Credential>
            </oas:Security>
        </SOAP-ENV:Header>
        <SOAP-ENV:Body>
            <ns1:execute
                xmlns:ns1="urn:com.company.us.abc.service.xyz.someService">
                <si:ServiceRequestInfo>
                    <si:Tag1 />
                    <si:Tag2 />
                    <si:Tag3 />
                    <si:Tag4 />
                </si:ServiceRequestInfo>
                <ns1:ServiceIn>
                    <ns1:Tag5>%s</ns1:Tag5>
                    <ns1:File>
                        <ns1:Sender>
                            <ns1:Name>%s</ns1:Name>
                            <ns1:Email>%s</ns1:Email>
                            <ns1:replyTo>%s</ns1:replyTo>
                        </ns1:Sender>
                    </ns1:File>
                </ns1:ServiceIn>
            </ns1:execute>
        </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

score 0 · Accepted Answer · answered Jun 10 '20 at 22:48

Your current XQuery path is anonymous and also starts at the wrong root.

You need to define the required namespaces using with xmlnamespaces or declare the namespaces inside the XQuery statement itself, e.g.:

create table tablename (
  XMLColumnname xml
);

insert tablename values
(N'<?xml version="1.0"?>
    <SOAP-ENV:Envelope
        xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
        xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/1999/XMLSchema"
        xmlns:si="http://someservice.abc.com/stds/xyz/a1"
        xmlns:oas="http://docs.oasis-open.org/abc/2004/01/oasis-200401-abc-abcsecurity-xyz- 1.0.xsd">
        <SOAP-ENV:Header>
            <oas:Security>
                <oas:Credential>
                    <oas:name>%s</oas:name>
                    <oas:Password>%s</oas:Password>
                </oas:Credential>
            </oas:Security>
        </SOAP-ENV:Header>
        <SOAP-ENV:Body>
            <ns1:execute
                xmlns:ns1="urn:com.company.us.abc.service.xyz.someService">
                <si:ServiceRequestInfo>
                    <si:Tag1 />
                    <si:Tag2 />
                    <si:Tag3 />
                    <si:Tag4 />
                </si:ServiceRequestInfo>
                <ns1:ServiceIn>
                    <ns1:Tag5>%s</ns1:Tag5>
                    <ns1:File>
                        <ns1:Sender>
                            <ns1:Name>%s</ns1:Name>
                            <ns1:Email>%s</ns1:Email>
                            <ns1:replyTo>%s</ns1:replyTo>
                        </ns1:Sender>
                    </ns1:File>
                </ns1:ServiceIn>
            </ns1:execute>
        </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>');

update tablename    
set XMLColumnname.modify('declare namespace soap="http://schemas.xmlsoap.org/soap/envelope/";
declare namespace foo="urn:com.company.us.abc.service.xyz.someService";
delete /soap:Envelope/soap:Body/foo:execute/foo:ServiceIn/foo:File/foo:Sender/foo:replyTo[1]');

select * from tablename;

Which yields...

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/1999/XMLSchema" 
    xmlns:si="http://someservice.abc.com/stds/xyz/a1" 
    xmlns:oas="http://docs.oasis-open.org/abc/2004/01/oasis-200401-abc-abcsecurity-xyz- 1.0.xsd">
    <SOAP-ENV:Header>
        <oas:Security>
            <oas:Credential>
                <oas:name>%s</oas:name>
                <oas:Password>%s</oas:Password>
            </oas:Credential>
        </oas:Security>
    </SOAP-ENV:Header>
    <SOAP-ENV:Body>
        <ns1:execute xmlns:ns1="urn:com.company.us.abc.service.xyz.someService">
            <si:ServiceRequestInfo>
                <si:Tag1 />
                <si:Tag2 />
                <si:Tag3 />
                <si:Tag4 />
            </si:ServiceRequestInfo>
            <ns1:ServiceIn>
                <ns1:Tag5>%s</ns1:Tag5>
                <ns1:File>
                    <ns1:Sender>
                        <ns1:Name>%s</ns1:Name>
                        <ns1:Email>%s</ns1:Email>
                    </ns1:Sender>
                </ns1:File>
            </ns1:ServiceIn>
        </ns1:execute>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Query to remove a tag in XML in SQL Server

1 Answers1