GeoVIP GeoVIP - 1 year ago 105
SQL Question

How to concate joined table fields for xml

Hi have data structure like this :

CREATE TEMP TABLE test_names (
id serial primary key,
name character varying(50),
age int

INSERT INTO test_names(name,age) values ('name1',10),('name2',20);

CREATE TEMP TABLE test_names_details (
id serial primary key,
test_names_id int,
col1 int,
col2 int,
col3 int

INSERT INTO test_names_details(test_names_id,col1,col2,col3)

Want from this tables select data like xml :

<pn name="name">name1</pn>
<age name="age">10</age>
<col1 name="col1">2</col1>
<col2 name="col2">3</col1>
<col3 name="col3">4</col1>
<col1 name="col1">5</col1>
<col2 name="col2">6</col1>
<col3 name="col3">7</col1>
<col1 name="col1">8</col1>
<col2 name="col2">9</col1>
<col3 name="col3">10</col1>

<pn name="name">name2</pn>
<age name="age">20</age>
<col1 name="col1">20</col1>
<col2 name="col2">21</col1>
<col3 name="col3">22</col1>
<col1 name="col1">23</col1>
<col2 name="col2">24</col1>
<col3 name="col3">25</col1>


How to do it ?

Answer Source

Wrap this in a function and off you go! ;-)

'<info>' || 
    '<maininfo>' ||
        '<pn name="name">' || name || '</pn>' ||
        '<age name="age">' || age || '</age>' ||
    '</maininfo>' ||
    '<data>' ||
        '<row>' ||
            '<col name="col1">'||col1||'</col>' ||
            '<col name="col2">'||col2||'</col>' ||
            '<col name="col3">'||col3||'</col>' ||
        '</row>', '')
        FROM test_names_details WHERE test_names_id =
        ) ||
    , '') ||
FROM test_names
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download