insert node from one xml file into another using xquery for Exist-db

Question

I'm a SQL refugee new to Exist-db and I'm having so much trouble figuring out how to use xquery to take the value of node in file1.xml (a mysql dump) and add it to file2.xml (a TEI file). I want to match up the nodes from file1 with an xml:id value in file2.

I've googled around like crazy trying to understand how xquery updates work in Exist-DB, but can't seem to find a resource with more complex examples.

File1 looks like this:

<table_data name="places">
    <row>
        <field name="placeref">PLACEAFRICA</field>
                <field name="placename">Africa</field>
    </row>
    <row>
        <field name="placeref">PLACEILLINOIS</field>
        <field name="placename">Illinois</field>
    </row>
</table_data>

File 2 looks like this (and I want to match up where there's a name attribute placeref in file1 with the nodes where there's an xml:id in File2--the list of values is distinct in both documents):

<listPlace>
                <place xml:id="PLACEAFRICA">
                        <bibl>OBRATDS</bibl>
                    </place>
                    <place xml:id="PLACEILLINOIS">
                        <bibl>OBRATDS</bibl>
                    </place>
</listPlace>

What I want to happen:

<listPlace>
                <place xml:id="PLACEAFRICA">
                        <bibl>OBRATDS</bibl>
                        <name>Africa</name>
                    </place>
                    <place xml:id="PLACEILLINOIS">
                        <bibl>OBRATDS</bibl>
                        <name>Illinois</name>
                    </place>
</listPlace>

What's happening instead:

<listPlace>
                <place xml:id="PLACEAFRICA">
                        <bibl>OBRATDS</bibl>
                        <name>Africa</name>
                        <name>Illinois</name>
                    </place>
                    <place xml:id="PLACEILLINOIS">
                        <bibl>OBRATDS</bibl>
                        <name>Africa</name>
                        <name>Illinois</name>
                    </place>
</listPlace>

The xquery code I'm using:

let $file1 := doc('http://00.00.00.00/file1.xml')//row
let $file2 := doc('/db/madrid/xml/file2.xml')/* 
let $file2places := $file2//tei:place
let $file2placeref := $file2//tei:place/@xml:id
let $file1placeref := $file1//field[@name='placeref']//text() 
for  $file2placename in $file1//field[@name='placename']//text() 

let $placenamenodes :=  <name>{$file1placename}</name> 
where $file1placeref=$file2placeref 

return 
update insert $placenamenodes  into $file2places

Any help as to where I am going so (terribly) wrong or ideas for where to find resources on this sort of update (beyond the Exist-db resources) would be really appreciated. Apologies for the messiness of the code--I've tried so many different approaches.

Michael Westbay Michael Westbay · Accepted Answer · 2019-10-31T04:58:57

Your problem is a common one for beginners with XQuery.

$file2places has selected ALL places. You want to insert only into the $file2places where xml:id matches your placeref. In other words, you left out your WHERE clause for the target, thus inserting the new name tag into all records.

Try something like this:

(: Get source and target documents :)
let $rows := doc('http://00.00.00.00/file1.xml')//row
let $file2 := doc('/db/madrid/xml/file2.xml')/* 

(: Add place names by placerefs :)
for $row in $rows
  let $placeref := normalize-spaces($row/field[@name eq 'placeref'])
  let $placename := normalize-spaces($row/field[@name eq 'placename'])
  return update insert <name>{$placename}</name> 
    into $file2//place[@xml:id eq $placeref]

The normalize-spaces function takes all text values of an element, joins them together, and removes leading and trailing spaces plus makes all white space between words a single space. It's more efficient than using //text().

The main thing is to limit the places that are having a tag added to them with the [@xml:id eq $placeref] filter.

Hope this helps.

insert node from one xml file into another using xquery for Exist-db

1 Answers