Oracle Database Discussions

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!

Select xml data problem

Frank LehmannApr 17 2024

Hi,

we have a xml data type like this:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Header></soap:Header><soap:Body><getCompanyListResponse xmlns="http://soap.mareon.com/hwa24/schema"><company><unr>599130000</unr><address><role>LOCATION</role><name></name><firstName>DRITTERP SOAP Test</firstName><street>Isaac-Fulda-Allee 6</street><zip>55124</zip><city>Mainz</city></address><settings><signature>true</signature><archive>true</archive><forceRestrictToOrderPositions>true</forceRestrictToOrderPositions><disable13b>true</disable13b><disable35a>false</disable35a><failInvoiceSum>false</failInvoiceSum><disableTenantLiable>false</disableTenantLiable><disableDiscount>false</disableDiscount><disableInvoiceText>false</disableInvoiceText><disablePartialInvoices>false</disablePartialInvoices><disableInsuranceClaims>true</disableInsuranceClaims><disableSkonto>true</disableSkonto><disableInvoices>false</disableInvoices><inventoryManagement>false</inventoryManagement><restrictCancellationReasons>true</restrictCancellationReasons><cancellationReason>Ferien</cancellationReason><cancellationReason>Krankheit</cancellationReason><cancellationReason>Insolvenz</cancellationReason><erpType>GU</erpType><disableTermofPayment>false</disableTermofPayment><invoiceWithoutOrder>false</invoiceWithoutOrder><maxLenHWInvoiceNumber>25</maxLenHWInvoiceNumber><usesSubUnrConditions>false</usesSubUnrConditions><usesFpaWithDiscount>false</usesFpaWithDiscount><allFPA>false</allFPA><enableConditionalAcceptanceForOrder>true</enableConditionalAcceptanceForOrder><enableParkingOrder>true</enableParkingOrder><invoiceWithoutWorkDone>false</invoiceWithoutWorkDone></settings><subCompany><subUnr>599130100</subUnr><address><role>LOCATION</role><name></name><firstName>GU SOAP Test </firstName><street>Isaac-Fulda-Allee 6</street><zip>55124</zip><city>Mainz</city></address><settings><signature>true</signature><archive>true</archive><forceRestrictToOrderPositions>true</forceRestrictToOrderPositions><disable13b>true</disable13b><disable35a>false</disable35a><failInvoiceSum>false</failInvoiceSum><disableTenantLiable>false</disableTenantLiable><disableDiscount>false</disableDiscount><disableInvoiceText>false</disableInvoiceText><disablePartialInvoices>false</disablePartialInvoices><disableInsuranceClaims>true</disableInsuranceClaims><disableSkonto>true</disableSkonto><disableInvoices>false</disableInvoices><inventoryManagement>false</inventoryManagement><restrictCancellationReasons>true</restrictCancellationReasons><cancellationReason>Ferien</cancellationReason><cancellationReason>Krankheit</cancellationReason><cancellationReason>Insolvenz</cancellationReason><erpType>GU</erpType><disableTermofPayment>false</disableTermofPayment><invoiceWithoutOrder>false</invoiceWithoutOrder><maxLenHWInvoiceNumber>25</maxLenHWInvoiceNumber><usesSubUnrConditions>false</usesSubUnrConditions><usesFpaWithDiscount>false</usesFpaWithDiscount><allFPA>false</allFPA><enableConditionalAcceptanceForOrder>true</enableConditionalAcceptanceForOrder><enableParkingOrder>true</enableParkingOrder><invoiceWithoutWorkDone>false</invoiceWithoutWorkDone></settings></subCompany><subCompany><subUnr>599130000</subUnr><address><role>LOCATION</role><name></name><firstName>DRITTERP SOAP Test</firstName><street>Isaac-Fulda-Allee 6</street><zip>55124</zip><city>Mainz</city></address><settings><signature>true</signature><archive>true</archive><forceRestrictToOrderPositions>true</forceRestrictToOrderPositions><disable13b>true</disable13b><disable35a>false</disable35a><failInvoiceSum>false</failInvoiceSum><disableTenantLiable>false</disableTenantLiable><disableDiscount>false</disableDiscount><disableInvoiceText>false</disableInvoiceText><disablePartialInvoices>false</disablePartialInvoices><disableInsuranceClaims>true</disableInsuranceClaims><disableSkonto>true</disableSkonto><disableInvoices>false</disableInvoices><inventoryManagement>false</inventoryManagement><restrictCancellationReasons>true</restrictCancellationReasons><cancellationReason>Ferien</cancellationReason><cancellationReason>Krankheit</cancellationReason><cancellationReason>Insolvenz</cancellationReason><erpType>GU</erpType><disableTermofPayment>false</disableTermofPayment><invoiceWithoutOrder>false</invoiceWithoutOrder><maxLenHWInvoiceNumber>25</maxLenHWInvoiceNumber><usesSubUnrConditions>false</usesSubUnrConditions><usesFpaWithDiscount>false</usesFpaWithDiscount><allFPA>false</allFPA><enableConditionalAcceptanceForOrder>true</enableConditionalAcceptanceForOrder><enableParkingOrder>true</enableParkingOrder><invoiceWithoutWorkDone>false</invoiceWithoutWorkDone></settings></subCompany><subCompany><subUnr>599130200</subUnr><address><role>LOCATION</role><name></name><firstName>GU SOAP Test </firstName><street>Isaac-Fulda-Allee 6</street><zip>55124</zip><city>Mainz</city></address><settings><signature>true</signature><archive>true</archive><forceRestrictToOrderPositions>true</forceRestrictToOrderPositions><disable13b>true</disable13b><disable35a>false</disable35a><failInvoiceSum>false</failInvoiceSum><disableTenantLiable>false</disableTenantLiable><disableDiscount>false</disableDiscount><disableInvoiceText>false</disableInvoiceText><disablePartialInvoices>false</disablePartialInvoices><disableInsuranceClaims>true</disableInsuranceClaims><disableSkonto>true</disableSkonto><disableInvoices>false</disableInvoices><inventoryManagement>false</inventoryManagement><restrictCancellationReasons>true</restrictCancellationReasons><cancellationReason>Ferien</cancellationReason><cancellationReason>Krankheit</cancellationReason><cancellationReason>Insolvenz</cancellationReason><erpType>GU</erpType><disableTermofPayment>false</disableTermofPayment><invoiceWithoutOrder>false</invoiceWithoutOrder><maxLenHWInvoiceNumber>25</maxLenHWInvoiceNumber><usesSubUnrConditions>false</usesSubUnrConditions><usesFpaWithDiscount>false</usesFpaWithDiscount><allFPA>false</allFPA><enableConditionalAcceptanceForOrder>true</enableConditionalAcceptanceForOrder><enableParkingOrder>true</enableParkingOrder><invoiceWithoutWorkDone>false</invoiceWithoutWorkDone></settings></subCompany><pn>101905</pn></company></getCompanyListResponse></soap:Body></soap:Envelope>

I want to query the company and the sub-companies in this xml, but i am not able to do.

I have tried following:

XMLTable(
'/Envelope/Body/*/company'
passing t.xml_data
columns
unr varchar2 path '@unr'

XMLTable(
'/Envelope/Body/getCompanyListResponse/company'
passing t.xml_data
columns
unr varchar2 path './unr'

XMLTable(
'/Envelope/Body/getCompanyListResponse/company'
passing t.xml_data
columns
unr varchar2 path 'unr'

This post has been answered by Solomon Yakobson on Apr 17 2024

Jump to Answer

Added on Apr 17 2024

#query, #xml, #xmltype

1 comment

163 views