1

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.

Prasad
  • 45
  • 1
  • 11
  • 2
    It would help to provide an example with the expected outcome. – Erwin Brandstetter Mar 16 '15 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 Mar 17 '15 at 14:40
  • Why did you revert the improvements by a_horse? Please show example values. This might be of help: http://stackoverflow.com/questions/7491479/xml-data-to-postgresql-database/7628453#7628453 – Erwin Brandstetter Mar 17 '15 at 15:17
  • See also [XML element name from data in Postgres](https://stackoverflow.com/q/31203190/1048572) – Bergi Oct 10 '20 at 23:22

2 Answers2

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.

Prasad
  • 45
  • 1
  • 11
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.

  • 1
    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? – Prasad Mar 17 '15 at 20:03
  • @PrasadChinni: apart from writing your own function for that, no I don't think so. –  Mar 17 '15 at 20:15
  • I was trying to write a custom function. Is there any way to invoke the function for XMLElement name? Thanks. – Prasad Mar 30 '15 at 19:09