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

this copy of question posted here need notice happening in xmltable of teiid engine. i'm parsing 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 *         xmltable(xmlnamespaces( default 'http://ns1',             'http://ns2/default.xsd' ns2 ),             'attributesets/ns2:itemattributes' passing x.attributesets         columns              creator string path 'string-join(//ns2:creator/(@role, node()) , '', '')'     ) p; 

here x.attributesets xml typed variable content above.

i'm trying format , combine 1 line using xpath. like:

string-join(//ns2:creator/concat(./text(), @role), ', ') 

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

string-join(//ns2:creator/@role , ', ') 

works , gives me comma-separated list of roles: role1, role2, role3

and this

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

combines values of creators: "creator one, creator two, creator three".

i'd final output of

role1: creator one, role2: creator two, role3: creator 3 

the was:

 string-join(//ns2:creator/(@role, node()) , ', ') 

this comma-separates roles , creators 1 line. reason concat operator seems no work. please help.

try following xpath

string-join(for $n in /attributesets/itemattributes/creator return concat($n/@role, ':',$n/text()),',') 

remember adjust selector xpath within for per source tree context. assumed document root hence

/attributesets/itemattributes/creator 

the documentation string-join function @ w3c has pretty similar example.

update:

i think, i'm somewhere close, because this: ... works , gives me comma-separated list of roles: role1, role2, role3

think quite close. tried small tweak , worked:

string-join(//ns2:creator/concat(@role, ':', ./text()), ', ') 

for reason concat operator seems no work.

not sure that, checked online xpath tester here , works perfectly. infact online tool accepts xapth following output:

creator onerole1, creator tworole2, creator threerole3