1

I have data in a table like this:

ID  Date        Side      Qty
1   2015-07-01  buy       1000
2   2015-07-02  buy       1000
3   2015-07-03  sell      1000
4   2015-07-04  sell      1000

I need to extract it as XML in a format like this:

<trades>
    <buy>
        <date>2015-07-01</date>
        <qty>1000</qty>
    </buy>
    ...
    <sell>
        <date>2015-07-03</date>
        <qty>1000</qty>      
    </sell>
    ...
</trades>

The data format I am working to specifies that each child entry has to be contained in a tag <buy> or <sell> which means that the name of the tag has to be generated from the "Side" column. The trouble is I don't know how to set the name of an xml element from data or even if this is possible. This is my code:

select 
xmlelement(
        name "trades",
        xmlagg(  
        xmlelement(name Side
            xmlconcat(
                xmlelement(name "date", Date), ...

This generates 

<trades>
    <Side>
        <date>2015-07-01</date>
        <qty>1000</qty>
    </Side>
    ...
    <Side>
        <date>2015-07-03</date>
        <qty>1000</qty>      
    </Side>
    ...
</trades>

In other words, the word "Side" gets used instead of the value of the column. Is it possible to use data values as xml element names in Postgres?

I'm using 9.4

Improve this question

1 Answer 1

Reset to default
3

It is not possible to use any SQL/XML function with dynamic tagname. You can use a format function.

postgres=# SELECT * FROM foo;
┌──────┬──────┐
│  nm  │ val  │
╞══════╪══════╡
│ buy  │  100 │
│ sell │ 1000 │
│ buy  │  102 │
│ sell │ 1033 │
└──────┴──────┘
(4 rows)

postgres=# SELECT xmlelement(name "trades", xmlagg(x)) 
              FROM (SELECT format('<%1$s>%2$s</%1$s>', 
                                   nm, 
                                   xmlagg(xmlelement(name value, val)))::xml x 
                        FROM foo group by nm) s;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                    xmlelement                                                       │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ <trades><sell><value>1000</value><value>1033</value></sell><buy><value>100</value><value>102</value></buy></trades> │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. It thought that might be the case. Your solution is a nice one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.