developers

    Forum Stats

  • 3,873,649 Users
  • 2,266,621 Discussions
  • 7,911,596 Comments

Discussions

XML Extract function help

1050736
1050736 Member Posts: 1
edited Nov 10, 2013 4:37PM in XQuery

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>

Tagged:
ranit B

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy

    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
    ranit B Member Posts: 3,268 Silver Trophy

    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
    ranit B Member Posts: 3,268 Silver Trophy
    edited Nov 9, 2013 1:27PM

    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
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Nov 10, 2013 6:32AM
    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
  • ranit B
    ranit B Member Posts: 3,268 Silver Trophy
    edited Nov 10, 2013 4:42PM

    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

This discussion has been closed.
developers