Question: While using Nokogiri::XML::Builder I need to be able to generate a node that also replaces a regex match on the text with some other XML.

Currently I'm able to add additional XML inside the node. Here's an example;

```
def xml
  Nokogiri::XML::Builder.new do |xml|
    xml.chapter {
      xml.para {
        xml.parent.add_child("Testing[1] footnote paragraph.")
        add_footnotes(xml, 'An Entry')
      }
    }
  end.to_xml
end

# further child nodes WILL be added to footnote
def add_footnotes(xml, text)
  xml.footnote text
end

```

which produces;

```
<chapter>
  <para>Testing[1] footnote paragraph.<footnote>An Entry</footnote></para>
</chapter>

```

But I need to be able to run a regex replace on the reference `[1]`, replacing it with the `<footnote>` XML, producing output like the following;

```
<chapter>
  <para>Testing<footnote>An Entry</footnote> footnote paragraph.</para>
</chapter>

```

I'm making the assumption here that the `add_footnotes` method would receive the reference match (e.g. as `$1`), which would be used to pull the appropriate footnote from a collection.

That method would also be adding additional child nodes, such as the following;

```
<footnote>
  <para>Words.</para>
  <para>More words.</para>
</footnote>

```

Can anyone help?

Answer: 
You can use "listagg"

Example:

```
SELECT LISTAGG(columnname, ',') WITHIN GROUP (ORDER BY columname)

```

Hope it resolves the issue!
You can use the xmlelement function.

```
SELECT
   RTRIM (XMLAGG (XMLELEMENT (E, EMPLOYEENAME|| ', ')).EXTRACT ('//text()'), ',') ENAMES
FROM
   EMPLOYEE;

```