5 Replies Latest reply: Nov 10, 2013 3:42 PM by ranit B RSS

    XML Extract function help

    14a082e1-2694-4133-8671-aa7f349177a2

      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>

        • 1. Re: XML Extract function help
          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.

          • 2. Re: XML Extract function help
            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

            • 3. Re: XML Extract function help
              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

              • 4. Re: XML Extract function help
                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.


                • 5. Re: XML Extract function help
                  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 (: , 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