4 Replies Latest reply: May 7, 2012 5:44 AM by Jitesh Gurnani-Oracle RSS

    XML - ORA-19025: EXTRACTVALUE returns value of only one node

    Jitesh Gurnani-Oracle
      Hi,

      I am new to XML DB. Can somebody help me with the below XML

      I use the following XML ... (I have pasted only a part of it coz i require data only till this section )

      XML
      --------------------

      <?xml version="1.0" encoding="UTF-8"?><SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-
      instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><SOAP-ENV:Body>
      <ns:PicklistWS_GetPicklistValues_Output xmlns:ns="urn:crmondemand/ws/picklist/">
      <ListOfParentPicklistValue xmlns="urn:/crmondemand/xml/picklist">
      <ParentPicklistValue>
      <Language>ENU</Language>
      <ParentFieldName>plProduct_Team</ParentFieldName>
      <ParentDisplayValue>Marketing On Demand</ParentDisplayValue>
      <ParentCode>Marketing On Demand</ParentCode>
      <Disabled>N</Disabled>
      <ListOfPicklistValue>
      <PicklistValue>
      <Code>OCP/SME Escalation</Code>
      <DisplayValue>OCP/SME Escalation</DisplayValue>
      <Disabled>N</Disabled>
      </PicklistValue>
      <PicklistValue>
      <Code>Fusion Request</Code>
      <DisplayValue>Fusion Request</DisplayValue>
      <Disabled>N</Disabled>
      </PicklistValue>



      Code
      ---------




      SELECT distinct
      EXTRACTVALUE(VALUE(SR), '/ParentPicklistValue/ListOfPicklistValue/PicklistValue/Code','xmlns="urn:/crmondemand/xml/picklist"') AS Display,
      EXTRACTVALUE(VALUE(SR),'/ParentPicklistValue/ListOfPicklistValue/PicklistValue/DisplayValue','xmlns="urn:/crmondemand/xml/picklist"') AS Return,
      EXTRACTVALUE(VALUE(SR),'/ParentPicklistValue/ParentDisplayValue','xmlns="urn:/crmondemand/xml/picklist"') AS parent_display,
      EXTRACTVALUE(VALUE(SR),'/ParentPicklistValue/ParentCode','xmlns="urn:/crmondemand/xml/picklist"') AS parent_return
      FROM TABLE(XMLSEQUENCE(EXTRACT(
      WEB_SERVICE('<?xml version="1.0" encoding="UTF-8" standalone="no"?><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:Body>
      <PicklistWS_GetPicklistValues_Input xmlns="urn:crmondemand/ws/picklist/">
      <FieldName>Type</FieldName>
      <RecordType>Service Request</RecordType>
      </PicklistWS_GetPicklistValues_Input>
      </soap:Body>
      </soap:Envelope>'
      ,'document/urn:crmondemand/ws/picklist/:GetPicklistValues', Sessionid),
      '/soap:Envelope/soap:Body/*/*/*','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/'))) SR


      ERROR
      ---------

      ORA-19025: EXTRACTVALUE returns value of only one node


      UNDERSTANDING
      ---------------------------

      As my Xpath points only until the node - ParentPicklistValue and not the child nodes under it. Hence when i try to query the child nodes - /ParentPicklistValue/ListOfPicklistValue/PicklistValue/Code, I recieve the above mentioned error.

      REQUIREMENT
      -----------------------

      Can somebody help me to recieve the Parent values and also its child values based on the above query and xml.
        • 1. Re: XML - ORA-19025: EXTRACTVALUE returns value of only one node
          odie_63
          Hi,

          That's a classic ;)

          You need a second XMLSequence that will shred the collection of PicklistValue into relational rows :
          select extractvalue(value(sr2), '/PicklistValue/Code', 'xmlns="urn:/crmondemand/xml/picklist"') AS Display
               , extractvalue(value(sr2), '/PicklistValue/DisplayValue', 'xmlns="urn:/crmondemand/xml/picklist"') AS Return
               , extractvalue(value(sr1), '/ParentPicklistValue/ParentDisplayValue', 'xmlns="urn:/crmondemand/xml/picklist"') AS parent_display
               , extractvalue(value(sr1), '/ParentPicklistValue/ParentCode', 'xmlns="urn:/crmondemand/xml/picklist"') AS parent_return
          from table(
                 xmlsequence(
                   extract( WEB_SERVICE( ... )
                          , '/soap:Envelope/soap:Body/ns:PicklistWS_GetPicklistValues_Output/ListOfParentPicklistValue/ParentPicklistValue'
                          , 'xmlns="urn:/crmondemand/xml/picklist"
                             xmlns:ns="urn:crmondemand/ws/picklist/"
                             xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"' )
                 )
               ) sr1
             , table(
                 xmlsequence(
                   extract( value(sr1)
                          , '/ParentPicklistValue/ListOfPicklistValue/PicklistValue'
                          , 'xmlns="urn:/crmondemand/xml/picklist"' )
                 )
               ) sr2
          ;
          What's your database version BTW?
          On 10.2 and up, you may use XMLTable instead.
          • 2. Re: XML - ORA-19025: EXTRACTVALUE returns value of only one node
            Jitesh Gurnani-Oracle
            Hi Odie ...

            Must tell you that your answer was a CLASSIC ;) ... It worked great ... Thanks a ton.

            The version of my database is 11.1.0.6.0.

            I would also like to look at your other solution ... and also if you could assist me with useful XML query/XML docs or links, it would be great.

            Regards,
            Jitesh
            • 3. Re: XML - ORA-19025: EXTRACTVALUE returns value of only one node
              odie_63
              XMLTable version :
              select p.parent_display
                   , p.parent_return
                   , v.display
                   , v.return
              from xmltable(
                     xmlnamespaces(
                       default 'urn:/crmondemand/xml/picklist'
                     , 'urn:crmondemand/ws/picklist/' as "ns"
                     , 'http://schemas.xmlsoap.org/soap/envelope/' as "soap"
                     )
                   , '/soap:Envelope/soap:Body/ns:PicklistWS_GetPicklistValues_Output/ListOfParentPicklistValue/ParentPicklistValue'
                     passing WEBSERVICE( ... )
                     columns
                       parent_display      varchar2(80) path 'ParentDisplayValue'
                     , parent_return       varchar2(80) path 'ParentCode'
                     , PicklistValues      xmltype      path 'ListOfPicklistValue'
                   ) p
                 , xmltable(
                     xmlnamespaces(default 'urn:/crmondemand/xml/picklist')
                   , '/ListOfPicklistValue/PicklistValue'
                     passing p.PicklistValues
                     columns
                       display             varchar2(80) path 'Code'
                     , return              varchar2(80) path 'DisplayValue'
                   ) v
              ;
              The official documentation is good place to start : http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAGCBGJ
              which is part of the XML DB Dev Guide : http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/toc.htm