oracle evalname function equivalent in postgres

Question

Oracle supports dynamic XMLElement name with evalname function. Is there a similar feature in postgres to get the XMLElement name dynamically instead of using constant?

Example in ORACLE:

select xmlelement(evalname(ENAME),EMPNO) from EMP;

This statement will result in list of enames as separate xml elements.

<SMITH>7369</SMITH>
<ALLEN>7499</ALLEN>
<WARD>7521</WARD>

Not sure if postgres has something similar.

Thanks.

It would help to provide an example with the expected outcome. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 16, 2015 at 22:39
Please edit the question to improve it. Never hide essential information in comments. Click on "edit" left under your question. And add the original XML value to make the example more useful. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 17, 2015 at 14:40
Why did you revert the improvements by a_horse? Please show example values. This might be of help: stackoverflow.com/questions/7491479/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 17, 2015 at 15:17

Prasad · Accepted Answer · 2015-04-10 19:03:49Z

3

I was able to get a workaround to construct xml with dynamic element names in Postgres using execute format. Posting this just in case if anyone had same issue.

execute format('SELECT XMLElement(NAME %I, $1)', emp_name) USING empno from emp;

<SMITH>7369</SMITH>
<ALLEN>7499</ALLEN>

Same worked with XMLForest and having XMLAttributes inside XMLElement.

answered Apr 10, 2015 at 19:03

Prasad

551 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2015-03-17 19:02:33Z

0

There is no such function as far as I know.

The closest you can get is adding an attribute with the empname:

select xmlelement(name emp, xmlattributes(empname), empno)
from emp;

Generates:

<emp empname="Smith">7369</emp>
<emp empname="Allend">7499</emp>
<emp empname="Ward">7521</emp>

Personally I would find that format much easier to parse e.g. in XSLT or an XML parser. Because in order to process a tag you would need to know the tag name, which you don't if the tag changes for each row - but this might just be me.

answered Mar 17, 2015 at 19:02

user330315

3 Comments

Prasad Over a year ago

Thanks for inputs. This doesn't help for me coz I am trying to migrate existing function from oracle and was trying avoid changing the xml schema. Is there any way to get dynamic values for names either by selecting from table or calling function which returns list of columns instead of having a constant value for XMLElement name?

user330315 Over a year ago

@PrasadChinni: apart from writing your own function for that, no I don't think so.

Prasad Over a year ago

I was trying to write a custom function. Is there any way to invoke the function for XMLElement name? Thanks.

Collectives™ on Stack Overflow

oracle evalname function equivalent in postgres

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related