How to insert XML Content in db using SQL Module

Question

I am a beginner in XQuery and I could use some help on how to insert XML elements in my MySQL database. So far, it only inserts pieces of fragments of an xml file but I'd like to fetch the full fragments, including the elements along the content. For instance, let's say I have :

let $element := "<exemple>hello world</exemple>"
let $currentDate := fn:format-dateTime(fn:current-dateTime(), "[Y0001]-[M01]-[D01] [H01]:[m01]:[s01]")
let $values :=  "('" ||  $element || "','" || $currentDate|| "','" || $currentDate ||  "')"


let $q := 'INSERT into table (element,created,updated) values ' || $values
let $conn := sql:connect("jdbc:mysql://xxxx:xxx@mysql:3306/database",map{'autocommit': true() ,'useUnicode':true(),'characterEncoding':'utf8'})
let $s := sql:execute($conn, $q)
let $conn := sql:close($conn)

-----------------------------------------------------
|   id  |   element     |   created     |   updated |
-----------------------------------------------------
|   x   |   hello world |   xxxxx       |    xxxxx  |           <========   ? ( "<exemple>hello world</exemple>")

I actually need the full fragment. How to achieve that?

Thanks for your help.

Answer 1

Try serializing your element variable :

let $values :=  "('" ||  fn:serialize($element) || "','" || $currentDate|| "','" || $currentDate ||  "')"

Let me know how it goes