Re: Extracting an XML fragment

[Harald Groven]

MySql unfortunately doesn't have a function for extracting XML-fragments, only CDATA.

In some cases you may insert encapsulate a part of an XML-document with a CDATA section, so that EXTRACTVALUE will return tags and not only text.


Here's an example of how to insert an <![CDATA[ start tag and ]]> inside all occurrences of the tag <programDescription> that do not already have a CDATA section

UPDATE courses
SET xmldata = REPLACE(xmldata, '<programDescription>', '<programDescription>\n<![CDATA['),
xmldata = REPLACE(xmldata, '</programDescription>', ']]>\n</programDescription>')
WHERE xmldata NOT REGEXP '<programDescription>[[:space:]]{0,}<![[.left-square-bracket.]]CDATA'
;