XML Extract function help

1050736

I have below xml in oracle table, how to extract partial address data "10 Otterburn Gardens, ISLEWORTH, Middlesex TW4 5JJ" using sql

<?xml version="1.0"?>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

  <soap:Body>

    <qas:QASearchResult xmlns:qas="http://www.qas.com/web-2009-08" VerifyLevel="None">

      <qas:QAPicklist AutoFormatSafe="true">

        <qas:FullPicklistMoniker>0_MGBRFQbdBwAAAAABAQEAAQAAAADfKULSACEYAgAAAAAAMTYAAP..ZAAAAAD.....AAAAAAAAAAA-</qas:FullPicklistMoniker>

        <qas:PicklistEntry FullAddress="true">

          <qas:Moniker>0XOGBRFQbdBwAAAAABAwEAAAAA3ylC0gAhGAIAAAAAADE2AAD..2QAAAAA.....wAAAAAAAAAAADE2IE90dGVyYnVybiBHYXJkZW5zLCBUVzcgNUpKAA--</qas:Moniker>

          <qas:PartialAddress>10 Otterburn Gardens, ISLEWORTH, Middlesex TW4 5JJ</qas:PartialAddress>

          <qas:Picklist>16 Otterburn Gardens, ISLEWORTH, Middlesex</qas:Picklist>

          <qas:Postcode>TW7 5JJ</qas:Postcode>

          <qas:Score>100</qas:Score>

        </qas:PicklistEntry>

        <qas:Prompt>Enter selection</qas:Prompt>

        <qas:Total>1</qas:Total>

      </qas:QAPicklist>

    </qas:QASearchResult>

  </soap:Body>

</soap:Envelope>

odie_63

Starting with 11g :

select xmlcast(
         xmlquery(
           'declare default element namespace "http://www.qas.com/web-2009-08"; (:  
            declare namespace soap = "http://schemas.xmlsoap.org/soap/envelope/"; (: 

            /soap:Envelope/soap:Body/QASearchResult/QAPicklist/PicklistEntry/PartialAddress'
           passing t.xml_col
           returning content
         )
         as varchar2(100)
       )
from your_table t ;

For earlier versions, use EXTRACTVALUE function.

ranit B

Hi Odie,

As per your advice, I came into this forum space (XML DB/Xquery) to learn more.

Could you please help us understand the working of this path expression:

'declare default element namespace "http://www.qas.com/web-2009-08"; (::)   
    declare namespace soap = "http://schemas.xmlsoap.org/soap/envelope/"; (::)

    /soap:Envelope/soap:Body/QASearchResult/QAPicklist/PicklistEntry/PartialAddress'

I can guess that you are creating namespaces called "soap" and "qas", but what is "default namespace" and what is this (:: )  ??

It'll be really helpful for OP, me and other people who trying to understand XQuery.

Thanks

-- Ranit

ranit B

Also, I can see here -- ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

that you have used another technique XMLNamespaces() for the same.

select x.*
from abc t
   , xmltable(
       xmlnamespaces(
         default 'urn:swift:xsd:fin.970.2011'
       , 'urn:swift:xsd:mtmsg.2011' as "ns0"
       )
     , '/ns0:FinMessage/ns0:Block4/Document/MT970/F61a/F61'
       passing t.col1
       columns F61ValueDate                Varchar(40) Path 'ValueDate'
             , DebitCreditMark             Varchar(40) Path 'DebitCreditMark'
             , Amount                      Varchar(40) Path 'Amount'
             , TransactionType             Varchar(40) Path 'TransactionType'
             , IdentificationCode          Varchar(40) Path 'IdentificationCode'                  
             , ReferenceForTheAccountOwner Varchar(40) Path 'ReferenceForTheAccountOwner'
             , SupplementaryDetails        Varchar(40) Path 'SupplementaryDetails'        
     ) x;

What you are trying to do here? and why that "default" namespace?

Also, I'll also go through the docs in this meantime.

-- Ranit

odie_63

I can guess that you are creating namespaces called "soap" and "qas", but what is "default namespace" and what is this (:: )  ??

When we declare a default namespace, all non-prefixed element names referenced in the XQuery expression will be considered belonging to that namespace.

The example above is equivalent to this one, using a declared prefix instead of a default namespace :

'declare namespace ns0 = "http://www.qas.com/web-2009-08"; (::)  

    declare namespace ns1 = "http://schemas.xmlsoap.org/soap/envelope/"; (::)

    /ns1:Envelope/ns1:Body/ns0:QASearchResult/ns0:QAPicklist/ns0:PicklistEntry/ns0:PartialAddress'

In the XQuery language, (: and are comment delimiters.

Here, I used an empty comment directly after the semicolon to prevent my client tool (PL/SQL Developer) to interpret it as a SQL statement terminator.

This trick is explained in the documentation : Using Namespaces with XQuery

XMLNamespaces() is available with XMLTable only. It provides a more friendly way to declare a default namespace and prefixes.

ranit B

You are simply amazing, Odie. Thanks!!!

Just to explain the above trick, quoting the text from doc link that you posted:

and NOTE: this trick is only while using SQL*Plus

Be aware of the following pitfall, if you use SQL*Plus: If the semicolon (;) at the end of a namespace declaration terminates a line, SQL*Plus interprets it as a SQL terminator. To avoid this, you can do one of the following:

Place the text that follows the semicolon on the same line.
Place a comment, such as <span class="codeinlinebold">(: <span __jive_emoticon_name="happy" __jive_macro_name="emoticon" class="jive_macro jive_emote" src="/5.0.5/images/emoticons/happy.gif"></span></span>, after the semicolon, on the same line.
Turn off the recognition of the SQL terminator with SQL*Plus command SET SQLTERMINATOR.

Your efforts are much appreciated.

-- Ranit