How to remove part of string start and end with specific character in SQL-CodePudding

how to remove part of string start with "diffg" and end with ">" from this string

DECLARE @XmlDocument nvarchar(max) =
'<NewDataSet>
    <Table diffgr:id="Table1" msdata:rowOrder="0">
       <ActionDate>2020/06/03</ActionDate>
       <ProductCode>4523</ProductCode>
       <ToDateOnTheWay>0</ToDateOnTheWay>
    </Table> 
    <Table diffgr:id="Table2" msdata:rowOrder="2">
       <ActionDate>2020/06/03</ActionDate>
       <ProductCode>0241445</ProductCode>
       <ToDateOnTheWay>0</ToDateOnTheWay>
       <InventoryFee>286295415</InventoryFee>
       <BuyFee>997545</BuyFee>
    </Table>                             
</NewDataSet>'

CodePudding user response：

I'm not sure that this is exactly what you trying to get, but you can use something like this:

declare @newxml NVARCHAR(max) = '';
select @newxml  = case when value like '%diffgr%' 
        then stuff(value,charindex('diffgr',value)-1,999,'') else value end   '>'
from string_split(@XmlDocument,'>')
select @newxml as newXMLDoc

CodePudding user response：

Using CHARINDEX on XML is a recipe for disaster.

The real way to do this is using an XML modification statement, and store your XML in an actual xml data type.

I note that your XML is missing the namespace declarations, I have added them

DECLARE @XmlDocument xml =
'<NewDataSet xmlns:diffgr="someURI" xmlns:msdata="someURI">
    <Table diffgr:id="Table1" msdata:rowOrder="0">
       <ActionDate>2020/06/03</ActionDate>
       <ProductCode>4523</ProductCode>
       <ToDateOnTheWay>0</ToDateOnTheWay>
    </Table> 
    <Table diffgr:id="Table2" msdata:rowOrder="2">
       <ActionDate>2020/06/03</ActionDate>
       <ProductCode>0241445</ProductCode>
       <ToDateOnTheWay>0</ToDateOnTheWay>
       <InventoryFee>286295415</InventoryFee>
       <BuyFee>997545</BuyFee>
    </Table>                             
</NewDataSet>';

WHILE @XmlDocument.exist('NewDataSet/Table/@*') = 1
    SET @XmlDocument.modify('
        delete (NewDataSet/Table/@*)[1]
    ');

SELECT @XmlDocument;

db<>fiddle