CODEWITHSUNDEEP
sqlsql-serverxml
awall
awall

Reputation: 13

Update XML value in SQL

Hopefully, someone can help.

I have a SQL Server table with a column StudentMessage which contains XML:

<StudentMessage xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://example.com/ESB/Schemas/Canonical/StudentMessage/1.0">
  <ProcessHeader xmlns="">
  ..
  </ProcessHeader>
  <Student xmlns="">
    <StudentRegNo>550059513</StudentRegNo>
    <TypeOfStudent>A</TypeOfStudent>
    ..
    ..
  </Student>
  ..
</StudentMessage>

I'm trying to update the value in TypeOfStudent from A to B.

I initially tried the below which was accepted however the value wasn't updated which lead me to believe I have the XPATH incorrect

UPDATE [Student]
SET StudentMessage.modify('replace value of (//*:StudentMessage/*:Student[1]/@TypeOfStudent)[1] with ("B")')
WHERE StudentMessageId = 19181

The full XPATH of TypeOfStudent is below:

/*[local-name()='StudentMessage' and namespace-uri()='http://example.com/ESB/Schemas/Canonical/StudentMessage/1.0']/*[local-name()='Student' and namespace-uri()='']/*[local-name()='TypeOfStudent' and namespace-uri()='']

I also tried using

UPDATE [Student]
SET StudentMessage.modify('replace value of (/*[local-name()='StudentMessage' and namespace-uri()='http://example.com/ESB/Schemas/Canonical/StudentMessage/1.0']/*[local-name()='Student' and namespace-uri()='']/@*[local-name()='TypeOfStudent' and namespace-uri()=''])[1] with ("B")')
WHERE StudentMessageId = 19181

and again it doesn't update.

For this purpose, the tbl Student contains 2 columns

StudentMessageId (INT) 
StudentMessage (XML)

Upvotes: 1

Views: 52

Answers (1)

Charlieface
Charlieface

Reputation: 72287

You should use proper namespace aliasing for this. Also you need to get the text() node, and you need to check it is not already "A"

You can use WITH XMLNAMESPACES to declare a namespace alias.

WITH XMLNAMESPACES (
  'http://example.com/ESB/Schemas/Canonical/StudentMessage/1.0' AS ns
)
UPDATE Student
SET StudentMessage.modify('
    replace value of (ns:StudentMessage/Student/TypeOfStudent/text()[. = "A"])[1]
    with "B"
'
)
WHERE StudentMessage.exist('ns:StudentMessage/Student/TypeOfStudent/text()[. = "A"]') = 1
;

db<>fiddle

Alternatively declare it inline in the XQuery

UPDATE Student
SET StudentMessage.modify('
    declare namespace ns = "http://example.com/ESB/Schemas/Canonical/StudentMessage/1.0";
    replace value of (ns:StudentMessage/Student/TypeOfStudent/text()[. = "A"])[1]
    with "B"
'
)
WHERE StudentMessage.exist('ns:StudentMessage/Student/TypeOfStudent/text()[. = "A"]') = 1;

SELECT * FROM Student;

db<>fiddle

Upvotes: 2

Related Questions