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 ") )
')