creating tde template with element present at multiple places in xml

Question

I am working on TDE using MarkLogic. I have a specific scenario that I need to pull city that is available at different levels in XML. The city value has to go to the same column value not a tab delimited or anything if there are multiple values, but has to be treated as another row for a person/name i.e John Doe.

Below is the data I am using:

let $template= 
<template xmlns="http://marklogic.com/xdmp/tde>
<context>/data/person/address</context>
<rows>
<row>
<schema-name>test</schema-name>
<view-name>data</view-name>
<columns>
<column>
<name>address</name>
<scalar-type>string</scalar-type>
<val>/city</val>
</column>
</columns>
</row>
</rows>
</template>

Data I am using is below:

<data>
    <person>
        <name>John Doe</name>
        <age>30</age>
        <address>
            <city>New York</city>
            <country>USA</country>
        </address>
    </person>
    <employee>     
        <education>graduated</education>
        <address>
            <city>London</city>
            <country>UK</country>
        </address>
    </employee>
</data>

If I query my tde I should get below output

name	age	education	city
John Doe	30	null	New York
John Doe	30	graduated	London

Answer 1

You could set the context to be /data/*, which would match on either /data/person or /data/education, or you could also match on /data/*/address.

With the context on the repeating items that you want to create rows from, you can then use relative XPaths to select the values for the other columns.

Since education will only be present for the employee and not the person you need to mark that column as nullable.

A complete example:

let $data :=
<data>
    <person>
        <name>John Doe</name>
        <age>30</age>
        <address>
            <city>New York</city>
            <country>USA</country>
        </address>
    </person>
    <employee>     
        <education>graduated</education>
        <address>
            <city>London</city>
            <country>UK</country>
        </address>
    </employee>
</data>

let $template := 
<template xmlns="http://marklogic.com/xdmp/tde">
  <context>/data/*</context>
  <rows>
    <row>
      <schema-name>test</schema-name>
      <view-name>data</view-name>
      <columns>
        <column>
          <name>name</name>
          <scalar-type>string</scalar-type>
          <val>../person/name</val>
        </column>
        <column>
          <name>age</name>
          <scalar-type>int</scalar-type>
          <val>../person/age</val>
        </column>
        <column>
          <name>education</name>
          <scalar-type>string</scalar-type>
          <val>education</val>
          <nullable>true</nullable>
        </column>
        <column>
          <name>city</name>
          <scalar-type>string</scalar-type>
          <val>address/city</val>
          <nullable>true</nullable>
        </column>
      </columns>
    </row>
  </rows>
</template>

return 
  tde:node-data-extract($data, $template)