How to Update a Node of XML Datatype Column with Data from same Table

Haribansh Kumar Agrawal
Oct 09 2014
Code

1.4k
0
0

Suppose, I have table with name "XMLData" with two column "StudentID " --> datatype int and "XMLColumndata" with datatype "xml"

StudentID              XMLCOlumnData

      24                    <UBO4>.....</UBO4>
      25                     <UBO4>.....</UBO4>

The format of my XML is:

<UBO4 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<InvoiceID>0</InvoiceID>
<S1>Kanrad Home Health Inc
1580 Broad Avenue
Belle Vernon, NY 15012</S1>
<S2 >100</S2>
<S3>26020</S3>
<S4>XXX</S4>
<S5>1234567890</S5>
</UBO4>

If you want to update "S2" node of xml with StudentID of that row.
this is the Solution:

UPDATE XMLData SET XMLCOlumnData.modify('
replace value of
(/UBO4[1]/S2 [1]/text()[1])
with
( sql:column("StudentID ") )
')