XMLdata.modify Condition

101 views Asked by At

I'm usint MS SQL and in XML field need update like this.

 UPDATE Table
 SET 
    IF (XMLdata.exist('/ns:root/Field/text()') = 0)
       XMLdata.modify('
            insert text{"New value"} 
            into (/ns:root/Field)[1]
      ')
    ELSE
       XMLdata.modify('
            replace value of (/ns:root/Field/text())[1]
            with "Replaced value"
       '),
  WHERE condition

Sample of 2 XML Files

Version 1

<ns:root ....>
    <Field />
</ns:root>

Version 2

<ns:root ....>
    <Field>Value</Field>
</ns:root>
1

There are 1 answers

0
Rashad Aliyev On BEST ANSWER

I solved it.

;WITH XMLNAMESPACES ('uri' as ns)
UPDATE Document
SET
    XMLdata.modify('
        insert text{"New value"} 
        into (/ns:root/Field)[1]
    ')
WHERE 
     (XMLData.exist('(/ns:root/Field/text())[1]')=0)
AND condition;
;WITH XMLNAMESPACES ('uri' as ns)
UPDATE Document
SET 
    XMLdata.modify('
    replace value of (/ns:root/Field/text())[1]
    with "Replaced value"
')
WHERE condition;