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.

coralie · Accepted Answer · 2021-07-01 13:13:53Z

1

Try serializing your element variable :

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

Let me know how it goes

answered Jul 1, 2021 at 13:13

coralie

341 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert XML Content in db using SQL Module

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related