在SQL中用逗号分隔XML节点

<root>
  <person>
    <name>John</name>
    <age>30</age>
  </person>
  <person>
    <name>Jane</name>
    <age>25</age>
  </person>
</root>

SELECT xml_data.value('(root/person/name)[1]', 'VARCHAR(50)') AS name,
       xml_data.value('(root/person/name)[2]', 'VARCHAR(50)') AS name
FROM your_table

UPDATE your_table
SET xml_data.modify('replace value of (root/person[1]/name/text())[1] with "New Name"')

UPDATE your_table
SET xml_data.modify('insert <person><name>New Person</name><age>20</age></person> as last into (root)[1]')