Attach multiple attribute concatenations using node values ​​using xpath In Teiid

Question

This is a copy of my question posted here I only need to notice that this is happening in XMLTABLE for the teiid mechanism. I am 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 a typed xml variable with content at the top.

I am trying to format and merge this into one line using xpath. Sort of:

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

I think I'm somewhere nearby, because this:

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

works and provides a comma separated list of roles: Role1, Role2, Role3

and this one

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

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

I need a final conclusion

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

The most I could get was:

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

- . - concat, , . .

Answer 1

xpath

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

xpath . , , ,

/AttributeSets/ItemAttributes/Creator

string-join W3C .

Update:

, - , :... , : Role1, Role2, Role3

, . , :

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

- concat, , .

, - xpath , . Infact - xapth :

Creator OneRole1, Creator TwoRole2, Creator ThreeRole3