Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XMLTable attribute value as column and not row

AllenS.Oct 29 2017 — edited Oct 30 2017

Hi,

I have the following XML data that I need to convert to table.

\<record>

    \<field name="ProfileInformation,Candidate,Number">67264\</field>

    \<field name="Staff\_SIAAmount,Code">10,000\</field>

\</record>

\<record>

    \<field name="ProfileInformation,Candidate,Number">51301\</field>

    \<field name="Staff\_SIAAmount,Code">10,000\</field>

\</record>

</ExportXML>

I have this script that I created based on my research online however it does not produce the desired results.

select *

from xmltable ('/ExportXML/record/field' passing XMLType.createXML('<ExportXML>

                    \<record>

                        \<field name="ProfileInformation,Candidate,Number">67264\</field>

                        \<field name="Staff\_SIAAmount,Code">10,000\</field>

                    \</record>

                    \<record>

                        \<field name="ProfileInformation,Candidate,Number">51301\</field>

                        \<field name="Staff\_SIAAmount,Code">10,000\</field>

                    \</record>

                \</ExportXML>') columns attribute varchar2(250) path '@name'

                                     , value varchar2(100) path '.' );

The screenshot below is the current output.

2017-10-30 10_09_58-APPS@HRDEV - SQLTools 1.8b38 - [Script 2 _].png

The screen shot below is the desired output which is what I'm trying to achieve.

2017-10-30 10_11_21-APPS@HRDEV - SQLTools 1.8b38 - [Script 2 _].png

Appreciate any help.

This post has been answered by cormaco on Oct 30 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 27 2017

Added on Oct 29 2017

#performance-availability, #xml-db

2 comments

3,371 views