There is one XML example:

<Addresses>
    <Address>
        <Line1>ASHKD JUDUD dj</Line1>
        <Line2>   HSGKDJ JDJDY JJDHC</Line2>
        <Line3>KSJHD DJJDJ tqSJNC   </Line3>
        <Line4> SJKUnxc JDJDJ</Line4>
    </Address>
    <Address>
        <Line1>DKWUHD JDJCND</Line1>
        <Line2>SKDHWY DHCBo ICUN</Line2>
        <Line3>  TSDJk SDKJDUbn UDU</Line3>
        <Line4>297476 3837  </Line4>
    </Address>
    <Address>
        <Line1>AAA BB CC DD</Line1>
        <Line2> BB KDJUJD DJKCNUW KDJK</Line2>
        <Line3>LSIDCN JDCBDU  ddn    </Line3>
        <Line4>   BXNBDki odioIOSJCX</Line4>
        <Phone>  8273 3937 </Phone>
        <City>  KSDUC SJDHCU</City>
    </Address>
</Addresses>

Is there any generic method to modify any nodes to trim-ed values?

CodePudding user response：

Unfortunately, XQuery .modify only works on a single node. You have two options:

1. Run it in a loop.
   • The syntax //*/text() finds any descendant node's inner text
   • [substring(., 1, 1) = " " checks if the text starts with . Here the . means "current value"
   • or substring(., string-length(.) - 1, 1) = " "] or if it ends with it.
   • We store that in a variable (bailing out if it's null), trim it...
   • Then store it back using .modify with replace value of

WHILE (1=1)
BEGIN
    DECLARE @val nvarchar(max) = @xml.value('(//*/text()[substring(., 1, 1) = " " or substring(., string-length(.) - 1, 1) = " "])[1]', 'nvarchar(max)');
    IF @val IS NULL
        BREAK;
    
    SET @val = TRIM(@val);
    SET @xml.modify('
        replace value of
          (//*/text()[substring(., 1, 1) = " " or substring(., string-length(.) - 1, 1) = " "])[1]
        with sql:variable("@val")
        ');
END;

SELECT @xml;

2. Rebuild the whole XML. You obviously need to know the exact layout of the XML to do this.
   • Shred it into rows using .nodes.
   • Get each value using .value
   • TRIM it.
   • Recreate the XML using FOR XML

SELECT
  TRIM(x.addr.value('(Line1/text())[1]', 'nvarchar(max)')) Line1,
  TRIM(x.addr.value('(Line2/text())[1]', 'nvarchar(max)')) Line2,
  TRIM(x.addr.value('(Line3/text())[1]', 'nvarchar(max)')) Line3,
  TRIM(x.addr.value('(Line4/text())[1]', 'nvarchar(max)')) Line4,
  TRIM(x.addr.value('(Phone/text())[1]', 'nvarchar(max)')) Phone,
  TRIM(x.addr.value('(City /text())[1]', 'nvarchar(max)')) City
FROM @xml.nodes('/Addresses/Address') x(addr)
FOR XML PATH('Address'), ROOT('Addresses'), TYPE;

db<>fiddle