Create path with xquery if it does not exist

I have an xml structure that looks like this:

<data>
  <a>
    <element name="aName">123</element>
    <element n开发者_如何学Pythoname="xyz">foobar</element>
  </a>
  <b>
    <element name="aName">foo</element>
    <element name="otherName">bar</element>
  </b>
</data>

Which is saved in an XML column in a DB2 database. Within /a/element/@name is unique and /b/element/@name is unique etc.

Now I want to insert a new element tag in either ,. If an element with the same name attribute already exists, I want to replace that element (order of element tags do not matter). I did this like that:

update mytable set myXmlColumn=xmlquery('copy $new := $old modify
  (
    do delete $new/data/a/element[@name=$newName],
    do insert $insert as last into $new/data/a
  ) return $new'
  passing
    'aName' as "newName",
    xmlparse(document '<element name="aName">aaa</element>') as "insert"),
    myXmlColumn as "old"
) where id=123

This inserts the new element all right, removing another element with the same name if it existed. But know I want this to work even if the tag does not exist yet (in this case an empty tag should be created and the new element should be added to it) and it should still work if myXmlColumn was previously null.

For the latter requirement I found a solution although it strikes me as rather inelegant: In the passing section I can write coalesce(myXmlColumn,xmlparse(document '<data><a /></data>')), which will make the statement work if myXmlColumn is null. But what if it is not null, but just the is missing?

---

I found a solution that works, although it still feels awkward. I wonder if there is a more elegant solution...

update mytable set myXmlColumn=xmlquery('copy $xml := (if (not($xml/data/a)) then transform copy $xml := $xml modify (do insert <a/> as last into $xml/data) return $xml else $xml) modify
  (
    do delete $xml/data/a/element[@name=$newName],
    do insert $insert as last into $xml/data/a
  ) return $xml'
  passing
    'aName' as "newName",
    xmlparse(document '<element name="aName">aaa</element>') as "insert"),
    COALESCE(myXmlColumn, xmlparse(document '<data/>')) as "xml"
) where id=123

---

You can add if-then logic in your XQuery expression that will add different amounts of XML depending on which nodes exist. This approach allows you the most flexibility, since it can be programmed to handle almost any possible situation, such as where /data exists, but not /data/a. You could even decide what to do when the root document element is not <data>.

As for the COALESCE(), I like it and consider it a worthy workaround for the condition you've described.