wildcard search update in SQL server for XML data coulmn

Question

I have Table T1 which has one XML datatype column C1, here I want to update only domain name. do need to where for where condition, I have to update this for entire table Ex:- Data in Column C1 before update

    <DocumentElement>
      <DeliveryParameters>
       <EmailFromAddress>a1@**xyz**.com</EmailFromAddress>
       <EmailToAddress>a2@**xyz**.com;a3@**xyz**.com</EmailToAddress>
       <EmailCcAddress>a4@**xyz**.com</EmailCcAddress>
     </DeliveryParameters>
   </DocumentElement>

Result:- Data should be in C1 after update

<DocumentElement>
    <DeliveryParameters>
        <EmailFromAddress>a1@**abc**.com</EmailFromAddress>
        <EmailToAddress>a2@**abc**.com;a3@**abc**.com</EmailToAddress>
        <EmailCcAddress>a4@**abc**.com</EmailCcAddress>
    </DeliveryParameters>
</DocumentElement>

score 2 · Answer 1 · answered Mar 11 '16 at 11:54

Primary idea - recreate xml:

DECLARE @x XML = '
<DocumentElement>
    <DeliveryParameters>
        <EmailFromAddress>a1@xyz.com</EmailFromAddress>
        <EmailToAddress>a2@xyz.com;a3@xyz.com</EmailToAddress>
        <EmailCcAddress>a4@xyz.com</EmailCcAddress>
    </DeliveryParameters>
</DocumentElement>'

SELECT *
FROM (
    SELECT col = t.c.value('local-name(.)', 'VARCHAR(100)'),
           val = REPLACE(t.c.value('.', 'VARCHAR(4000)'), '@xyz.com', '@abc.com')
    FROM @x.nodes('DocumentElement/DeliveryParameters/*') t (c)
) t
PIVOT (
    MAX(val)
    FOR col IN ([EmailFromAddress], [EmailToAddress], [EmailCcAddress]) 
) p
FOR XML PATH('DeliveryParameters'), ROOT('DocumentElement')

If I understand the question correctly, the OP doesn't need the whole eMail replaced but just the xyz-part... Alltogether the question is rather unclear... — Shnugo, Mar 12 '16 at 15:47

wildcard search update in SQL server for XML data coulmn

1 Answers1