Search code examples
xmlxpathxqueryexist-db

Detect a gap / the first ID available in a xml:id sequence with XQuery/Xpath

I have a master xml file with a list like this:

<listPerson>
    <person xml:id="pe0001">
        <persName>
            <surname>Anderson</surname>
            [...]
       </persName>
    </person>
    <person xml:id="pe0002">
        <persName>
            <surname>Smith</surname>
            [...]
        </persName>
    </person>
    <person xml:id="pe0004">
        <persName>
            <surname>Another</surname>
            [...]
        </persName>
    </person>
</listPerson>

I have an html form which call an app in the app.xql and insert a new <person> record inside the master xml file. If there's a gap in the ID sequence (e.g ID pe0003 above) I'd like eXist-db to return that ID and 'fill the gap', otherwise just output the latest available ID (i.e. pe0005). I'm already accomplishing this last thing with:

declare function app:addPers($node as node(), $model as map(*)) {

    let $peid := doc('masterfile.xml')//tei:listPerson/tei:person[@xml:id][last()]/@xml:id
    let $idnumber := xs:decimal(substring-after($peid, 'pe'))
    let $newidnumber := (sum($idnumber + 1))
    let $newpeid := concat('pe0', $newidnumber)

    return

<html stuff>

}

What I'd like to do now is having an XQuery/Xpath code which detects when there's a gap in the sequence and acts accordingly. This is what I've done so far:

[app.xql]

declare function app:addPers($node as node(), $model as map(*)) {

let $seqpe := doc('masterfile.xml')//tei:listPerson/tei:person[@xml:id]/@xml:id
        let $peid := 
        for $item at $pos in $seqpe
            let $item := xs:decimal(substring-after($seqpe, 'pe'))
            return if ($item[$pos + 1] - $item[$pos] != 1) then 
            doc('masterfile.xml')//tei:listPerson/tei:person[@xml:id][$item]/@xml:id
        else 
        doc('masterfile.xml')//tei:listPerson/tei:person[@xml:id][last()]/@xml:id

        let $newidnumber := (sum($peid + 1))
        let $newpeid := concat('pe0', $newidnumber)
 return 

<html stuff>

}

This returns a err:FORG0001 cannot construct xs:decimal from "" error. What am I doing wrong?

Update

This is another test I made, which returns a err:XPDY0002 Undefined context sequence for 'following-sibling::tei:person error:

let $seqpe := doc('masterfile.xml')//tei:listPerson/tei:person
         let $peid := 
         for $item in $seqpe
             return if ((xs:decimal(substring-after(following-sibling::tei:person/@xml:id, 'pe'))) - (xs:decimal(substring-after($item/@xml:id, 'pe'))) ne 1) then 
             doc('masterfile.xml')//tei:listPerson/tei:person[@xml:id eq $item/@xml:id]/@xml:id
         else 
         doc('masterfile.xml')//tei:listPerson/tei:person[@xml:id][last()]/@xml:id

     let $newidnumber := (sum($peid + 1))
     let $newpeid := concat('pe0', $newidnumber)

SECOND UPDATE

As far as returning the last ID is concerned, both this code:

(let $idnext :=

  for $person in doc('/db/apps/app-ct/data/indices/pedb.xml')//tei:listPerson/tei:person[position() ne last()]
  where local:get-id($person/@xml:id) ne (local:get-id($person/following-sibling::tei:person[1]/@xml:id) - 1)
return 
    if (empty($idnext)) then
    (local:get-id(listPerson/person[last()]/@xml:id) + 1)
    else (local:get-id($person/@xml:id) + 1)
let $newpeid := 
if (fn:string-length($idnext) = 1) then
   concat('pe000', $idnext) else if
   (fn:string-length($idnext) = 2) then 
   concat('pe00', $idnext) else if 
   (fn:string-length($idnext) = 3) then 
   concat('pe0', $idnext) else 
   concat('pe', $idnext)

return

<html stuff>)[1]

and this:

    (let $idnext :=

      for $person in doc('/db/apps/app-ct/data/indices/pedb.xml')//tei:listPerson/tei:person[position() ne last()]
      where local:get-id($person/@xml:id) ne (local:get-id($person/following-sibling::tei:person[1]/@xml:id) - 1)
      return local:get-id($person/@xml:id) + 1
    return 
        if (empty($idnext)) then
        (local:get-id(listPerson/person[last()]/@xml:id) + 1)
        else ($idnext),
    let $newpeid := 
    if (fn:string-length($idnext) = 1) then
       concat('pe000', $idnext) else if 
       (fn:string-length($idnext) = 2) then 
       concat('pe00', $idnext) else if 
       (fn:string-length($idnext) = 3) then 
       concat('pe0', $idnext) else 
       concat('pe', $idnext)

    return

<html stuff>)[1]

return a err:XPDY0002 variable '$idnext' is not set. error.

Third and final update

Below is the code which does precisely what I want, i.e. returning the first available ID, wheter it's within a gap or not.

let $id_gap :=

        (for $person in doc('myfile.xml')//tei:listPerson/tei:person[position() ne last()]
        where local:get-id($person/@xml:id) ne (local:get-id($person/following-sibling::tei:person[1]/@xml:id) - 1)
        return (local:get-id($person/@xml:id) + 1))[1]

        let $idnext :=
        if (empty($id_gap))
        then (local:get-id(doc('myfile.xml')//tei:listPerson/tei:person[last()]/@xml:id) + 1)
        else ($id_gap)

        let $newpeid := 
         if (fn:string-length($idnext) = 1) then
            concat('pe000', $idnext) else if 
            (fn:string-length($idnext) = 2) then 
            concat('pe00', $idnext) else if 
            (fn:string-length($idnext) = 3) then 
            concat('pe0', $idnext) else 
            concat('pe', $idnext)

       return

  <html code>
Solution

  • I tried it like this:

    declare function local:get-id($xml-id as xs:string) as xs:integer {
    xs:integer(replace($xml-id, '[^0-9]+', ''))
};

for $person in (listPerson/person)[position() ne last()]
where local:get-id($person/@xml:id) ne (local:get-id($person/following-sibling::person[1]/@xml:id) - 1)
return local:get-id($person/@xml:id) + 1

    and at http://xqueryfiddle.liberty-development.net/nbUY4kh for the sample input

    <listPerson>
    <person xml:id="pe0001">
        <persName>
            <surname>Anderson</surname>
            [...]
       </persName>
    </person>
    <person xml:id="pe0003">
        <persName>
            <surname>Smith</surname>
            [...]
        </persName>
    </person>
    <person xml:id="pe0004">
        <persName>
            <surname>Another</surname>
            [...]
        </persName>
    </person>
    <person xml:id="pe0005">
        <persName>
            <surname>Another</surname>
            [...]
        </persName>
    </person>
    <person xml:id="pe0006">
        <persName>
            <surname>Another</surname>
            [...]
        </persName>
    </person>
    <person xml:id="pe0008">
        <persName>
            <surname>Another</surname>
            [...]
        </persName>
    </person>
    <person xml:id="pe0009">
        <persName>
            <surname>Another</surname>
            [...]
        </persName>
    </person>
    <person xml:id="pe0010">
        <persName>
            <surname>Another</surname>
            [...]
        </persName>
    </person>
    <person xml:id="pe0014">
        <persName>
            <surname>Another</surname>
            [...]
        </persName>
    </person>
</listPerson>

    it gives

    2
7
11

    It might also be doable by a window clause although I am not sure Exist-Db supports that.

    As for returning a new id if there are no gaps, I am not sure whether there is a more elegant or compact solution but I guess a simple check

    let $new-ids :=
    for $person in (listPerson/person)[position() ne last()]
    where local:get-id($person/@xml:id) ne (local:get-id($person/following-sibling::person[1]/@xml:id) - 1)
    return local:get-id($person/@xml:id) + 1
return
    if (empty($new-ids))
    then local:get-id(listPerson/person[last()]/@xml:id) + 1
    else $new-ids

    implements your verbal description: http://xqueryfiddle.liberty-development.net/nbUY4kh/2