1

I have an xml document saved in a DB2 Table with XML datatype and I want to update the value of a node. I tried this:

XQUERY replace value of node db2-fn:sqlquery('select my_xml_column from myTable where someId = someValue)/some/xpath/with/@attribute with "foobar"

(and I tried several variants, everything that google hinted that it could do the job). But unfortunatelly I am just getting error messages. Here:

SQL16002N  An XQuery expression has an unexpected token "value" following "replace ". Expected tokens may include: "

What am I doing wrong?

yankee
  • 38,872
  • 15
  • 103
  • 162
  • @yankee: Do you use the proper DB2 server version? (Looks like from 9.5 there is XQuery Update support) Do you use the proper SQL host expression like the examples at this articule http://www.ibm.com/developerworks/data/library/techarticle/dm-0710nicola/ ? –  Feb 03 '11 at 22:58
  • @Alejandro: Yes, I am using DB2 V9.7 and it does have XQuery support. Running simple XQuerys like "XQUERY db2-fn:sqlquery(...)/some/xpath" works. What do you mean with "host expression"? In the article you mentioned xquery is unfortunatelly not used – yankee Feb 03 '11 at 23:07
  • @yankee: You are using [XQuery Update Facility](http://www.w3.org/TR/xquery-update-10/) and from the articule I've linked you have to embed this XQuery Update expressions into an not standard DB2 specific SQL expression. –  Feb 03 '11 at 23:30
  • @Alejandro: which I seem to be failing to do correctly... – yankee Feb 04 '11 at 00:00
  • @yankee: You wrote *"This works and does what I want it to do"*. Then you should post that as an answer instead of a question update. It's not bad to answer your own questions. –  Feb 04 '11 at 00:07
  • @Alejandro: I'd call this a workaround. Yes, it does end up having the effect that I desire, but I still hope that somebody may come up with an XQuery solution that is not based on the xmlquery function and based on copying the whole document, changing one node and copying everything back. Perhaps I should rephrase this... – yankee Feb 04 '11 at 01:24
  • might be interesting for you - all db2 xml possibilities http://amolnpujari.wordpress.com/2008/04/12/db2-native-xml/ – Amol Pujari Dec 28 '13 at 07:07

1 Answers1

1

update myTable SET myXmlColumn = XMLQUERY(' transform copy $copy := $original modify do replace value of $copy/some/xpath/with/@attribute with "FOOBAR" return $copy ' PASSING myXmlColumn AS "original" ) WHERE someId = someValue

This works and has the desired effect. It hoped for somebody to come up with a pure XQuery solution, but the problem is solved...

Fred Sobotka
  • 5,252
  • 22
  • 32
yankee
  • 38,872
  • 15
  • 103
  • 162