CODEWITHSUNDEEP
mysqlxml
Simon
Simon

Reputation: 17

how to use EXTRACTVALUE() in mysql to get xmlfrag?

given the xml in mysql DB like is:

set @xml = '<lrb id="370" deleted="0">
<lrb id="371" deleted="0"><add/></lrb>
<lrb id="372" deleted="0"><add/></lrb> 
<add/></lrb>'

I use sql: SELECT EXTRACTVALUE(@xml,'//lrb[@id=370]')

and I want return this: <lrb id="371" deleted="0"><add/></lrb> <lrb id="372" deleted="0"><add/></lrb>

but the result only return some whitespaces.

I need your help to this hardwork.

thanks.

Upvotes: 0

Views: 2564

Answers (1)

ajreal
ajreal

Reputation: 47321

ExtractValue() returns only CDATA, and does not return any tags that might be contained within a matching tag, nor any of their content (see the result returned as val1 in the following example). details

If you replace it to something like :

set @xml = 
'<lrb id="370" deleted="0">THIS WILL RETURN
  <lrb id="371" deleted="0">bbb<add/></lrb>
  <lrb id="372" deleted="0">ccc<add/></lrb>
  <add/>
</lrb>';

SELECT TRIM(EXTRACTVALUE(@xml,'//lrb[@id=370]'));

return THIS WILL RETURN

Upvotes: 2

Related Questions