Postgres Pro
Downloads
Home   >   mailing lists

Thread: Get attributes names

Get attributes names

From
ramsiddu007
Date:
Dear all,
              I hope you are all doing well. Today i got one requirement, for that i need attribute names as below example.

Xml:
<emp>
<emp_details emp_id="101"  emp_name="tom" dept="development"/>
</emp>

In the above sample input xml, i want attributes list of emp_deails like.
Attributes
___________
emp_id
emp_name
dept


Thanking you,

Re: Get attributes names

From
Arthur Zakirov
Date:
On 24.01.2019 11:54, ramsiddu007 wrote:
> Dear all,
>                I hope you are all doing well. Today i got one 
> requirement, for that i need attribute names as below example.
> 
> Xml:
> <emp>
> <emp_details emp_id="101"  emp_name="tom" dept="development"/>
> </emp>
> 
> In the above sample input xml, i want attributes list of emp_deails like.
> Attributes
> ___________
> emp_id
> emp_name
> dept
> 
> 
> Thanking you,
> 
In PostgreSQL 11 (and 10 I think) you can do something like this:

=# CREATE TABLE xmldata AS SELECT xml $$
<emp>
<emp_details emp_id="101"  emp_name="tom" dept="development"/>
</emp>
$$
as data;
=# SELECT xmltable.*
FROM xmldata,
   xmltable('/emp/emp_details/@*' passing data
     columns atrr text path 'name()');
    atrr
----------
  emp_id
  emp_name
  dept

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Re: Get attributes names

From
ramsiddu007
Date:
Hi Arthur,
                  Thanks for your help. But am using postgres 9.6 version.

On Thu, 24 Jan 2019, 15:24 Arthur Zakirov <a.zakirov@postgrespro.ru wrote:
On 24.01.2019 11:54, ramsiddu007 wrote:
> Dear all,
>                I hope you are all doing well. Today i got one
> requirement, for that i need attribute names as below example.
>
> Xml:
> <emp>
> <emp_details emp_id="101"  emp_name="tom" dept="development"/>
> </emp>
>
> In the above sample input xml, i want attributes list of emp_deails like.
> Attributes
> ___________
> emp_id
> emp_name
> dept
>
>
> Thanking you,
>
In PostgreSQL 11 (and 10 I think) you can do something like this:

=# CREATE TABLE xmldata AS SELECT xml $$
<emp>
<emp_details emp_id="101"  emp_name="tom" dept="development"/>
</emp>
$$
as data;
=# SELECT xmltable.*
FROM xmldata,
   xmltable('/emp/emp_details/@*' passing data
     columns atrr text path 'name()');
    atrr
----------
  emp_id
  emp_name
  dept

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company