Join multiple concatenations of attributes with node values using xpath In Teiid

This is a very copy of my question posted here I only need to notice that this is happening in the XMLTABLE of the teiid engine. I'm parsing the following xml content:

<AttributeSets>
    <ns2:ItemAttributes xml:lang="de-DE">
        <ns2:Creator Role="Role1">Creator One</ns2:Creator>
        <ns2:Creator Role="Role2">Creator Two</ns2:Creator>
        <ns2:Creator Role="Role3">Creator Three</ns2:Creator>
    </ns2:ItemAttributes>
</AttributeSets>

using

    Select * From
        XMLTABLE(XMLNAMESPACES( DEFAULT 'http://ns1',
            'http://ns2/default.xsd' as ns2 ),
            'AttributeSets/ns2:ItemAttributes' PASSING x.AttributeSets
        COLUMNS 
            Creator STRING PATH 'string-join(//ns2:Creator/(@Role, node()) , '', '')'
    ) p;

Here x.AttributeSets is an xml typed variable with the content from above.

I'm trying to format and combine this into one line using xpath. Something like:

string-join(//ns2:Creator/concat(./text(), @Role), ', ')

I think, i'm somewhere close, because this:

string-join(//ns2:Creator/@Role , ', ')

works and gives me a comma-separated list of roles: Role1, Role2, Role3

and this

string-join(//ns2:Creator/node(), ', ')

combines the values of creators: "Creator One, Creator Two, Creator Three".

I'd like the final output of

Role1: Creator One, Role2: Creator Two, Role3: Creator Three

The most I could get was:

 string-join(//ns2:Creator/(@Role, node()) , ', ')

This comma-separates all roles and creators into one line. For some reason the concat operator seems no to work. Could you please help.

Solution

Try the following xpath

string-join(for $n in /AttributeSets/ItemAttributes/Creator return concat($n/@Role, ':',$n/text()),',')

Remember to adjust the selector xpath within the for as per your source tree context. I assumed document root hence

/AttributeSets/ItemAttributes/Creator

The documentation for the string-join function at W3C has a pretty similar example.

Update:

I think, i'm somewhere close, because this: ... works and gives me a comma-separated list of roles: Role1, Role2, Role3

Think you were quite close. I tried a small tweak to that and this worked:

string-join(//ns2:Creator/concat(@Role, ':', ./text()), ', ')

For some reason the concat operator seems no to work.

Not sure about that, I checked with an online xpath tester here and it works perfectly. Infact the online tool accepts your xapth as well with the following output:

Creator OneRole1, Creator TwoRole2, Creator ThreeRole3