My Oracle Support Banner

How To Create Relational Data Out Of Xml Data Master Detail View Using XMLType (Doc ID 757063.1)

Last updated on APRIL 29, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

I need to retrieve "addresses" in a way that preserves the correct list of addresses for each customer even though there are no IDs for the addresses. This is a Master-Detail query.

This is the data:

<CustomerList> 
        <Customer> 
                <ID>1</ID> 
                <FIRST-NAME>Richard</FIRST-NAME> 
                <LAST-NAME>xxx</LAST-NAME> 
                <ADDRESSES> 
                        <ADDRESS> 
                                <Line1>883</Line1> 
                                <Line2>xyz</Line2> 
                                <City>213</City> 
                                <State>IL></State> 
                                <ZIP>xxxx</ZIP> 
                        </ADDRESS> 
                        <ADDRESS> 
                                <Line1>8e xxf</Line1> 
                                <Line2>937 xxxx</Line2> 
                                <City>o23k2</City> 
                                <State>TX></State> 
                                <ZIP>xxxx</ZIP> 
                        </ADDRESS> 
                        <ADDRESS> 
                                <Line1>423</Line1> 
                                <Line2>xfxsz</Line2> 
                                <City>xxx</City> 
                                <State>IL></State> 
                                <ZIP>xxxx</ZIP> 
                        </ADDRESS> 
                        <ADDRESS> 
                                <Line1>3cxA XXW</Line1> 
                                <Line2>WQ IIO</Line2> 
                                <City>xxx</City> 
                                <State>xx></State> 
                                <ZIP>xxx</ZIP> 
                        </ADDRESS> 
                </ADDRESSES> 
        </Customer> 
        <Customer> 
                <ID>2</ID> 
                <FIRST-NAME>Maira</FIRST-NAME> 
                <LAST-NAME>Joes</LAST-NAME> 
                <ADDRESSES> 
                        <ADDRESS> 
                                <Line1>332 fairway</Line1> 
                                <Line2>abc j932j1a</Line2> 
                                <City>St. xxxx</City> 
                                <State>MO></State> 
                                <ZIP>xxx</ZIP> 
                        </ADDRESS> 
                        <ADDRESS> 
                                <Line1>9099</Line1> 
                                <Line2>9l./;/ldsja</Line2> 
                                <City>w2123</City> 
                                <State>xx></State> 
                                <ZIP>xxx</ZIP> 
                        </ADDRESS> 
                        <ADDRESS> 
                                <Line1>xxx</Line1> 
                                <Line2>2qsfaq</Line2> 
                                <City>222</City> 
                                <State>xx></State> 
                                <ZIP>xxxx</ZIP> 
                        </ADDRESS> 
                </ADDRESSES> 
        </Customer> 
</CustomerList> 
 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.