I can not parse the XML file. The request returns nothing:

with x as (select XMLTYPE( '<?xml version="1.0" encoding="UTF-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <soap:Header><CigWsHeader xmlns="http://ws.info.com/"> <Culture>ru-RU</Culture> <Password>Vfylfh12354</Password> <SecurityToken /><UserId>0</UserId> <UserName>CNTuser03</UserName> <Version>1_0</Version></CigWsHeader> </soap:Header><soap:Body> <GetReportResponse xmlns="http://ws.info.com/"> <GetReportResult><CigResult Version="3.0.1" xmlns=""> <DateTime>23.12.2017 15:49:18</DateTime> <ReferenceId>10</ReferenceId> <ServiceName>Front Office WS</ServiceName> <ResultCulture>ru-RU</ResultCulture> <ResultCode>10</ResultCode><ResultDescription /> <UsageIdentity /><Result><Root><Title name="tblTitle" title="Report" intitle="Report subject" /> <Header name="tblHeader" EntityType="Individual" title="Extended" ReportCode="Report.ReportName.Advanced" intitle="Subject"> <RegistrationID title="Reg id" value="1984725" /> <DateOfBirth title="Date of birth" value="26.02.1988" /> <Gender title="Gender" value="Male" /> <Name title="Name" value="JOHN" /> <Surname title="Surname" value="SMITH" /> <CityOfBirth title="City of birth" value="New York" /> <Education title="Edu" id="123" value="Master" /> <MatrialStatus title="Marital st." id="45" value="Single" /> <RegionOfBirth title="Region" value="NY" /> <CountryOfBirth title="Country" value="USA" /></Header> </Root></Result></CigResult></GetReportResult></GetReportResponse></soap:Body></soap:Envelope>') as xml from dual) SELECT y.* FROM x, XMLTABLE(xmlnamespaces('http://www.w3.org/2001/XMLSchema' as "xsd", 'http://www.w3.org/2001/XMLSchema-instance' as "xsi", 'http://schemas.xmlsoap.org/soap/envelope/' as "soap"), '/soap:Envelope/soap:Body/GetReportResponse/GetReportResult/CigResult/Result/Root/Header/RegistrationID' PASSING x.xml COLUMNS val VARCHAR2(255) PATH 'RegistrationID') y ;
  • What is your problem? - 0xdb
  • The request returns nothing, in the request for the example I am trying to pull out the value of the RegistrationID attribute but without result. - Didar Utkelbaev
  • Immediately I wondered what the problem was. Look, I once answered a similar question here. For you, the second option is relevant. - 0xdb
  • And try to reduce the xml to the required minimum, rather than embed it as is. - 0xdb
  • While it is visible that in columns no type. And the path looks weird, where is your return ? - 0xdb

2 answers 2

The nodes GetReportResponse and GetReportResult declared in the namespace xmlns="http://ws.info.com/" . You must specify the same namespace in the XMLNAMESPACES clause, and then use it in XQuery and XPath expressions as qualified names in the XMLTable function.

Excerpt from off. documentation :

The XMLNAMESPACES clause contains a set of XML namespace declarations. These declarations are referenced by the XQuery_string, the statement is subject to the XMLTtable function. If you want to use the COLUMNS clause expressions of the COLUMNS clause, then you need to specify the XMLNAMESPACES clause.

The example uses XML data from the question, but for clarity, I removed some of the nodes and attributes while keeping the document structure unchanged:

 with xmldata as ( select xmltype ('<?xml version="1.0" encoding="UTF-8"?> <soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Header> <CigWsHeader xmlns="http://ws.info.com/"> <UserName>CNTuser03</UserName> </CigWsHeader> </soap:Header> <soap:Body> <GetReportResponse xmlns="http://ws.info.com/"> <GetReportResult> <CigResult Version="3.0.1" xmlns=""> <ReferenceId>10</ReferenceId> <ServiceName>Front Office WS</ServiceName> <ResultCode>10</ResultCode> <Result> <Root> <Header name="tblHeader"> <RegistrationID title="Reg id" value="1984725" /> <Name title="Name" value="JOHN"/> <Surname title="Surname" value="SMITH"/> </Header> </Root> </Result> </CigResult> </GetReportResult> </GetReportResponse> </soap:Body> </soap:Envelope>') as doc from dual ) select xtab.* from xmldata x cross join xmltable ( xmlnamespaces ( 'http://schemas.xmlsoap.org/soap/envelope/' as "soap", 'http://ws.info.com/' as "ws" ), '/soap:Envelope/soap:Body/ws:GetReportResponse/ws:GetReportResult/ CigResult/Result/Root/Header/RegistrationID' passing x.doc columns "Title" varchar2 (32) path '@title', "Value" varchar2 (32) path '@value' ) xtab ;

Conclusion:

 Title Value -------------------------------- -------------------------------- Reg id 1984725

Note : The solution in the vehicle's response using search through descendant:: (along the axis of descendants) will also work, but is not recommended for performance reasons, as Oracle indicated in its White Books (see page 32) about reaching max. XML query performance.

    In general, after a long dig, I found a way out. It seems to be in namespaces that are specified in GetReportResponse. This is the way to solve the problem:

     WITH X AS (SELECT XMLTYPE( '<?xml version="1.0" encoding="UTF-8"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <soap:Header> <CigWsHeader xmlns="http://ws.info.com/"> <Culture>ru-RU</Culture> <Password>Vfylfh12354</Password> <SecurityToken /><UserId>0</UserId> <UserName>CNTuser03</UserName> <Version>1_0</Version></CigWsHeader> </soap:Header> <soap:Body> <GetReportResponse xmlns="http://ws.info.com/"> <GetReportResult><CigResult Version="3.0.1" xmlns=""> <DateTime>23.12.2017 15:49:18</DateTime> <ReferenceId>10</ReferenceId> <ServiceName>Front Office WS</ServiceName> <ResultCulture>ru-RU</ResultCulture> <ResultCode>10</ResultCode><ResultDescription /> <UsageIdentity /> <Result> <Root> <Title name="tblTitle" title="Report" intitle="Report subject" /> <Header name="tblHeader" EntityType="Individual" title="Extended" ReportCode="Report.ReportName.Advanced" intitle="Subject"> <RegistrationID title="Reg id" value="1984725" /> <DateOfBirth title="Date of birth" value="26.02.1988" /> <Gender title="Gender" value="Male" /> <Name title="Name" value="JOHN" /> <Surname title="Surname" value="SMITH" /> <CityOfBirth title="City of birth" value="New York" /> <Education title="Edu" id="123" value="Master" /> <MatrialStatus title="Marital st." id="45" value="Single" /> <RegionOfBirth title="Region" value="NY" /> <CountryOfBirth title="Country" value="USA" /></Header> </Root> </Result> </CigResult> </GetReportResult> </GetReportResponse> </soap:Body></soap:Envelope>') AS XML FROM DUAL) SELECT y.* FROM x, XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' as "soap"), '*/*/*/*/*/*/Root/descendant::RegistrationID' PASSING x.xml COLUMNS title VARCHAR2(200) PATH '@title', val VARCHAR2(200) PATH '@value') y ;